VBA to Change Pivot Table Source
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:
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
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.