Solved: Refresh my pivot table

[abraham30]

Hi all,

I want to refresh my pivot table (sheet1) by referring the data of sheet2 by simply writing the VBA code.

Data of sheet2 are frequently changing. The recorded macro is below.

sub macro()
Sheets("Overview").Select
ActiveSheet.PivotTables("PivotTable4").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"C:\Desktop\[asd.xlsm]RAW DATA!R1C1:R30C6", Version:= _
xlPivotTableVersion14)
Range("C20").Select
ActiveSheet.PivotTables("PivotTable4").PivotCache.Refresh
End Sub

Advance thanks

[Bob Phillips]

Sub macro()

With Sheets("Sheet1")

.PivotTables("PivotTable4").ChangePivotCache _
ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=Worksheets("Sheet2").UsedRange, _
Version:=xlPivotTableVersion14)
End With

ActiveSheet.PivotTables("PivotTable4").PivotCache.Refresh
End Sub

[abraham30]

Hello Xld,
Its working perfectly. One simple query.

Suppose I run the macro in active sheet "sheet2". It refreshes the pivot but I am getting error like

Run-time error '1004'
Unable to get the PivotTables property of the worksheet class


Advance thanks

[Bob Phillips]

Try this modification

Sub macro()

With Sheets("Sheet1")

.PivotTables("PivotTable4").ChangePivotCache _
ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=Worksheets("Sheet2").UsedRange, _
Version:=xlPivotTableVersion14)

.PivotTables("PivotTable4").PivotCache.Refresh
End With
End Sub

[abraham30]

Thanks Xld for ur kind help