Pivot Table Source Data File Path Change
 

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\Finan cial
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\Finan cial
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!
--
Thank You
Andrew Edmunds

Pivot Table Source Data File Path Change
 

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\Fina ncial
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.

"AndrewEdmunds" wrote:

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\Finan cial
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\Finan cial
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!
--
Thank You
Andrew Edmunds