CODEWITHSUNDEEP
vbaexcelpivotpowerpivot
Akire
Akire

Reputation: 169

Only update certain worksheets - VBA update Pivot Cache

I have a workbook consisting of to sets of Pivot Tables. One Set is fixed and the datasourse does not need to be updated. The other Set is for Pivot Tables where the data is updated weekly.

I have below VBA:

Sub AdjustPivotDataRangeAll()
    Dim PT As PivotTable, pc As PivotCache
    Dim dataSheet As Worksheet, WS As Worksheet, wks As Worksheet
    Dim StartPoint As Range, dataSource As Range, NewRange As String
    Dim wks1 As Worksheet
    Dim pt1 As PivotTable
    Dim i As Long
    Dim pvts As SlicerPivotTables

    ' Datasource workbook and worksheet
    Set wkb = Workbooks("XXX.xlsb")
    Set wks = wkb.Worksheets("Data")
    ' Worksheets in Workbook
    Set WS1 = ActiveWorkbook.Worksheets("WS1")
    Set PT = WS1.PivotTables("PivotTable01")

    ''Update Cache for PivotTable1

    ' Dynamically retrieve range address of data
     Set dataSource = wks.Range("A1").CurrentRegion

    ' update pivot source and refresh
        PT.ChangePivotCache ThisWorkbook.PivotCaches.Create( _
            SourceType:=xlDatabase, _
             SourceData:=dataSource)
        PT.RefreshTable

    ''change pivot cache for all Pivot Tables in workbook

    For Each wks1 In ActiveWorkbook.Worksheets
     For Each pt1 In wks1.PivotTables
        pt1.CacheIndex = Sheets("FY19-Pivot Country").PivotTables("PivotTable01").CacheIndex
     Next pt1
 ''  Next wks1

End Sub

This code is updating the Pivot Caches for all of my Pivot tables in the entire Workbook, but I only want it updated for two of my worksheets. Do anyone have any idea how to modify this code to do only update certain worksheets?

Thanks in advance

Upvotes: 0

Views: 153

Answers (1)

Denyo
Denyo

Reputation: 117

instead of:

' this part is a loop through all your worksheets
    For Each wks1 In ActiveWorkbook.Worksheets
        For Each pt1 In wks1.PivotTables
            pt1.CacheIndex = Sheets("FY19-Pivot Country").PivotTables("PivotTable01").CacheIndex
       `enter code here`
       Next pt1
      `enter code here`
    Next wks1

' replace it with this. declare the two sheets, that should be updated

    set worksheet1 = ActiveWorkbook.Worksheets("worksheet1withpivot")
    set worksheet2 = ActiveWorkbook.Worksheets("worksheet2withpivot")

    worksheet1.CacheIndex = Sheets("FY19-Pivot Country").PivotTables("PivotTable01").CacheIndex
    worksheet2.CacheIndex = Sheets("FY19-Pivot Country").PivotTables("PivotTable01").CacheIndex

code is untested

Upvotes: 0

Related Questions