Changing many pivot tables' sourcedata

[j01233210]

Hi,

I have a workbook with many sheets and each sheet has several pivot tables. However, this workbook is copied from different workbooks and the pivot tables are referencing to the original workbooks. I want them all now to use the same Table1 as source data in the new workbook. So I wrote this:

Sub Macro1()
Dim ws As Worksheet
Dim abc As Integer
For Each ws In ThisWorkbook.Worksheets
    For i = 1 To 20
    abc = i
    ActiveSheet.PivotTables("PivotTable" & abc).ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Table1", Version _
        :=xlPivotTableVersion12)
    Next i
Next
End Sub

The problem is my pivot tables are not ordered in 1,2,3,4,.....20. So it got errors. Can anyone help me out? Huge thx!!!!

 

[GaryWMn]

How about this?

Sub ChangePivotSource()
    Dim pt As PivotTable
    Dim ws As Worksheet
    
    For Each ws In Worksheets
        For Each pt In ws.PivotTables
            pt.SourceData = "Table1"
        Next pt
    Next ws
    
End Sub

Let us know if it works.

Gary

 

[GaryWMn]

If memory is of concern, you could change the method to point all the PivotTables to the same source.

If all the PivotTables do indeed have the same structure, this code should do the trick.

Sub ChangeCacheSource()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim pt1 As PivotTable
    Dim i As Long
    
    For Each ws In Worksheets
        For Each pt In ws.PivotTables
            If i = 0 Then
                i = 1
                pt.SourceData = "Table1"
                Set pt1 = pt
            Else
                pt.CacheIndex = pt1.CacheIndex
            End If
        Next pt
    Next ws
End Sub

 

[j01233210]

Failed.. I think you cannot directly call
For Each pt In ws.PivotTables
I use debug mode and when it goes to this row, it popped an error.

 

[GaryWMn]

What was the error message you received?

 

[j01233210]

Run time error '1004'
Application-defined or object-defined error