Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sheet protection code conflicts with Pivot Table "auto refresh" | Excel Discussion (Misc queries) | |||
Pivot table Data refresh | Excel Worksheet Functions | |||
Pivot Table Problems | Excel Discussion (Misc queries) | |||
Pivot table refresh | Excel Worksheet Functions | |||
ability to auto file column(s) on a pivot table | Excel Worksheet Functions |