0

I have already scoured the internet (eg. 1 2 3 ) for the answer, but all of them seem to provide a rather convoluted answer, which the users raised with specific conditions that are characteristic only to their own project, I intend this question to cut straight to the chase and bit more of a useful general learning resource. The question is:

How can I update my Pivot Table Source to a specific table range?

I have a Worksheet called "Summary" that contains the following ListObject (table) called "t_sum"

enter image description here

Issue is, upon a Workbook name change, the link to the pivot table gets broken and refreshing them returns the following error:

enter image description here

I tried something very simplistic in style of (that is invoked on every time workbook is opened)

Private Sub fix_pivot_source
   For Each pivot in Sheets("Summary").PivotTables
      pivot.SourceData = "Summary!t_sum"
   Next pivot
End Sub

That however doesn't seem to work. Another issue is, given this is a financial report, the data is refreshing and so do the pivot table names. So I can't just directly reference them in the PivotCache

Any idea how can I link to the static table name while taking the Workbook name out of the equation - effectively ommitting it from SourceData check?

Samuel Hulla
  • 6,617
  • 7
  • 36
  • 70
  • 1
    I am not sure if I understand the question, but table names have workbook scope, so `pivot.SourceData = "t_sum"` should work fine. – BrakNicku Aug 09 '18 at 13:59
  • @BrakNicku unfortunately it doesn't seem to work. I think the range itself gets specified fine, I just somehow need it to accommodate on possible `Workbook` name change. – Samuel Hulla Aug 10 '18 at 11:27
  • But is the source table in the same workbook as the pivot table(s)? If so, changing workbook name should not influence pivot cache. If it still causes problems, I would suggest recreating source and pivot table in fresh workbook from scratch to see if you can reproduce it. – BrakNicku Aug 10 '18 at 11:44
  • Why do you need to repoint the PIvotTables with code? It should happen automatically. – jeffreyweir Aug 13 '18 at 05:21
  • @jeffreyweir What you see right there is a Pivot Chart that is dependant on a pivot table. The pivot table summarizes the data per period. – Samuel Hulla Aug 13 '18 at 07:08
  • @BrakNicku yes, it is. In fact, it's literally hidden under the graph. That's why it's baffling me as well that it's effected upon workbook name change – Samuel Hulla Aug 13 '18 at 07:09
  • Is the source data in another workbook? – jeffreyweir Aug 13 '18 at 09:18
  • @jeffreyweir Check the comment above addressed to BrakNicku. **Pivot Table, Original (Table) Data and Chart (Graph) are all located in the same Worksheet** – Samuel Hulla Aug 13 '18 at 09:34
  • Perhaps the Excel PivotTable is inadvertently referencing a previous copy of the worksheet? That's happened to me a couple of times before. What do you see if you click the Change Data Source option? And does anything change if you use that Chance Source Data option to repoint the PivotTable at the data again? – jeffreyweir Aug 13 '18 at 10:12
  • @Rawrplus Any update on the above questions? – jeffreyweir Aug 15 '18 at 18:16
  • @jeffreyweir Unfortunately no, not yet. That file is on a protected server drive that needs to be accessed remotely. Didn't have the opporutnity to acess it yet – Samuel Hulla Aug 15 '18 at 18:27

0 Answers0