Auto Refresh Pivot Table on Activate Worksheet

[K. Georgiadis]

Could you provide VBA code for refreshing an Excel Pivot Table automatically upon activating the worksheet?

[Anne Troy]

I'm no coder, and I stole this, but you can try it unless and until someone better comes along. Copy this code:


Private Sub Worksheet_Activate()
'If this worksheet is activated, refresh the pivot table
Sheets("Pivot").PivotTables("PivotTable1").RefreshTable
End Sub


Open your Excel file.
Right-click the sheet tab on which the pivot table resides and hit View Code.
Paste the code into the code window at right.
Hit the Save button. Close the Visual Basic Editor window.

To test:
Click on any other sheet.
Click on the sheet with your pivot table.

Warning: Your pivot table may not be pivot table 1...

[K. Georgiadis]

Appropriately enough my Pivot Table is in worksheet "Pivot Table," but what does "PivotTable1" refer to?

[Anne Troy]

Whenever you place an object into a worksheet, such as a pivot table, chart, or drawn object from the drawing toolbar, Excel assigns it a *name*. That's all I know about that. But, I suspect that if you created your pivot table once, and deleted it, the next time you create it, it could be assigned as pivot table 2...

I'm not sure on it...

[Anne Troy]

It appears as though if you select the top, right-hand corner cell of your pivot table and choose "Table Options", you can find out the name of your pivot table, or even assign it a different name.

[K. Georgiadis]

you're absolutely right. Apparently my table is PivotTable2

[Anne Troy]

Great. Let us know if the code works for you. :)

[K. Georgiadis]

and it works too! Thanks very much

[Anne Troy]

Cool!! Marked solved, and added to the KB.

[Brandtrock]

Just a note, if this code is utilized AND you have used a Dynamic Named Range to identify your pivot table range, you won't have to worry about "missing" any data you add to your pivot data range.

[K. Georgiadis]

thanks for the reminder. I used a dynamic range formula provided by Debra Dalgleish. It works great

[Anne Troy]

Hi, K.
If you wouldn't mind terribly, I'd love to change the KB entry to include the method you used to create the dynamic range. Do you think you could attach your file, or a sample that we could use for it? If you do, I'll put the KB article in your name instead of mine. :)

Or, if you like, apply the methods to the attached workbook, which contains some sample data.

[K. Georgiadis]

The method I used was to:

1) name the data range as "dynarange" (you can use any name you want) and in the "refers to" box I entered:

=OFFSET("Data"!$A$2,0,0,COUNTA("Data"!$A:$A),14)

"data' is the name of the worksheet containing the data
$A$2 is the cell where the data list begins
14 is the number of columns in my data list

2) in the Pivot wizard I added "dynarange" as the range

The above formula was adapted from a formula provided by Debra Dalgleish, a very fine Excel MVP

[Anne Troy]

Excellent. Thanks very much!

[Zack Barresse]

Couldn't you also use a non-volatile INDEX instead of the OFFSET? I always did prefer that, personal preference I guess. The biggest reason being that COUNTA will not take into account any blank cells whilst a binary lookup (MATCH) will. Hence my preference:


=Data!$A$2:INDEX(Data!$A:$N,MATCH(REPT("z",255),Data!$A:$A),14)

{untested tho}

[K. Georgiadis]

I never tried that approach. If it works well, do let us know

[K. Georgiadis]

Per your request, here is your revised pivot.zip file, based on a named, dynamic range.

Go to Insert>Name>Define and look under "dynarange" to see the OFFSET formula. After defining the formula thus, I went to the Pivot Table, I right-clicked to bring up the Pivot Table Wizard, I clicked <back> to bring up the data range and I typed dynarange in the window.

[Anne Troy]

yoooda bomb!
I'll check it out tomorrow.

[Zack Barresse]

Hello,

Yes my solution works just fine. Tested with your file:


='Source Data'!$A$1:INDEX('Source Data'!$A:$F,MATCH(REPT("z",255),'Source Data'!$A:$A),6)

..was the solution adapted to your file. Below I uploaded a copy of it and it is using this formula and works well. Either one would work, and I guess I'm just splittin' hairs here, but it's good practices imho. In the sample file the dynamic range was thoughtfully named "dynarange2", LOL. The OFFSET range is still there too.

[K. Georgiadis]

Thanks! I'll check it out...most importantly, I'll try to dissect your formula to make sure I understand exactly how and why it works

[Zack Barresse]

='Source Data'!$A$1:

Obviously starts at A1.

Array Reference:
INDEX('Source Data'!$A:$F,

This is the range that were going to look at. If our row or column numbers from hte next two sections are out of this range, formula will #REF! out.

Row Reference:
MATCH(REPT("z",255),'Source Data'!$A:$A),

This is the magic part, and it's far too difficult to explain, so layman's terms will suffice. It's basically a binary search down column A for the last cell filled with text. With the MATCH function, this returns reference numbers, a row number in this case. If we started on A2, we'd have to add 1 to this specific section or we'd be missing the very last bottom row. For finding numbers you'd use MATCH(9.99999999E+307,$A:$A), or for both use MAX(MATCH(9.99999999E+307,$A:$A),MATCH(REPT("z",255),'Source Data'!$A:$A)). This is especially helpful when you have data and want the last cell but do not know if it will be textual or numerical.

Column Reference
6)

Or column F in other words. If this was above 6, with the Array set to what it is (A:F) the formula would error out.

So on the numerical side, MATCH(9.99999999E+307,$A:$A) will bring back the last numerical cell in column a. Both the numerical and textual (9.9999, rept("z)) give the largest value you can use in excel, or the last cell in a specified range, searching from the bottom up.

If there is nothing in the column in question for the binary match portion, you'll receive the #N/A error. Depending on your data you can error handle this.

Hope that helps.

[Brandtrock]

Nice bit of work Zach! I have always used the COUNTA method, but I like the way yours works better.
:bigdance2

[K. Georgiadis]

='Source Data'!$A$1:

Obviously starts at A1.

Array Reference:
INDEX('Source Data'!$A:$F,

This is the range that were going to look at. If our row or column numbers from hte next two sections are out of this range, formula will #REF! out.

Row Reference:
MATCH(REPT("z",255),'Source Data'!$A:$A),

This is the magic part, and it's far too difficult to explain, so layman's terms will suffice. It's basically a binary search down column A for the last cell filled with text. With the MATCH function, this returns reference numbers, a row number in this case. If we started on A2, we'd have to add 1 to this specific section or we'd be missing the very last bottom row. For finding numbers you'd use MATCH(9.99999999E+307,$A:$A), or for both use MAX(MATCH(9.99999999E+307,$A:$A),MATCH(REPT("z",255),'Source Data'!$A:$A)). This is especially helpful when you have data and want the last cell but do not know if it will be textual or numerical.

Column Reference
6)

Or column F in other words. If this was above 6, with the Array set to what it is (A:F) the formula would error out.

So on the numerical side, MATCH(9.99999999E+307,$A:$A) will bring back the last numerical cell in column a. Both the numerical and textual (9.9999, rept("z)) give the largest value you can use in excel, or the last cell in a specified range, searching from the bottom up.

If there is nothing in the column in question for the binary match portion, you'll receive the #N/A error. Depending on your data you can error handle this.

Hope that helps.
it most certainly helps! Thanks for the detailed explanation.