Update multiple pivot tables to the last row of data.

[sraza]

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. The code does work for one pivot table, but i need to get it done for all.


<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">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</code>