VBA change pivot table source data?

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

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

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:

Sub Macro1()

    With Sheet12.PivotTables("PVT01")
        .SourceData = "Database02"

    End With
    
End Sub

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