VBA to change Data Source in Pivot Table - Getting invalid procedure call or argument

[parkerbelt]

I'm trying to use VBA to change the data source for 3 pivot tables that I have and I'm getting Invalid procedure call or argument at this bit of code:

Pivot_sht.PivotTables(PivotName2).ChangePivotCache _
ThisWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=NewRange)

Here is the code that I'm using:

'PURPOSE: Automatically readjust a Pivot Table's data source range
'SOURCE: www.TheSpreadsheetGuru.com/The-Code-Vault


    Dim Data_sht As Worksheet
    Dim Pivot_sht As Worksheet
    Dim StartPoint As Range
    Dim DataRange As Range
    Dim PivotName2 As String
    Dim PivotName3 As String
    Dim PivotName4 As String
    Dim NewRange As String


    'Set Variables Equal to Data Sheet and Pivot Sheet
'        Set Data_sht = ThisWorkbook.Worksheets("Sheet1")
'        Set Pivot_sht = ThisWorkbook.Worksheets("Sheet2")
        Set Data_sht = Sheets("DATA DUMP")
'        Set Data_sht = ThisWorkbook.Worksheets("DATA DUMP")
        Set Pivot_sht = Sheets("PTs")


    'Enter in Pivot Table Name
'        PivotName = "PivotTable1"
        PivotName2 = "PivotTable2"
        PivotName3 = "PivotTable3"
        PivotName4 = "PivotTable4"
        
        
    'Dynamically Retrieve Range Address of Data
'        Set StartPoint = Data_sht.Range("A1")
        Set StartPoint = Data_sht.Range("A25")
        'Set DataRange = Data_sht.Range(StartPoint, StartPoint.SpecialCells(xlLastCell))
        Set DataRange = StartPoint.CurrentRegion
  
  
        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(25)) > 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 Table 2
        Pivot_sht.PivotTables(PivotName2).ChangePivotCache _
            ThisWorkbook.PivotCaches.Create( _
            SourceType:=xlDatabase, _
            SourceData:=NewRange)
      'Pivot Table 3
        Pivot_sht.PivotTables(PivotName3).ChangePivotCache _
            ThisWorkbook.PivotCaches.Create( _
            SourceType:=xlDatabase, _
            SourceData:=NewRange)
      'Pivot Table 4
        Pivot_sht.PivotTables(PivotName4).ChangePivotCache _
            ThisWorkbook.PivotCaches.Create( _
            SourceType:=xlDatabase, _
            SourceData:=NewRange)
            
            
            
    'Ensure Pivot Table is Refreshed
      'Pivot Table 2
        Pivot_sht.PivotTables(PivotName2).RefreshTable
      'Pivot Table 3
        Pivot_sht.PivotTables(PivotName3).RefreshTable
      'Pivot Table 4
        Pivot_sht.PivotTables(PivotName4).RefreshTable
        
        
    'Complete Message
        MsgBox PivotName2 & "'s data source range has been successfully updated!"
        MsgBox PivotName3 & "'s data source range has been successfully updated!"
        MsgBox PivotName4 & "'s data source range has been successfully updated!"

 

[parkerbelt]

I figured out a solution that required way less code. Here's what I used:

Sheets("DATA DUMP").Select
    ActiveSheet.ShowAllData
 
    Range("A26").Select
    
    Selection.CurrentRegion.Select


    DataArea = "DATA DUMP!R25C1:R" & Selection.Rows.Count & "C" & Selection.Columns.Count
 
    
    Sheets("PTs").Select
    Range("D5").Select
    
    
    ActiveSheet.PivotTables("PivotTable2").ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:=DataArea, _
        Version:=xlPivotTableVersion14)