Pivot Table-change data source in multiple tables

[dde938]

Hi, I have a report that I duplicate for different campaigns. The report contains about 20 pivot tables all based on the exact same data source (data source contained in excel). When the next campaign starts, I want to copy the old report and then change the data source on ALL 20 tables at the same time. How?
(if I go to pivot wizard and hit back, I can change the source on each table individually, but this takes a long time and registers as a new "source" each time so the report gets way too big too quick...and it takes a long time.)
Thanks,Danielle

 

[pbornemeier]

Hi! Welcome to the Board!

If you turn on the macro recorder and create a pivot table, you can get some good ideas on what you have to do to modify the source.

Something like this should work. Put this code in a Standard Module in the workbook(s) that contain(s) the Pivot Table(s):

Sub ChangePTSource()

    Dim iX As Integer
    For iX = 1 To ActiveWorkbook.PivotCaches.Count
        ActiveWorkbook.PivotCaches(iX).SourceData = _
            "'[Cell Phone Usage 20091130.xls]Sheet1'!R2C1:R70C6"
    Next
End Sub

 

[jakefh]

Hi.

I have a similar problem.

I have several sheets, all with multiple (horizontal) pivot tables (I know having multiple pivot tables on one sheet isn't advisable, but it really does suit my requirements).

I have just noticed that several of them have a data source from another workbook. I would like to replace the all the pivot tables in the workbook with a single data source within the same workbook.

I recorded a macro whilst I changed the data source on 6 pivot tables on one sheet, then moved to another sheet and changed one on there.

However I have no idea how to make the macro just change all the pivot tables in the workbook, to the same internal data source.

Any help would be appreciated. Thanks.

Sub PivotSource()
'
' PivotSource Macro
'


'
    ActiveSheet.PivotTables("PivotTable2").ChangePivotCache ( _
        "Headlines WAG Aut1!PivotTable1")
    ActiveCell.Offset(0, 8).Range("A1").Select
    ActiveSheet.PivotTables("PivotTable3").ChangePivotCache ( _
        "Headlines WAG Aut1!PivotTable1")
    ActiveCell.Offset(0, 8).Range("A1").Select
    ActiveSheet.PivotTables("PivotTable4").ChangePivotCache ( _
        "Headlines WAG Aut1!PivotTable1")
    ActiveCell.Offset(0, 8).Range("A1").Select
    ActiveSheet.PivotTables("PivotTable5").ChangePivotCache ( _
        "Headlines WAG Aut1!PivotTable1")
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 13
    ActiveCell.Offset(0, 8).Range("A1").Select
    ActiveSheet.PivotTables("PivotTable6").ChangePivotCache ( _
        "Headlines WAG Aut1!PivotTable1")
    ActiveCell.Offset(0, 8).Range("A1").Select
    ActiveSheet.PivotTables("PivotTable7").ChangePivotCache ( _
        "Headlines WAG Aut1!PivotTable1")
    Sheets("Progress 8 WAG Aut1").Select
    ActiveCell.Offset(10, 1).Range("A1").Select
    ActiveSheet.PivotTables("PivotTable1").ChangePivotCache ( _
        "Headlines WAG Aut1!PivotTable1")
End Sub

 

[pbornemeier]

The reason it is not wise to have multiple PT on a single worksheet is that they can grow in size quite rapidly as fields are added/deleted/reordered in the Row & Column parts of the PT. One table can easily grow to overwrite another.

Try this code:

Option Explicit

Sub PivotSource()

    Dim lWksIndex As Long
    Dim lPtIndex As Long
    Dim lUpdateCount As Long
    
    Const sNewCacheName As String = "Headlines WAG Aut1!PivotTable1"
    
    For lWksIndex = 1 To Worksheets.Count
        With Worksheets(lWksIndex)
            For lPtIndex = 1 To .PivotTables.Count
                .PivotTables(lPtIndex).ChangePivotCache (sNewCacheName)
                lUpdateCount = lUpdateCount + 1
            Next
        End With
    Next

    MsgBox lUpdateCount & " PivotTable(s) Source Updated."

End Sub

 

[jakefh]

My pivots have a lot of thin blank columns between them

Thanks Phil, your code worked real good.

Jake