Pivot Table Source Data File Path Change

[AndrewEdmunds]

Hello, I need to know if there is anyway to automatically change the source
data path for a multiple consolidation range pivot table. I know that I
could go into the chart wizard and backup to change the source data manually,
but I have 25 files I would need to manually update I also have 20 different
pivot tables I would need to do it for. An example of one of the source data
path's is below.

'Q:\deptshare\Essentia\Finance\Andy\Projects\Financial
Collaborative\SEPTEMBER\Facility Files\[BLH.xls]BLH'!$G$320:$J$349

I want to replace the part of the file path that is in caps:

'Q:\deptshare\Essentia\Finance\Andy\Projects\Financial
Collaborative\OCTOBER\Facility Files\[BLH.xls]BLH'!$G$320:$J$349

Would anyone have any suggestions, keep in mind I'm not fluent in VBA, but
I'd be willing to try anything.

Thanks in advance!

 

[IdiotZ42]

The best way I've come up with is something like this

'create a compleetly new pivot cache
Dim fileName As String
fileName = "'Q:\deptshare\Essentia\Finance\Andy\Projects\Financial
Collaborative\" & UCase(Format(Date, "mmmm")) & "\Facility
Files\[BLH.xls]BLH'!$G$320:$J$349"
ThisWorkbook.Worksheets("Pivot Worksheet").PivotTables(1).PivotTableWizard
SourceType:=xlDatabase, SourceData:=tmp

'copy the index of the new pivot cache to the other pivot tables
Dim indx As Long
indx = ThisWorkbook.Worksheets("Pivot Worksheet").PivotTables(1).CacheIndex
ThisWorkbook.Worksheets("Other Pivot Worksheet").PivotTables(1).CacheIndex =
indx

'turn off the tool bars that get turned on
ThisWorkbook.ShowPivotTableFieldList = False
Application.CommandBars("PivotTable").Visible = False

although since each of the different pivot tables in your example are in
different work books you'll need to reset the pivot caches in each work book
individually.