Hi.

I modified the below macro which I found researching online to dynamically update multiple source data and the associated pivot tables but with my limited knowledge I was unsuccessful.
The code works but only updates the last Data_sht and Pivot_sht and it associated PivotName which is "PYQTDPVT" in the code below. However I want the macro to update all the data sources and pivots all at once.
Below is the code I tried to unsuccesfully edit;

Sub AdjustPivotDataRange()

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("IND MAC CY MTD") ' Update Pivot Source Data tab
  Set Pivot_sht = ThisWorkbook.Worksheets("CY MTD Pivot")  ' Update Pivot Table tab
  Set Data_sht = ThisWorkbook.Worksheets("IND MAC CY QTD")
  Set Pivot_sht = ThisWorkbook.Worksheets("CY QTD Pivot")
  Set Data_sht = ThisWorkbook.Worksheets("IND MAC CY & PY YTD Summary")
  Set Pivot_sht = ThisWorkbook.Worksheets("CYTD Pivot")
  Set Data_sht = ThisWorkbook.Worksheets("IND MAC CY & PY YTD Summary")
  Set Pivot_sht = ThisWorkbook.Worksheets("PYTD Pivot")
  Set Data_sht = ThisWorkbook.Worksheets("IND MAC PY MTD")
  Set Pivot_sht = ThisWorkbook.Worksheets("PY MTD Pivot")
  Set Data_sht = ThisWorkbook.Worksheets("IND MAC PY QTD")
  Set Pivot_sht = ThisWorkbook.Worksheets("PY QTD Pivot")

'Enter in Pivot Table Name
  PivotName = "CYMTDPVT"
  PivotName = "CYQTDPVT"
  PivotName = "CYTDPvt"
  PivotName = "PYTDPvt"
  PivotName = "PYMTDPVT"
  PivotName = "PYQTDPVT"
  

'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( _
    SourceType:=xlDatabase, _
    SourceData:=NewRange)
      
'Ensure Pivot Table is Refreshed
  Pivot_sht.PivotTables(PivotName).RefreshTable
  'ThisWorkbook.RefreshAll

'Next Pivot_sht
'Next Data_sht
'Next Pivotname

'Complete Message
  MsgBox PivotName & "'s data source range has been successfully updated!"

End Sub
I appreciate the help

Pi