K. Georgiadis

07-03-2004, 10:28 AM

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

View Full Version : Solved: Auto Refresh Pivot Table on Activate Worksheet

K. Georgiadis

07-03-2004, 10:28 AM

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

Anne Troy

07-03-2004, 10:32 AM

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...

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

07-03-2004, 11:01 AM

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

Anne Troy

07-03-2004, 11:17 AM

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...

I'm not sure on it...

Anne Troy

07-03-2004, 11:20 AM

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

07-03-2004, 11:42 AM

you're absolutely right. Apparently my table is PivotTable2

Anne Troy

07-03-2004, 11:43 AM

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

K. Georgiadis

07-03-2004, 11:46 AM

and it works too! Thanks very much

Anne Troy

07-03-2004, 11:49 AM

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

Brandtrock

07-03-2004, 01:42 PM

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

07-03-2004, 02:11 PM

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

Anne Troy

07-03-2004, 02:16 PM

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.

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

07-03-2004, 03:19 PM

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

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

07-03-2004, 03:30 PM

Excellent. Thanks very much!

Zack Barresse

07-03-2004, 03:47 PM

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}

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

{untested tho}

K. Georgiadis

07-03-2004, 04:13 PM

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

K. Georgiadis

07-03-2004, 05:35 PM

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.

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

07-03-2004, 05:42 PM

yoooda bomb!

I'll check it out tomorrow.

I'll check it out tomorrow.

Zack Barresse

07-04-2004, 01:58 AM

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.

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

07-04-2004, 05:55 AM

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

07-04-2004, 09:58 AM

='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.

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

07-04-2004, 10:25 AM

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

:bigdance2

:bigdance2

K. Georgiadis

07-04-2004, 11:32 AM

='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.

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.

Powered by vBulletin® Version 4.2.5 Copyright © 2020 vBulletin Solutions Inc. All rights reserved.