locked
refresh Pivot table with New Data Source via VBA RRS feed

  • Question

  • You cannot vote on your own post                    
    0
                


    So, below is what i have to update the 10 pivot tables.Some spreadsheet can have more than 1 pivot table.
    is the below the best approach?
    I want to make sure that all the tables are being updated with the correct information and new row source.
    data is not being corrupted
    The pivot table can have up to or more than 15,000 rows of data


    Thanks!!!

    Dim sht As Worksheet
    Dim pvt As PivotTable


    'Create SourceData address
    Sheets("OENS OMOs").Select
     
     
      Range("A1").Select
     Selection.CurrentRegion.Select
    DataArea = "OENS OMOs!R1C1:R" & Selection.Rows.Count & "C" & Selection.Columns.Count
     
    'Loop through and update pivot tables with new data source range
      For Each sht In ActiveWorkbook.Worksheets
        For Each pvt In sht.PivotTables
         
          'Change Pivot Table's data source range address
            pvt.ChangePivotCache _
              ActiveWorkbook.PivotCaches.Create( _
              SourceType:=xlDatabase, _
              SourceData:=DataArea)
           
          'Ensure Pivot Table is refreshed
            pvt.RefreshTable
            Next pvt
      Next sht
    'Completion Message
      MsgBox "All Pivot Table Data Source Ranges have been updated in this workbook!", vbInformation

    'Difference between the budget
      Sheets("Budget Rpt Pivot").Select
     Range("D6:D" & Cells(Rows.Count, "A").End(xlUp).Row).FormulaR1C1 = "=(RC[-2])-RC[-1]"

    • Edited by ellis Wa Friday, March 18, 2016 3:07 PM
    Monday, March 14, 2016 3:16 PM

All replies