refresh pivot table when data changes

[robert111]

good morning all.

I would like to automatically update my pivot table whenever I make changes to the raw data. I have tried:

private sub worksheet_activate()
run "pivotmacro"
end sub

and

private sub worksheet_calculate()
sheets("pivot").pivottables("pivottable1").refreshtable
end sub

but neither works......help

[Carim]

Hi,

Following code should help ...
( if Pivot Table is located in its own worksheet)

Private Sub Worksheet_Activate()
ActiveSheet.PivotTables("PivotTable1").RefreshTable
End Sub

HTH
Carim

[robert111]

thanks Carim, I am very new at VBA, I have entered your code, but nothing happens, do I need to activate or run it, is it meant to refresh the pivot table continuously? On any data change would be ideal...

Bob

[Carim]

Hi,

Go to worksheet tab
right click
select view code
copy code

that's it ...

HTH
Carim

[Carim]

Robert,

There are a couple of traps to avoid

1. If the pivot table is located in the same worksheet as your data

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.PivotTables("PivotTable1").RefreshTable
End Sub

2. Make sure the Name of your pivot table is "PivotTable1"
While located in the pivot table, right click, select Table Options and correct the name, if need be ...

HTH
Carim

[robert111]

Many thanks, Carim, it works now, although the screen flickers for about a second after I change some base data, although I can see the new pivot total is correct from the outset. Data and pivot are in the same worksheet. If the flicker happens to you also, obviously I have to live with it.

(only using test data at present, 2 cols by 10 rows)

Bob