I recently worked on a project that had three distinct and large datasets for different geographical areas in three workbooks. Starting with the first workbook and data set in a Table in its own worksheet, I created a worksheet of seven Pivot Tables to provide seven Pivot Charts needed in their own sheet. This could also easily suit a Dashboard solution by making the Pivot Charts smaller. The charts had to be the same for the other two datasets and the columns in the datasets were identical.
So I copied the Pivot Table page to the new workbooks where the second and third datasets resided but of course the pivot tables still pointed to the 1st workbook. So to save some tedious work I wrote a handy quick and dirty macro to change the data source for these copied Pivot Tables to the dataset Table in this new workbook as follows:
[vb]
Sub Change_Pivot_Source()
‘For when you copy say a worksheet of Pivot Tables to another workbook
Dim pt As PivotTable
Sheet6.Activate
For Each pt In ActiveWorkbook.Worksheets(“Pivot Tables”).PivotTables
pt.ChangePivotCache ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=”tblData_y”)
Next pt
End Sub
[/vb]
In this specific situation, Sheet6 was the new copied sheet and “tblData_y” is the Table name of the data to be used in the Pivot Tables so adapt for your situation.
So this worked well. I enjoy writing what I call ‘quick and dirty’ macros for my projects so long as it saves the client time as you often learn something new in VBA with every new code and of course you have the code then for future situations. Plus VBA is fun!