VBA Pivot Data Source Change Error "method pivottables of object _worksheet failed"

[Nitesh Khot]

Unable to change Existing Pivot Table data source..

Getting error "method pivottables of object _worksheet failed"

Set Pivot_sht = ThisWorkbook.Sheets("Summary")
Pivot_sht.PivotTables("Manpower").ChangePivotCache _
                 ThisWorkbook.PivotCaches.Create( _
                 SourceType:=xlDatabase, _
                 SourceData:=Sheets("Data").Range("A1:AD" & lrows))

 

[Chihiro]

This part...

SourceData:=Sheets("Data").Range("A1:AD" & lrows)

Can't be range object, but rather string using R1C1 reference. So...

SourceData:="Data!R1C1:R" & lrows & "C30"

 

[Nitesh Khot]

Have tried this but getting same error...

Please Note : I am copying Pivot Table from Another Workbook into new workbook and Then I had changed Pivot Table name in new workbook and then trying to change Source Data but error occurred.

Dim Pivot_sht As Worksheet
Set Pivot_sht = ThisWorkbook.Sheets("Summary")
Pivot_sht.PivotTables("Manpower").ChangePivotCache _
                 ThisWorkbook.PivotCaches.Create( _
                 SourceType:=xlDatabase, _
                 SourceData:="Data!R1C1:R" & lrows & "C30")