Rolling Forward Pivot Table Source Data

[AndrewEdmunds]

Hello,

I am using a pivot table to consolidate the financial statements of 26
facilities. How do I roll forward the pivot tables to point at the next
month's facility files without having to recreate a new pivot table each
month?

Thanks

 

[Barb Reinhardt]

I'm guessing you could define a dynamic named range for the Pivot Source.
It's far easier to give a suggestion when you know how the source data is set
up.

 

[AndrewEdmunds]

Hello and thanks for your reply,

I'll try to explain what I mean as it is complicated. I have the financial
statements for each of the 26 facilities for September. In a separate
workbook, I created a pivot table to consolidate the 26 facilities. For
October, I copy all of the facility files from September into an October
folder and update the files with October's information. If I copy the pivot
table file into the October folder the source data still points to the
September files. How can I get it to point to October without having to go
through and resetup each link?

Thanks,

 

[Barb Reinhardt]

I'm going to assume you're talking about changing the pivot source for all
pivot tables within a given workbook. If this is it, try this. It just
replaces the old file name with the new file name. If you want to change
the path, you can do that as well.

Option Explicit

Sub ChangePivotSource()
Dim WS As Worksheet
Dim aWB As Workbook
Dim myPivot As PivotTable
Dim OldFile As String
Dim NewFile As String
Dim myString As String

OldFile = "myFile.xls"
NewFile = "myNewFile.xls"

Set aWB = ActiveWorkbook

For Each WS In aWB.Worksheets
For Each myPivot In WS.PivotTables
Debug.Print myPivot.Name, myPivot.SourceData
myString = Replace(myPivot.SourceData, OldFile, NewFile)
myPivot.SourceData = myString
Next myPivot
Next WS
End Sub



--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.

 

[AndrewEdmunds]

Thank you very much for your response.

Two things, the first is that it is a pivot table from multiple workbooks so
there are 26 paths that I need to change for each pivot table. Would that
change the coding?

Second, I do not know visual basic very well, is there anyway to do this
without visual basic. Before I started using pivot tables, the spreadsheets
I used were linked by cell reference. When I copied and pasted the source
workbooks and the linked workbooks at the same time into a new folder the
links automatically updated to the new path. Is there any way for the pivot
table links to automatically update as well? I know I'm probably grasping at
straws here but I do really appreciate all of your help. Have a great day!