Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Luc Poppe
 
Posts: n/a
Default auto refresh of pivot table

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.
  #2   Report Post  
Debra Dalgleish
 
Posts: n/a
Default

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

  #3   Report Post  
Dave Peterson
 
Posts: n/a
Default

In a private reply, I suggested that the code may be in the wrong spot.

I said to verify that the code is behind the worksheet with the pivottables--by
rightclicking on the worksheet tab, selecting view code and pasting the code
into the code window.

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.


--

Dave Peterson
  #4   Report Post  
Luc Poppe
 
Posts: n/a
Default

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


  #5   Report Post  
Dave Peterson
 
Posts: n/a
Default

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


  #6   Report Post  
Luc Poppe
 
Posts: n/a
Default

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

  #7   Report Post  
Dave Peterson
 
Posts: n/a
Default

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
  #8   Report Post  
Luc Poppe
 
Posts: n/a
Default

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

  #9   Report Post  
Dave Peterson
 
Posts: n/a
Default

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
  #10   Report Post  
Luc Poppe
 
Posts: n/a
Default

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sheet protection code conflicts with Pivot Table "auto refresh" KG Excel Discussion (Misc queries) 6 December 22nd 05 12:16 AM
Pivot table Data refresh SSD1 Excel Worksheet Functions 2 June 17th 05 09:13 PM
Pivot Table Problems Rachel Gonsior Excel Discussion (Misc queries) 3 March 21st 05 08:24 PM
Pivot table refresh Excel GuRu Excel Worksheet Functions 2 February 23rd 05 02:47 AM
ability to auto file column(s) on a pivot table km Excel Worksheet Functions 1 December 20th 04 10:39 PM


All times are GMT +1. The time now is 12:25 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"