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

**huyeote1** · 06-09-2011, 11:38 PM

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:

Please Login or Register  to view this content.

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.

**MarvinP** · 06-10-2011, 08:59 AM

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.

**romperstomper** · 06-10-2011, 10:15 AM

Something like:

Please Login or Register  to view this content.

**huyeote1** · 06-13-2011, 05:28 AM

Hi Marvin,

Thanks for your help. But MS's VBA reference said ChangePivotCache method desn't work on cache that is connected to an external source.

**huyeote1** · 06-13-2011, 05:37 AM

Great, romperstomper, your method worked.

Somehow I tried to assign same index number to CacheIndex property but for no reason it failed. This time it worked. A big thank-you!

**beebs1993** · 10-28-2012, 07:40 PM

Originally Posted by MarvinP

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.

Dude, Thank you! It worked!