auto refresh of pivot table

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
>

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

Using your latest routine does indeed cause the tabel to update. Although
being a very small table (1 column, 8 rows), excel seems to go through a
series of calculations that causes the worksheet to flash for about 2
seconds. Is it recalcualting 8 times, since I have 8 rows ?? or what's going
on ?

"Dave Peterson" wrote:

> 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
>

Ahhhhhhhh.

When the pivottables are refreshed, it causes the worksheet_Change event to fire
again. In my haste, I didn't disable the events (I just copied the code over).

Try this version.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myPT As PivotTable
Application.enableevents = false
For Each myPT In Me.PivotTables
myPT.RefreshTable
Next myPT
application.enableevents = true
End Sub



Luc Poppe wrote:
>
> Using your latest routine does indeed cause the tabel to update. Although
> being a very small table (1 column, 8 rows), excel seems to go through a
> series of calculations that causes the worksheet to flash for about 2
> seconds. Is it recalcualting 8 times, since I have 8 rows ?? or what's going
> on ?
>
> "Dave Peterson" wrote:
>
> > 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
> >

--

Dave Peterson

Works like charm now !
Thanks a lot.

"Dave Peterson" wrote:

> Ahhhhhhhh.
>
> When the pivottables are refreshed, it causes the worksheet_Change event to fire
> again. In my haste, I didn't disable the events (I just copied the code over).
>
> Try this version.
>
> Option Explicit
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim myPT As PivotTable
> Application.enableevents = false
> For Each myPT In Me.PivotTables
> myPT.RefreshTable
> Next myPT
> application.enableevents = true
> End Sub
>
>
>
> Luc Poppe wrote:
> >
> > Using your latest routine does indeed cause the tabel to update. Although
> > being a very small table (1 column, 8 rows), excel seems to go through a
> > series of calculations that causes the worksheet to flash for about 2
> > seconds. Is it recalcualting 8 times, since I have 8 rows ?? or what's going
> > on ?
> >
> > "Dave Peterson" wrote:
> >
> > > 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
> > >
>
> --
>
> Dave Peterson
>