Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table(s) - refresh in sheet - macro - automate
Hello:
I have two tables, in separate Work Sheets: Table1 and Table2. - Table1 and Table2 are periodically updated. Generally only the number of rows changes, up or down. I have a separate Work Sheet called Info1. There I slice and dice the data from both tables, with the use of 3 Pivot Tables, which were created side-by-side. I will have Work Sheet with pivots, called: Info2, Info3, etc. Question 1: The toolbar offers a button to refresh a Pivot table. I recorded the procedure (using a macro) called it PivotUPDT. Sub PivotUPDT() ActiveSheet.PivotTables("PivotTable1").PivotCache. Refresh End Sub How do I tweak so that this procedure refreshes any and all Pivot Tables, in an active Work Sheet, be it Info1, Info 2 or Info 3? "PivotTable1" suggests only the first Pivot Table that was created will be refreshed. Question 2: If the boundy of my Table1 or Table2 changes (i.e. less rows are present than before). In Info1, for example, I want to be able to update the range that a Pivot Table is mapped to. What is the code for that? I'm ok with making a button for each Pivot Table in Info1 as long as I know how to do it. Thanks, Pepe |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table(s) - refresh in sheet - macro - automate
Pepe,
Sub RefreshAllPTs() Dim myPT As PivotTable For Each myPT In Sheets("Info1").PivotTables myPT.PivotCache.Refresh Next End Sub The best way to deal with pivot table ranges is to define the range at least one row longer than the data, shade the data cells, and make sure that when you need to insert more data, you start by inserting new rows at the first empty but shaded row. Then Excel will automatically expand the PT range. HTH, Bernie MS Excel MVP "pepenacho" wrote in message ... Hello: I have two tables, in separate Work Sheets: Table1 and Table2. - Table1 and Table2 are periodically updated. Generally only the number of rows changes, up or down. I have a separate Work Sheet called Info1. There I slice and dice the data from both tables, with the use of 3 Pivot Tables, which were created side-by-side. I will have Work Sheet with pivots, called: Info2, Info3, etc. Question 1: The toolbar offers a button to refresh a Pivot table. I recorded the procedure (using a macro) called it PivotUPDT. Sub PivotUPDT() ActiveSheet.PivotTables("PivotTable1").PivotCache. Refresh End Sub How do I tweak so that this procedure refreshes any and all Pivot Tables, in an active Work Sheet, be it Info1, Info 2 or Info 3? "PivotTable1" suggests only the first Pivot Table that was created will be refreshed. Question 2: If the boundy of my Table1 or Table2 changes (i.e. less rows are present than before). In Info1, for example, I want to be able to update the range that a Pivot Table is mapped to. What is the code for that? I'm ok with making a button for each Pivot Table in Info1 as long as I know how to do it. Thanks, Pepe |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table(s) - refresh in sheet - macro - automate
Ooops, I missed the activesheet part.....
Sub RefreshAllPTs2() Dim myPT As PivotTable For Each myPT In ActiveSheet.PivotTables myPT.PivotCache.Refresh Next End Sub HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Pepe, Sub RefreshAllPTs() Dim myPT As PivotTable For Each myPT In Sheets("Info1").PivotTables myPT.PivotCache.Refresh Next End Sub The best way to deal with pivot table ranges is to define the range at least one row longer than the data, shade the data cells, and make sure that when you need to insert more data, you start by inserting new rows at the first empty but shaded row. Then Excel will automatically expand the PT range. HTH, Bernie MS Excel MVP "pepenacho" wrote in message ... Hello: I have two tables, in separate Work Sheets: Table1 and Table2. - Table1 and Table2 are periodically updated. Generally only the number of rows changes, up or down. I have a separate Work Sheet called Info1. There I slice and dice the data from both tables, with the use of 3 Pivot Tables, which were created side-by-side. I will have Work Sheet with pivots, called: Info2, Info3, etc. Question 1: The toolbar offers a button to refresh a Pivot table. I recorded the procedure (using a macro) called it PivotUPDT. Sub PivotUPDT() ActiveSheet.PivotTables("PivotTable1").PivotCache. Refresh End Sub How do I tweak so that this procedure refreshes any and all Pivot Tables, in an active Work Sheet, be it Info1, Info 2 or Info 3? "PivotTable1" suggests only the first Pivot Table that was created will be refreshed. Question 2: If the boundy of my Table1 or Table2 changes (i.e. less rows are present than before). In Info1, for example, I want to be able to update the range that a Pivot Table is mapped to. What is the code for that? I'm ok with making a button for each Pivot Table in Info1 as long as I know how to do it. Thanks, Pepe |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table(s) - refresh in sheet - macro - automate
Bernie:
It worked fine thank you!! I have two short follow up questions. Question 1) Is there a general standard/order to the syntax for objects in vb? I.e. for ActiveSheet.PivotTables Such that i.e. [object class].[specific object].[event on object].[etc].[etc].[etc].[etc] Furthermore, when I explore the object library in the VB editor, I cannot seem to find any definitions/discussion on how to use what I find, in order to help myself differentiate and test!, if I've found the right thing. Am I missing something? Question 2) Let's say that i have a PivotTable called PivotTable1. I now want to write a subprocedure to select the entire PivotTable, in order to copy the information. Because the boundry of a PivotTable is a "moving target" is there an object or a property that can be written and that will select the entire PivotTable regardless of its current width or length? Thanks, Robert (pepe) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table(s) - refresh in sheet - macro - automate
Robert(pepe)
1) Generally, an object has three options: [object].[subobject] [object].[property] [object].[method] For example: Worksheets(1).Range("A1") Worksheets(1).Visible Workbooks(1).Move and each subobject can have all three options. 2) The macro recorder is your best friend when you are trying to learn VBA coding: selecting a cell within a PT, then using the Pivot Table Commandbar: Pivot Table / Select / Entire Table, you get: ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel, True HTH, Bernie MS Excel MVP "pepenacho" wrote in message ... Bernie: It worked fine thank you!! I have two short follow up questions. Question 1) Is there a general standard/order to the syntax for objects in vb? I.e. for ActiveSheet.PivotTables Such that i.e. [object class].[specific object].[event on object].[etc].[etc].[etc].[etc] Furthermore, when I explore the object library in the VB editor, I cannot seem to find any definitions/discussion on how to use what I find, in order to help myself differentiate and test!, if I've found the right thing. Am I missing something? Question 2) Let's say that i have a PivotTable called PivotTable1. I now want to write a subprocedure to select the entire PivotTable, in order to copy the information. Because the boundry of a PivotTable is a "moving target" is there an object or a property that can be written and that will select the entire PivotTable regardless of its current width or length? Thanks, Robert (pepe) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table(s) - refresh in sheet - macro - automate
Bernie:
All the stuff worked. There seems to be one minor subtle outcome with this sub. I have 4 tables in my Excel file and about 8 Work Sheets that have PivotTables, which feed from the tables. In each Work Sheet I have a button that calls a procedure in a Module called RefreshAllPivots Sometimes this seems to refresh all PivotTables in all the Work Sheets, and sometimes in just that one specific one. What's the catch, I trouble-shooted this to kingdom come and can't figure it out? Sub RefreshAllPivots() Dim myPT As PivotTable For Each myPT In ActiveSheet.PivotTables myPT.PivotCache.Refresh Next End Sub Thanks, Robert |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table(s) - refresh in sheet - macro - automate
Robert,
Perhaps it is because two or more of your pivot tables share a data table. Updating one pivot table will update all pivot tables based on that data table, and perhaps cause a cascade effect (though here I am just guessing - I rarely use more than one data table in a workbook, and having the pivot tables update prematurely isn't a big deal). If it is causing you problems, you might want to split your workbook up.... Bernie "pepenacho" wrote in message ... Bernie: All the stuff worked. There seems to be one minor subtle outcome with this sub. I have 4 tables in my Excel file and about 8 Work Sheets that have PivotTables, which feed from the tables. In each Work Sheet I have a button that calls a procedure in a Module called RefreshAllPivots Sometimes this seems to refresh all PivotTables in all the Work Sheets, and sometimes in just that one specific one. What's the catch, I trouble-shooted this to kingdom come and can't figure it out? Sub RefreshAllPivots() Dim myPT As PivotTable For Each myPT In ActiveSheet.PivotTables myPT.PivotCache.Refresh Next End Sub Thanks, Robert |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to refresh the pivot table while the excel sheet in shared? | Excel Worksheet Functions | |||
Refresh Pivot Table when Sheet is PW Procted | Excel Worksheet Functions | |||
Refresh pivot table on protected sheet | Excel Discussion (Misc queries) | |||
Pivot Table Refresh Deletes Range On Different Sheet | Excel Discussion (Misc queries) | |||
Timing of automatic query refresh and macro pivot table refresh | Excel Programming |