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!

ActiveSheet.PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Sheet1!R10C1:R15C3" _
        , Version:=xlPivotTableVersion12)

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:

Sub Macro1()
    With Sheet12.PivotTables("PVT_Database01_Subcategories").PivotCache
        .SourceData = Range("Database02")

    End With
    
End Sub

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