VBA change pivot table source data?

**skysurfer** · 06-16-2010, 06:34 PM

Hi,

Is it possible to have VBA tell a pivot table to change the location of the source data after the pivot table has been created, or can that only be done by completely recreating the pivot table?

Here's the scenario... I have two databases in Excel, each feeding a different pivot table (and cache) that then feeds a bunch of charts. What I would like to do is to be able to change pivot tables source data/cache to the either database using VBA.

Lawrence

**skysurfer** · 06-17-2010, 11:36 AM

Just bumpin' up the list...

Lawrence

**t_e_k** · 06-17-2010, 11:52 AM

Well I just tried recording a macro whilst I changed source, and this is what it gave me!

Please Login or Register  to view this content.

Where Sheet1!R10C1:R15C3 was the new data source I selected. Seems to work when I re-run the macro!

**skysurfer** · 06-17-2010, 12:49 PM

t_e_k,

Thanks!

Which version of Excel was that in? In 2003, I kept getting an error.

Lawrence

**skysurfer** · 06-17-2010, 03:13 PM

Okay, I can't seem to get this work and it's no doubt my VBA coding skills.

I have already created two pivot tables using two separate caches (from ranges Database01 and Database02). I'm trying to change the 1st pivot table's cache like so:

Please Login or Register  to view this content.

But...it keep blowing up on the SourceData line.

Lawrence

**t_e_k** · 06-17-2010, 03:27 PM

Hi Lawrence,

I was using 2007. Unfortunately my VBA skills are limited! Have you tried recording a macro as you change the source? And then maybe pasting that snippet into your main code? When I record a macro I think I have noticed that it doesn't pick up on named ranges, and so you might have to substitute in your Database02 name for the reference it gives you.

But that's as much as I can help, sorry!

**skysurfer** · 06-17-2010, 03:50 PM

t_e_k,

When you recorded the macro, what steps did you take to change the source data?

When you initially said it was doable, I tried with the macro recorder and using the Wizard and was successful. If you used a different method, that may help lead me to the answer.

Thanks again!

Lawrence

**skysurfer** · 06-21-2010, 01:02 PM

Hi all,

Haven't been able to resolve this one, yet. I now know if the Wizard can change the SourceData for a pivot table, then there must somehow be a way to make the code work for an already existing pivot table and databases. Maybe I've got a minor error in my code below???

Any ideas???

Thanks,

Lawrence

**skysurfer** · 07-14-2010, 09:14 PM

Finally got back to this problem...and solved it!

To change the data source for an already existing pivot table from "Database01" (a dynamic range) to "database02", use this code:

Please Login or Register  to view this content.

Where the name of the pivot table is PVT01 and the VBA name of the worksheet it's located is Sheet12.

Some of the most time-consuming problems have such simple answers. <sheesh>

Lawrence