Pivot Table(s) - refresh in sheet - macro - automate

[pepenacho]

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

 

[Bernie Deitrick]

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

 

[Bernie Deitrick]

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

 

[pepenacho]

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)

 

[Bernie Deitrick]

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]

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

 

[Bernie Deitrick]

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