Thread: VBA to change data source of pivot table to another pivot table

I'm building an Excel 2007 workbook that contains multiple pivot tables which are all based on same pivot cache. The data source is external and retrieved by a SQL query from an Access 2007 DB. The SQL query may be changed for a purpose so a new pivot cache is created when it's modified.

I can use Pivot Table Wizard to manually steer all pivot tables to new master copy of pivot table without a problem. But I want to use VBA code to do the process to same time as I have more then 10 pvt in the workbook. So I use the Excel's macro to record a manual change. Below is what is the VBA code produced by Macro:

    Range("E77").Select
    ActiveSheet.PivotTableWizard SourceType:=xlPivotTable, SourceData:= _
        "pvtMstrCopy"
    ActiveWorkbook.ShowPivotTableFieldList = True

pvtMstrCopy is the name I give to the master pivot table. Cell E77 is inside the target pivot table which I want to change data source.

But the problem is I can't even re-run this recorded macro. Excel report error message

Run-time error '1004' PivotTableWizard method of Worksheet class failed

I add a sheet name to SourceData parameter to "Sheet1!pvtMstrCopy" and it still has the same error.

It's frustrating since I've been searching same error in various Excel forums for 2 days and I didn't find same one.

Can anyone tell me how to use VBA to change pivot table data source to another one? Thanks in advance.

Hi huyeole1,

I recorded two macros that changed the data source for a single pivot table. See if the attached example helps you figure out how to solve your problem. I was unable to make the SourceData equal to a named range.

Something like:

Dim PT as PivotTable
for each pt in activesheet.pivottables
   pt.cacheindex = sheets("Master").Pivottables("pvtMstrCopy").Cacheindex
next pt