Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Refreshing Pivots in multiple tabs
So I have this simple macro that refreshes the pivot tables in a template.
However, I have it specifying the Sheet number and Pivot Table names. The problem comes when a user would deletes or even hides a tab or copy over a pivot without renaming the table. Is there any way I can program it such that it will refresh all pivot tables in a tab that is between 2 tabs. For example: tab 1 tab 2 tab 3 tab 4 Refresh all pivots that are between tab 1 and 4. Then I can add tabs in between and not have to worry about Pivot Table names. Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Refreshing Pivots in multiple tabs
Hi Fred
Try using ActiveWorkbook.RefreshAll -- Regards Roger Govier "FredL" wrote in message ... So I have this simple macro that refreshes the pivot tables in a template. However, I have it specifying the Sheet number and Pivot Table names. The problem comes when a user would deletes or even hides a tab or copy over a pivot without renaming the table. Is there any way I can program it such that it will refresh all pivot tables in a tab that is between 2 tabs. For example: tab 1 tab 2 tab 3 tab 4 Refresh all pivots that are between tab 1 and 4. Then I can add tabs in between and not have to worry about Pivot Table names. Thanks. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Refreshing Pivots in multiple tabs
Very nice! Simple and elegant.
Thanks! "Roger Govier" wrote: Hi Fred Try using ActiveWorkbook.RefreshAll -- Regards Roger Govier "FredL" wrote in message ... So I have this simple macro that refreshes the pivot tables in a template. However, I have it specifying the Sheet number and Pivot Table names. The problem comes when a user would deletes or even hides a tab or copy over a pivot without renaming the table. Is there any way I can program it such that it will refresh all pivot tables in a tab that is between 2 tabs. For example: tab 1 tab 2 tab 3 tab 4 Refresh all pivots that are between tab 1 and 4. Then I can add tabs in between and not have to worry about Pivot Table names. Thanks. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Refreshing Pivots in multiple tabs
This worked so well, that the formulas that the other pivot depends on
doesn't have a chance to get updated. So the way I have this setup is that I have a Main Pivot table with calculations. I then have to group those calculations together and so run a pivot of those. Since I can't do a pivot on pivot, I have a formula that points to the results. The second pivot then uses those formulas as its source. With the solution below, those formulas doesn't have time to update when the main pivot refreshes. Therefore, the second pivot still has the results from the prior refresh. Any way around this? "Roger Govier" wrote: Hi Fred Try using ActiveWorkbook.RefreshAll -- Regards Roger Govier "FredL" wrote in message ... So I have this simple macro that refreshes the pivot tables in a template. However, I have it specifying the Sheet number and Pivot Table names. The problem comes when a user would deletes or even hides a tab or copy over a pivot without renaming the table. Is there any way I can program it such that it will refresh all pivot tables in a tab that is between 2 tabs. For example: tab 1 tab 2 tab 3 tab 4 Refresh all pivots that are between tab 1 and 4. Then I can add tabs in between and not have to worry about Pivot Table names. Thanks. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Refreshing Pivots in multiple tabs
Hi Fred
If there is only 1 Pivot table on each of your sheets, you could use the following code Sub RefreshPivots() Dim myArray As Variant, ws As Worksheet Set myArray = Sheets(Array("Sheet2", "Sheet3", "Sheet5")) For Each ws In myArray ws.PivotTables(1).PivotCache.Refresh Next End Sub Change the Array of Sheets to the names of your sheets, in the order you want them calculated. If there is more than one Pivot Table on each sheet, then use the following code Sub RefreshPivots2() Dim myArray As Variant, ws As Worksheet, pt As PivotTable Dim i As Integer, ptcount As Integer Dim ptname As String Set myArray = Sheets(Array("Sheet2", "Sheet3", "Sheet5")) For Each ws In myArray ptcount = ws.PivotTables.Count For i = 1 To ptcount ptname = ws.PivotTables(i).Name ws.PivotTables(ptname).PivotCache.Refresh Next i Next End Sub The second macro will work with a single PT per sheet as well. Again, change the array of sheet names to suit. -- Regards Roger Govier "FredL" wrote in message ... This worked so well, that the formulas that the other pivot depends on doesn't have a chance to get updated. So the way I have this setup is that I have a Main Pivot table with calculations. I then have to group those calculations together and so run a pivot of those. Since I can't do a pivot on pivot, I have a formula that points to the results. The second pivot then uses those formulas as its source. With the solution below, those formulas doesn't have time to update when the main pivot refreshes. Therefore, the second pivot still has the results from the prior refresh. Any way around this? "Roger Govier" wrote: Hi Fred Try using ActiveWorkbook.RefreshAll -- Regards Roger Govier "FredL" wrote in message ... So I have this simple macro that refreshes the pivot tables in a template. However, I have it specifying the Sheet number and Pivot Table names. The problem comes when a user would deletes or even hides a tab or copy over a pivot without renaming the table. Is there any way I can program it such that it will refresh all pivot tables in a tab that is between 2 tabs. For example: tab 1 tab 2 tab 3 tab 4 Refresh all pivots that are between tab 1 and 4. Then I can add tabs in between and not have to worry about Pivot Table names. Thanks. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Refreshing Pivots in multiple tabs
What if I refresh the main pivot, do a pause so that the formulas can catch
up, then refresh all; understandably, the main pivot will refresh again. Is there a pause command? "Roger Govier" wrote: Hi Fred If there is only 1 Pivot table on each of your sheets, you could use the following code Sub RefreshPivots() Dim myArray As Variant, ws As Worksheet Set myArray = Sheets(Array("Sheet2", "Sheet3", "Sheet5")) For Each ws In myArray ws.PivotTables(1).PivotCache.Refresh Next End Sub Change the Array of Sheets to the names of your sheets, in the order you want them calculated. If there is more than one Pivot Table on each sheet, then use the following code Sub RefreshPivots2() Dim myArray As Variant, ws As Worksheet, pt As PivotTable Dim i As Integer, ptcount As Integer Dim ptname As String Set myArray = Sheets(Array("Sheet2", "Sheet3", "Sheet5")) For Each ws In myArray ptcount = ws.PivotTables.Count For i = 1 To ptcount ptname = ws.PivotTables(i).Name ws.PivotTables(ptname).PivotCache.Refresh Next i Next End Sub The second macro will work with a single PT per sheet as well. Again, change the array of sheet names to suit. -- Regards Roger Govier "FredL" wrote in message ... This worked so well, that the formulas that the other pivot depends on doesn't have a chance to get updated. So the way I have this setup is that I have a Main Pivot table with calculations. I then have to group those calculations together and so run a pivot of those. Since I can't do a pivot on pivot, I have a formula that points to the results. The second pivot then uses those formulas as its source. With the solution below, those formulas doesn't have time to update when the main pivot refreshes. Therefore, the second pivot still has the results from the prior refresh. Any way around this? "Roger Govier" wrote: Hi Fred Try using ActiveWorkbook.RefreshAll -- Regards Roger Govier "FredL" wrote in message ... So I have this simple macro that refreshes the pivot tables in a template. However, I have it specifying the Sheet number and Pivot Table names. The problem comes when a user would deletes or even hides a tab or copy over a pivot without renaming the table. Is there any way I can program it such that it will refresh all pivot tables in a tab that is between 2 tabs. For example: tab 1 tab 2 tab 3 tab 4 Refresh all pivots that are between tab 1 and 4. Then I can add tabs in between and not have to worry about Pivot Table names. Thanks. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Refreshing Pivots in multiple tabs
Hi
If ToolsoptionsCalculationis set to Automatic, then there should be no need for any pause, as the calculations will have been performed by the time the next refresh occurs. Check your setting for Calculation mode. -- Regards Roger Govier "FredL" wrote in message ... What if I refresh the main pivot, do a pause so that the formulas can catch up, then refresh all; understandably, the main pivot will refresh again. Is there a pause command? "Roger Govier" wrote: Hi Fred If there is only 1 Pivot table on each of your sheets, you could use the following code Sub RefreshPivots() Dim myArray As Variant, ws As Worksheet Set myArray = Sheets(Array("Sheet2", "Sheet3", "Sheet5")) For Each ws In myArray ws.PivotTables(1).PivotCache.Refresh Next End Sub Change the Array of Sheets to the names of your sheets, in the order you want them calculated. If there is more than one Pivot Table on each sheet, then use the following code Sub RefreshPivots2() Dim myArray As Variant, ws As Worksheet, pt As PivotTable Dim i As Integer, ptcount As Integer Dim ptname As String Set myArray = Sheets(Array("Sheet2", "Sheet3", "Sheet5")) For Each ws In myArray ptcount = ws.PivotTables.Count For i = 1 To ptcount ptname = ws.PivotTables(i).Name ws.PivotTables(ptname).PivotCache.Refresh Next i Next End Sub The second macro will work with a single PT per sheet as well. Again, change the array of sheet names to suit. -- Regards Roger Govier "FredL" wrote in message ... This worked so well, that the formulas that the other pivot depends on doesn't have a chance to get updated. So the way I have this setup is that I have a Main Pivot table with calculations. I then have to group those calculations together and so run a pivot of those. Since I can't do a pivot on pivot, I have a formula that points to the results. The second pivot then uses those formulas as its source. With the solution below, those formulas doesn't have time to update when the main pivot refreshes. Therefore, the second pivot still has the results from the prior refresh. Any way around this? "Roger Govier" wrote: Hi Fred Try using ActiveWorkbook.RefreshAll -- Regards Roger Govier "FredL" wrote in message ... So I have this simple macro that refreshes the pivot tables in a template. However, I have it specifying the Sheet number and Pivot Table names. The problem comes when a user would deletes or even hides a tab or copy over a pivot without renaming the table. Is there any way I can program it such that it will refresh all pivot tables in a tab that is between 2 tabs. For example: tab 1 tab 2 tab 3 tab 4 Refresh all pivots that are between tab 1 and 4. Then I can add tabs in between and not have to worry about Pivot Table names. Thanks. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Refreshing Pivots in multiple tabs
Hi Roger,
I do have it set to automatic. I'm using another macro to refresh the pivots in the active worksheet. Sub ProcedureA() Dim PVT As PivotTable For Each PVT In ActiveSheet.PivotTables PVT.RefreshTable Next PVT End Sub There are 5 pivots: one main one, and 4 that relies on a formula (=IF($A17="grand total","",IF(E17="","",E17))) that refrences the main pivot. Essentially, pivoting on a pivot. However, only one of the secondary pivots seems to be refreshing properly. The others are refreshing but on the old values. So the formulas doesn't have time to update before the pivot is refreshed. Is there any way to add a .5 - 1 delay before the next pivot is refreshed? Thanks, Fred "Roger Govier" wrote: Hi If ToolsoptionsCalculationis set to Automatic, then there should be no need for any pause, as the calculations will have been performed by the time the next refresh occurs. Check your setting for Calculation mode. -- Regards Roger Govier "FredL" wrote in message ... What if I refresh the main pivot, do a pause so that the formulas can catch up, then refresh all; understandably, the main pivot will refresh again. Is there a pause command? "Roger Govier" wrote: Hi Fred If there is only 1 Pivot table on each of your sheets, you could use the following code Sub RefreshPivots() Dim myArray As Variant, ws As Worksheet Set myArray = Sheets(Array("Sheet2", "Sheet3", "Sheet5")) For Each ws In myArray ws.PivotTables(1).PivotCache.Refresh Next End Sub Change the Array of Sheets to the names of your sheets, in the order you want them calculated. If there is more than one Pivot Table on each sheet, then use the following code Sub RefreshPivots2() Dim myArray As Variant, ws As Worksheet, pt As PivotTable Dim i As Integer, ptcount As Integer Dim ptname As String Set myArray = Sheets(Array("Sheet2", "Sheet3", "Sheet5")) For Each ws In myArray ptcount = ws.PivotTables.Count For i = 1 To ptcount ptname = ws.PivotTables(i).Name ws.PivotTables(ptname).PivotCache.Refresh Next i Next End Sub The second macro will work with a single PT per sheet as well. Again, change the array of sheet names to suit. -- Regards Roger Govier "FredL" wrote in message ... This worked so well, that the formulas that the other pivot depends on doesn't have a chance to get updated. So the way I have this setup is that I have a Main Pivot table with calculations. I then have to group those calculations together and so run a pivot of those. Since I can't do a pivot on pivot, I have a formula that points to the results. The second pivot then uses those formulas as its source. With the solution below, those formulas doesn't have time to update when the main pivot refreshes. Therefore, the second pivot still has the results from the prior refresh. Any way around this? "Roger Govier" wrote: Hi Fred Try using ActiveWorkbook.RefreshAll -- Regards Roger Govier "FredL" wrote in message ... So I have this simple macro that refreshes the pivot tables in a template. However, I have it specifying the Sheet number and Pivot Table names. The problem comes when a user would deletes or even hides a tab or copy over a pivot without renaming the table. Is there any way I can program it such that it will refresh all pivot tables in a tab that is between 2 tabs. For example: tab 1 tab 2 tab 3 tab 4 Refresh all pivots that are between tab 1 and 4. Then I can add tabs in between and not have to worry about Pivot Table names. Thanks. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Refreshing Pivots in multiple tabs
Fred - Did you ever get a response or find a solution to this question. I am
currently building a macro that does not work right when run full speed. If I step through the macro, it works great. I think if I added a pause at one point, the macro will run great full speed. Ron "FredL" wrote: Hi Roger, I do have it set to automatic. I'm using another macro to refresh the pivots in the active worksheet. Sub ProcedureA() Dim PVT As PivotTable For Each PVT In ActiveSheet.PivotTables PVT.RefreshTable Next PVT End Sub There are 5 pivots: one main one, and 4 that relies on a formula (=IF($A17="grand total","",IF(E17="","",E17))) that refrences the main pivot. Essentially, pivoting on a pivot. However, only one of the secondary pivots seems to be refreshing properly. The others are refreshing but on the old values. So the formulas doesn't have time to update before the pivot is refreshed. Is there any way to add a .5 - 1 delay before the next pivot is refreshed? Thanks, Fred "Roger Govier" wrote: Hi If ToolsoptionsCalculationis set to Automatic, then there should be no need for any pause, as the calculations will have been performed by the time the next refresh occurs. Check your setting for Calculation mode. -- Regards Roger Govier "FredL" wrote in message ... What if I refresh the main pivot, do a pause so that the formulas can catch up, then refresh all; understandably, the main pivot will refresh again. Is there a pause command? "Roger Govier" wrote: Hi Fred If there is only 1 Pivot table on each of your sheets, you could use the following code Sub RefreshPivots() Dim myArray As Variant, ws As Worksheet Set myArray = Sheets(Array("Sheet2", "Sheet3", "Sheet5")) For Each ws In myArray ws.PivotTables(1).PivotCache.Refresh Next End Sub Change the Array of Sheets to the names of your sheets, in the order you want them calculated. If there is more than one Pivot Table on each sheet, then use the following code Sub RefreshPivots2() Dim myArray As Variant, ws As Worksheet, pt As PivotTable Dim i As Integer, ptcount As Integer Dim ptname As String Set myArray = Sheets(Array("Sheet2", "Sheet3", "Sheet5")) For Each ws In myArray ptcount = ws.PivotTables.Count For i = 1 To ptcount ptname = ws.PivotTables(i).Name ws.PivotTables(ptname).PivotCache.Refresh Next i Next End Sub The second macro will work with a single PT per sheet as well. Again, change the array of sheet names to suit. -- Regards Roger Govier "FredL" wrote in message ... This worked so well, that the formulas that the other pivot depends on doesn't have a chance to get updated. So the way I have this setup is that I have a Main Pivot table with calculations. I then have to group those calculations together and so run a pivot of those. Since I can't do a pivot on pivot, I have a formula that points to the results. The second pivot then uses those formulas as its source. With the solution below, those formulas doesn't have time to update when the main pivot refreshes. Therefore, the second pivot still has the results from the prior refresh. Any way around this? "Roger Govier" wrote: Hi Fred Try using ActiveWorkbook.RefreshAll -- Regards Roger Govier "FredL" wrote in message ... So I have this simple macro that refreshes the pivot tables in a template. However, I have it specifying the Sheet number and Pivot Table names. The problem comes when a user would deletes or even hides a tab or copy over a pivot without renaming the table. Is there any way I can program it such that it will refresh all pivot tables in a tab that is between 2 tabs. For example: tab 1 tab 2 tab 3 tab 4 Refresh all pivots that are between tab 1 and 4. Then I can add tabs in between and not have to worry about Pivot Table names. Thanks. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Refreshing Pivots in multiple tabs
After I posted my other post, I looked around and found this code to put a
pause in your macro. This can be modified to remove the MsgBox. The pause works like a champ, but did not solve my problem. I hope it solves yours. Dim PauseTime, Start, Finish, TotalTime If (MsgBox("Press Yes to pause for 5 seconds", 4)) = vbYes Then PauseTime = 5 ' Set duration. Start = Timer ' Set start time. Do While Timer < Start + PauseTime DoEvents ' Yield to other processes. Loop Finish = Timer ' Set end time. TotalTime = Finish - Start ' Calculate total time. MsgBox "Paused for " & TotalTime & " seconds" Else End End If "FredL" wrote: Hi Roger, I do have it set to automatic. I'm using another macro to refresh the pivots in the active worksheet. Sub ProcedureA() Dim PVT As PivotTable For Each PVT In ActiveSheet.PivotTables PVT.RefreshTable Next PVT End Sub There are 5 pivots: one main one, and 4 that relies on a formula (=IF($A17="grand total","",IF(E17="","",E17))) that refrences the main pivot. Essentially, pivoting on a pivot. However, only one of the secondary pivots seems to be refreshing properly. The others are refreshing but on the old values. So the formulas doesn't have time to update before the pivot is refreshed. Is there any way to add a .5 - 1 delay before the next pivot is refreshed? Thanks, Fred "Roger Govier" wrote: Hi If ToolsoptionsCalculationis set to Automatic, then there should be no need for any pause, as the calculations will have been performed by the time the next refresh occurs. Check your setting for Calculation mode. -- Regards Roger Govier "FredL" wrote in message ... What if I refresh the main pivot, do a pause so that the formulas can catch up, then refresh all; understandably, the main pivot will refresh again. Is there a pause command? "Roger Govier" wrote: Hi Fred If there is only 1 Pivot table on each of your sheets, you could use the following code Sub RefreshPivots() Dim myArray As Variant, ws As Worksheet Set myArray = Sheets(Array("Sheet2", "Sheet3", "Sheet5")) For Each ws In myArray ws.PivotTables(1).PivotCache.Refresh Next End Sub Change the Array of Sheets to the names of your sheets, in the order you want them calculated. If there is more than one Pivot Table on each sheet, then use the following code Sub RefreshPivots2() Dim myArray As Variant, ws As Worksheet, pt As PivotTable Dim i As Integer, ptcount As Integer Dim ptname As String Set myArray = Sheets(Array("Sheet2", "Sheet3", "Sheet5")) For Each ws In myArray ptcount = ws.PivotTables.Count For i = 1 To ptcount ptname = ws.PivotTables(i).Name ws.PivotTables(ptname).PivotCache.Refresh Next i Next End Sub The second macro will work with a single PT per sheet as well. Again, change the array of sheet names to suit. -- Regards Roger Govier "FredL" wrote in message ... This worked so well, that the formulas that the other pivot depends on doesn't have a chance to get updated. So the way I have this setup is that I have a Main Pivot table with calculations. I then have to group those calculations together and so run a pivot of those. Since I can't do a pivot on pivot, I have a formula that points to the results. The second pivot then uses those formulas as its source. With the solution below, those formulas doesn't have time to update when the main pivot refreshes. Therefore, the second pivot still has the results from the prior refresh. Any way around this? "Roger Govier" wrote: Hi Fred Try using ActiveWorkbook.RefreshAll -- Regards Roger Govier "FredL" wrote in message ... So I have this simple macro that refreshes the pivot tables in a template. However, I have it specifying the Sheet number and Pivot Table names. The problem comes when a user would deletes or even hides a tab or copy over a pivot without renaming the table. Is there any way I can program it such that it will refresh all pivot tables in a tab that is between 2 tabs. For example: tab 1 tab 2 tab 3 tab 4 Refresh all pivots that are between tab 1 and 4. Then I can add tabs in between and not have to worry about Pivot Table names. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Prevent certain tabs/sheets from refreshing | Excel Discussion (Misc queries) | |||
Same Data source for Multiple Pivots | Excel Discussion (Misc queries) | |||
microsoft multiple pivots | Excel Discussion (Misc queries) | |||
How do I create multiple worksheets from Page Pivots | Excel Worksheet Functions | |||
multiple pivots updated with selection in one table | Excel Worksheet Functions |