Why is the invalid procedure or call error occurring on the change pivot table range line?

Question

Is the issue with the changepivottablecache line?

I have tried to this workbook, but I am able to execute the line to refresh the table.

I have looked at several fixes online but none work as expected.

Dim Data_Sheet As Worksheet
Dim Pivot_Sheet As Worksheet
Dim StartPoint As Range
Dim DataRange As Range
Dim PivotName As String
Dim NewRange As String
Dim LastCol As Long
Dim lastRow As Long

'Set Pivot Table & Source Worksheet
Set Data_Sheet = ThisWorkbook.Worksheets("Data")
Set Pivot_Sheet = ThisWorkbook.Worksheets("SheetTable")

'Enter in Pivot Table Name
PivotName = "PivotTable2"
'Defining Staring Point & Dynamic Range
Data_Sheet.Activate
Set StartPoint = Data_Sheet.Range("A1")
LastCol = StartPoint.End(xlToRight).Column
DownCell = StartPoint.End(xlDown).Row
Set DataRange = Data_Sheet.Range(StartPoint, Cells(DownCell, LastCol))
NewRange = Data_Sheet.Name & "!" & DataRange.Address(ReferenceStyle:=xlR1C1)

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

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

'Complete Message
Pivot_Sheet.Activate
MsgBox "Your Pivot Table is now updated."

End Sub

Answer 1

I was able to successfully change an exisiting pivot table using the following code. You may need to adapt to your situation. Original code from MSDN Forum

Sub ChangePTCache()
Dim PTRange As Range
Dim con_end As String
con_end = Range("'Sheet1'!A1").End(xlDown).Offset(0, 1).Address
Set PTRange = Range("'Sheet1'!A1:" & con_end)
Sheets(1).PivotTables(1).ChangePivotCache ActiveWorkbook.PivotCaches. _
    Create(xlDatabase, PTRange)
End Sub