0

I have multiple pivot tables on 5 sheets named in the code. Data source for all of them is "CUIC data", which i update daily.

I want to change the data source and refresh all the pivot tables in all of my worksheets. The code i have works for one sheet, but i dont know how to get it done for multiple pivot tables. 

Private Sub CommandButton1_Click()
'PURPOSE: Automatically readjust a Pivot Table's data source range

Dim Data_sht As Worksheet
Dim Pivot_sht As Worksheet
Dim StartPoint As Range
Dim DataRange As Range
Dim PivotName As String
Dim NewRange As String

'Set Variables Equal to Data Sheet and Pivot Sheet
  Set Data_sht = ThisWorkbook.Worksheets("CUIC data")
  Set Pivot_sht = ThisWorkbook.Worksheets("Agent,Projections,Supervisor,Platinum Club - MTD,Platinum Club - YTD")


'Enter in Pivot Table Name
  PivotName = ("Projections,Supervisor,Agent,mtd,ytd")


'Dynamically Retrieve Range Address of Data
  Set StartPoint = Data_sht.Range("A1")
  Set DataRange = Data_sht.Range(StartPoint, StartPoint.SpecialCells(xlLastCell))

  NewRange = Data_sht.Name & "!" & _
    DataRange.Address(ReferenceStyle:=xlR1C1)

'Make sure every column in data set has a heading and is not blank (error prevention)
  If WorksheetFunction.CountBlank(DataRange.Rows(1)) > 0 Then
    MsgBox "One of your data columns has a blank heading." & vbNewLine _
      & "Please fix and re-run!.", vbCritical, "Column Heading Missing!"
    Exit Sub
  End If

'Change Pivot Table Data Source Range Address
  Pivot_sht.PivotTables(PivotName).ChangePivotCache _
    ThisWorkbook.PivotCaches.Create(xlDatabase, SourceData:=NewRange)



'Ensure Pivot Table is Refreshed
  Pivot_sht.PivotTables(PivotName).RefreshTable

'Complete Message
  MsgBox PivotName & "'s data source range has been successfully updated!"
End Sub
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Shobi
  • 95
  • 1
  • 11
  • 1
    Possible duplicate of https://stackoverflow.com/questions/70947/refreshing-all-the-pivot-tables-in-my-excel-workbook-with-a-macro – W-hit May 08 '19 at 22:04

1 Answers1

0

Click on Name manager in Formula Tab.

Click define name

Name as Dyn_Range

refers to =OFFSET('CUIC data'!$A$1,0,0,COUNTA('CUIC data'!$A:$A),COUNTA('CUIC data'!$1:$1))

click on each pivot table - click change data source in Pivot table Analyze

Type Dyn_Range in Table/Range:

click ok

Post setting range for all pivot tables

All you need to do it is Go to Data Tab ➜ Connections ➜ Refresh All

murugesan kasirajan
  • 1
  • 1