Array in VBA Excel RRS feed

  • Question

  • Is the below a safe way to use an array?

    I have a workbook with various worksheets. I do not want to update the pivot table in sheet OPS.

    Is the below a safe approach?


     Dim sht As Worksheet

    Dim pvt As PivotTable

     For Each sht In Sheets(Array("Budget Hold", " Budget Pivot", "Budget Rpt"))

        For Each pvt In sht.PivotTables


          'Change Pivot Table's data source range address

            pvt.ChangePivotCache _

              ActiveWorkbook.PivotCaches.Create( _

              SourceType:=xlDatabase, _



          'Ensure Pivot Table is refreshed


            Next pvt

      Next sht

    Friday, March 18, 2016 7:41 PM

All replies

  • Because you are specifically referencing the pivot tables in the worksheets assigned to sht then it should only update those pivot tables but why not simply make a backup copy of the workbook and then test it and see what it does.

    Regards, OssieMac

    Sunday, March 20, 2016 9:08 PM
  • it worked. I just wanted to make sure that I followed the correct procedure or if it can be simplified
    • Edited by ellis Wa Tuesday, March 22, 2016 3:07 PM
    Tuesday, March 22, 2016 3:06 PM