Refreshing Pivot Tables

I have a certain workbook where I keep some statistics. I've created a small PivotTable, with a PivotChart attached to it. I like to have the chart update in real time, so I use a bit of code to do that. The following code goes in the sheet module for the particular sheet I want to update.

Private Sub Worksheet_Change(ByVal Target As Range)
' runs whenever anything on the worksheet is changed

With Application
  .ScreenUpdating = False
  .EnableEvents = False
End With

Dim pt As PivotTable
For Each pt In ActiveSheet.PivotTables
  pt.RefreshTable
Next pt

With Application
  .ScreenUpdating = True
  .EnableEvents = True
End With

End Sub

Certain weirdness starts happening with the sheet, however. For example, if you copy a range, the marching ants around the cell(s) disappears right after the first paste, so you can't copy and then paste repeatedly until you press Escape.

Related Articles:

• March 22, 2010 -- The Census is coming!