If the changes are by typing, you could use the worksheet_change event instead:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myPT As PivotTable
For Each myPT In Me.PivotTables
myPT.RefreshTable
Next myPT
End Sub

(Same spot--and you can dump the other procedure.)

But there is a reason that MS designed pivottables to not update when you make
changes--you'll usually suffer a performance hit. If it's a giant pivottable
(or lots of giant pivottables), you may not want to do this.

But if you select a cell in one of the pivottables, the pivottable toolbar will
appear. You can click the refresh icon (Looks like an exclamation point (!)) to
refresh that one.



Luc Poppe wrote:
>
> That seems to work fine, but how can we make it such that is refreshes every
> time I update the source data on the same sheet. I want to avoid creating a
> blank sheet just to go back and forth, something that not everyone will
> remember to do.
>
> "Dave Peterson" wrote:
>
> > This event fires when the worksheet is activated.
> >
> > So click on another worksheet, then come back and select this worksheet.
> >
> > Luc Poppe wrote:
> > >
> > > Debra,
> > > You are referring to the correct thread. I used the following code:
> > >
> > > Option Explicit
> > > Private Sub Worksheet_Activate()
> > >
> > > Dim myPT As PivotTable
> > >
> > > For Each myPT In Me.PivotTables
> > > myPT.RefreshTable
> > > Next myPT
> > >
> > > End Sub
> > >
> > > and pasted it into the worksheet by right-clicking the sheet tab, selecting
> > > "view-code", pasting the code above into the VB code window, click save and
> > > exited VB app. I did not perform any other functions or selections available
> > > in the VB app, as I'm not familiar with this.
> > > Thanks for the reply,
> > > Luc Poppe
> > >
> > > "Debra Dalgleish" wrote:
> > >
> > > > I guess you mean the code that Dave Peterson suggested in this thread:
> > > >
> > > >
> > > > http://groups.google.ca/group/micros...5c284c4?hl=en&
> > > >
> > > > Which code did you use, and where did you store the code?
> > > >
> > > > Luc Poppe wrote:
> > > > > I need to auto refresh a pivot table on a worksheet in Excel 2002. Tried to
> > > > > use suggested macro from Dave Patterson of 8/23/04 in this forum, but it does
> > > > > not work, it was a macro for excel 2000. I have no experience with Visual
> > > > > Basic.
> > > >
> > > >
> > > > --
> > > > Debra Dalgleish
> > > > Excel FAQ, Tips & Book List
> > > > http://www.contextures.com/tiptech.html
> > > >
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson