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" wrote:

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,



--
Thank You
Andrew Edmunds


"Barb Reinhardt" wrote:

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.
--
HTH,
Barb Reinhardt

"AndrewEdmunds" wrote:

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
--
Thank You
Andrew Edmunds