PDA

View Full Version : Solved: Refresh my pivot table



abraham30
03-04-2013, 01:53 PM
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

xld
03-04-2013, 02:33 PM
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
03-05-2013, 10:39 AM
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

xld
03-06-2013, 07:37 AM
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
03-06-2013, 09:39 AM
Thanks Xld for ur kind help