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.

[VBA]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
[/VBA]
Advance thanks

[Bob Phillips]

[VBA]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[/VBA]

[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

[VBA]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
[/VBA]

[abraham30]

Thanks Xld for ur kind help