VBA to change PT datasource

[DRANDON]

I would like to know a relatively simple vba snippet to change an existing pivot tables datasource. Macro recording doesn't reveal anything (just blank with the exception of a cell selection)

I've tried this with errors..

Worksheets("Pivot").PivotTables("Pivot_1").PivotCache.DataSource = _
Range("A2:B10")

I really appreciate any help - thanks

 

[daverunt]

Re: VBA to chnage PT datasource

Is this any help,

he's used variable names for the range in his example.

http://www.excelforum.com/excel-programming/733379-vba-change-pivot-table-source-data.html

 

[gizzylover]

Re: VBA to chnage PT datasource

For what it's worth, that link did help me ) I wound up using the one line, created a defined range called "PTDefinedRange", and then I created a sub routine to change the datasource for each of my pivot tables in a multi-tabbed file to my defined range. I'm no wizard, but it seemed to work!

Sub UpdatePTDataSource()
Dim pt As PivotTable
Dim wks As Worksheet

Application.ScreenUpdating = False

For Each wks In ActiveWorkbook.Worksheets
For Each pt In wks.PivotTables
pt.SourceData = "PTDefinedRange"
Next pt
Next wks

Set pt = Nothing
Set wks = Nothing

End Sub

 

[DRANDON]

Re: VBA to chnage PT datasource

That link helped me big time. Thanks Davevrunt!