VBA Refresh Pivot table data

[MBM2016]

Good morning,

I am so close to finishing up some code to refresh a 50+ worksheet workbook, all with multiple pivot tables.

The good news for me is there are only 4 source data sheets.

At the moment I am designating one sheet "Master Summary" as the sheet with the parent cache/source data mapped to it. With the goal to have the remaining child sheets reference the 4 pivot caches on the "Master Summary" sheet.

At the moment I have built two subroutines:
1) Creates 4 caches to the "Master Summary" Sheet
2) Changes/points the cache of each (child) pivot in all other sheets in the workbook to their corresponding data source

Sub 1

Sub Adjust_Parent_PT_Ranges()
 
Dim Data_sht1 As Range
Dim Data_sht2 As Range
Dim Data_sht3 As Range
Dim Data_sht4 As Range
 
Dim Pivot_sht As Worksheet
 
Dim PivotName1 As String
Dim PivotName2 As String
Dim PivotName3 As String
Dim PivotName4 As String
 
'= Set Variables Equal to Data Sheet and Pivot Sheet
  Set Data_sht1 = ActiveWorkbook.Sheets("Demo Details").[A:AF]
  Set Data_sht2 = ActiveWorkbook.Sheets("Lead Details").[A:N]
  Set Data_sht3 = ActiveWorkbook.Sheets("Opportunity Details").[A:X]
  Set Data_sht4 = ActiveWorkbook.Sheets("Sales Details").[A:AD]
 
  Set Pivot_sht = ThisWorkbook.Worksheets("Master Summary")
 
'= Enter in Pivot Table Name
  PivotName1 = "DemoDetails"
  PivotName2 = "Lead Details"
  PivotName3 = "OpptyDetails"
  PivotName4 = "SalesDetails"
 
'> Change Pivot Table Data Source Range Address
    'Pivot 1 range change
    Pivot_sht.PivotTables(PivotName1).ChangePivotCache _
      ThisWorkbook.PivotCaches.Create( _
      SourceType:=xlDatabase, _
      SourceData:=Data_sht1)
    'Pivot 2 range change
    Pivot_sht.PivotTables(PivotName2).ChangePivotCache _
      ThisWorkbook.PivotCaches.Create( _
      SourceType:=xlDatabase, _
      SourceData:=Data_sht2)
    'Pivot 3 range change
    Pivot_sht.PivotTables(PivotName3).ChangePivotCache _
      ThisWorkbook.PivotCaches.Create( _
      SourceType:=xlDatabase, _
      SourceData:=Data_sht3)
    'Pivot 4 range change
    Pivot_sht.PivotTables(PivotName4).ChangePivotCache _
      ThisWorkbook.PivotCaches.Create( _
      SourceType:=xlDatabase, _
      SourceData:=Data_sht4)
 
'Ensure Pivot Table is Refreshed
    Pivot_sht.PivotTables(PivotName1).RefreshTable
    Pivot_sht.PivotTables(PivotName2).RefreshTable
    Pivot_sht.PivotTables(PivotName3).RefreshTable
    Pivot_sht.PivotTables(PivotName4).RefreshTable
End sub

With this sub, I am receiving Runtime error ‘440’ – Method ‘Create of object ‘Pivot Caches’ failed and point out to 'Pivot 1 range change clause…I am sure it would be the same issue for the other three.
Can anyone see what I am missing here?

Sub 2

Sub Refresh_Child_Tabs()
    Dim ws As Worksheet
'--Dim Source Data Ranges
    Dim DD As Range
    Dim LD As Range
    Dim OD As Range
    Dim SD As Range
'--Dim Primary Pivot Caches
    Dim PT As PivotTable
    Dim pt1 As PivotTable
    Dim pt2 As PivotTable
    Dim pt3 As PivotTable
    Dim pt4 As PivotTable
 
'= Source Data Ranges
    Set DD = Sheets("Demo Details").Range("A:AF")
    Set LD = Sheets("Lead Details").Range("A:N")
    Set OD = Sheets("Opportunity Details").Range("A:X")
    Set SD = Sheets("Sales Details").Range("A:AD")
   
'= Primary Pivot Caches
    Set pt1.CacheIndex = Sheets("TalentBin Summary").PivotTables("DemoDetails").CacheIndex
    Set pt2.CacheIndex = Sheets("TalentBin Summary").PivotTables("LeadDetails").CacheIndex
    Set pt3.CacheIndex = Sheets("TalentBin Summary").PivotTables("OpptyDetails").CacheIndex
    Set pt4.CacheIndex = Sheets("TalentBin Summary").PivotTables("SalesDetails").CacheIndex
      
    '> Refresh Child Pivots in workbook
        For Each ws In ActiveWorkbook
            '--Refresh Summary Pivots
            For Each PT In ws.PivotTables
                Select Case ws.PivotTables
                    Case ws.PivotTables("DDetails")
                        PT.CacheIndex = pt1
                        PT.PivotCache.Refresh
                    Case ws.PivotTables("LDetails")
                        PT.CacheIndex = pt2
                        PT.PivotCache.Refresh
                    Case ws.PivotTables("ODetails"), ws.PivotTables("ODetails2")
                        PT.CacheIndex = pt3
                        PT.PivotCache.Refresh
                    Case ws.PivotTables("SDetails"), ws.PivotTables("SDetails2")
                        PT.CacheIndex = pt4
                        PT.PivotCache.Refresh
                End Select
            Next PT
        Next ws
 
End Sub

As for my second subroutine, I am getting a Compile error: Wrong number of arguments or invalid property assignment…Points out to '= Primary Pivot Caches. Again I cannot see what I missed.

Any help or guidance will be greatly appreciated; I am a novice in ever going training!

Regards,

MBM2016

 

[Worf]

Hello

Try it like this:

'= Primary Pivot Caches
    pt1.CacheIndex = Sheets("TalentBin Summary").PivotTables("DemoDetails").CacheIndex
    pt2.CacheIndex = Sheets("TalentBin Summary").PivotTables("LeadDetails").CacheIndex
    pt3.CacheIndex = Sheets("TalentBin Summary").PivotTables("OpptyDetails").CacheIndex
    pt4.CacheIndex = Sheets("TalentBin Summary").PivotTables("SalesDetails").CacheIndex

Are you still having trouble with the other routine?