macro to change data source for pivot table

[JoeSarton]

Hello -

Hoping someone can help me with my question regarding source data and pivot tables. One a tab I have columns of data which is the source data for a pivot table I have just below. For the sake of this question, lets say all of this is on "Sheet1", the data is in cells A2:F25, and the pivot table is in cell G27.

The problem comes when I make a copy of the tab (keeping the copied tab in the same workbook), change the data in this new tab (called Sheet2), the pivot table still uses the data from Sheet1 (it seems to automatically anchor the data source to that original tab). Is there a way that I can run a macro so that it will change the data source for the pivot table in Sheet2 to run off of the data in Sheet2.

The reason I don't want to manually adjust the source data is that I will have many tabs and hoping there was a macro that I could run on each tab to change the data source. Hopefully this question is clear enough that someone can help.

I don't know if it makes a difference, but I am in Excel 2010

Thanks!

 

[Jerry Sullivan]

Welcome to the Board!

As a first step, I recommend you use dynamic named ranges as the source for your PivotTable data.
The Contextures site has very clear instructions on how to do that.
http://www.contextures.com/xlPivot01.html#Dynamic

Next, if your source data is always on the same worksheet as your PT, then I'd suggest you
define the named ranges with Worksheet scope (instead of Workbook scope) and
adopt the practice of using the same name for the source data range on each sheet.

In response to your question, if you do the two steps above, all you need to do when you
copy a sheet with a PT is to remap the source data to the named range on the new sheet instead of the old sheet.

For example, you have a PT on Sheet1 and your data source is range:Sheet1!PTsource
When you copy it to make Sheet2, you just need to change the reference to: Sheet2!PTsource.
You could do that manually or with a simple macro like the one below.

Since your ranges are dynamic, they will adjust when you add or delete rows.

Here is the code- it assumes you have only one PT per sheet.

Sub Update_PTSource()
    With ActiveSheet
        .PivotTables(1).ChangePivotCache ActiveWorkbook. _
            PivotCaches.Create(SourceType:=xlDatabase, _
            SourceData:="'" & .Name & "'!PTsource")
    End With
End Sub

 

[JoeSarton]

Jerry -

Many thanks, this worked out quite well. Appreciate the speedy reply!

 

[UHCAron]

Can someone help me fix this:

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
ws.PivotTables(1).ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:="priorities")

Loop
-----------------------

I'm trying to loop through the pivot tables and change their sourcedata to priorities

 

[UHCAron]

Found my own answer

Dim ws As Worksheet
Dim PT As PivotTable


For Each ws In ActiveWorkbook.Worksheets
For Each PT In ws.PivotTables
PT.SourceData = "priorities"
Next PT
Next ws

 

[hanmas]

Hi all,
I am looking for something similar, but I would like to be able to set the datasource for all pivottables in a workbook according to the value in a cell. The cell contains a dropdown list of all available datasources (dynamic named ranges).

So far, I have the following but it isn't working (Error Code 1004 This command requires at least two rows of data)

Dim ws As Worksheet
Dim PT As PivotTable

For Each ws In ActiveWorkbook.Worksheets
For Each PT In ws.PivotTables
PT.SourceData = "Summary!$c$3"
Next PT
Next ws

Any ideas?
Thanks,
Graham

 

[UHCAron]

Try this.....

Sub RefreshPivotTables()
Dim ws As Worksheet
Dim PT As PivotTable
Dim SourceName As String
SourceName = Worksheets("Summary").Range("C3")

For Each ws In ActiveWorkbook.Worksheets
For Each PT In ws.PivotTables
PT.SourceData = SourceName
Next PT
Next ws

ActiveWorkbook.RefreshAll
End Sub

 

[hanmas]

Great...works perfectly. Thanks very much
Graham

 

[mike_romano]

Try this.....

Sub RefreshPivotTables()
Dim ws As Worksheet
Dim PT As PivotTable
Dim SourceName As String
SourceName = Worksheets("Summary").Range("C3")

For Each ws In ActiveWorkbook.Worksheets
For Each PT In ws.PivotTables
PT.SourceData = SourceName
Next PT
Next ws

ActiveWorkbook.RefreshAll
End Sub

Hi UHCAron,

Will this work with multiple Pivot Tables in one sheet? They all have the same range, and it will not change. I just have the same problem with the pivot tables being "anchored" to the original sheet whenever I make a copy of the sheet, and I have to manually change them one by one.

 

[Vince1984]

Hi Guys.

Re: macro to change data source for pivot table
Sub TestingPivot()
'
' TestingPivot Macro
' test pivot
'
Dim ws As Worksheet
Dim PT As PivotTable
Dim SourceName As String
SourceName = Worksheets("SMS Data Input").Range("A2:AH2")
For Each ws In ActiveWorkbook.Worksheets
For Each PT In ws.PivotTables
PT.SourceData = SourceName
Next PT
Next ws
ActiveWorkbook.RefreshAll

End Sub
(Error type mismatch)
Above is the code to Change Source Data to Range(A:AH) each time I input data into the source data sheet. My issue is that the pivot table requires the source data to be changed each time to Column A:AH and refresh but how can I automate this as I have more that 1 pivot table on 4 sheets(using the same source data as referrence). I also dont want the pivot table to change its organized structure in terms of what I am currently comparing on the pivot. I am new to VBA and would appreciate your advice.

Thanks

Vince