Could you provide VBA code for refreshing an Excel Pivot Table automatically upon activating the worksheet?
Printable View
Could you provide VBA code for refreshing an Excel Pivot Table automatically upon activating the worksheet?
I'm no coder, and I stole this, but you can try it unless and until someone better comes along. Copy this code:
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...
Appropriately enough my Pivot Table is in worksheet "Pivot Table," but what does "PivotTable1" refer to?
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...
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.
you're absolutely right. Apparently my table is PivotTable2
Great. Let us know if the code works for you. :)
and it works too! Thanks very much
Cool!! Marked solved, and added to the KB.
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.
thanks for the reminder. I used a dynamic range formula provided by Debra Dalgleish. It works great
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.
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
Excellent. Thanks very much!
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:
{untested tho}PHP Code:
=Data!$A$2:INDEX(Data!$A:$N,MATCH(REPT("z",255),Data!$A:$A),14)
I never tried that approach. If it works well, do let us know
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.
yoooda bomb!
I'll check it out tomorrow.
Hello,
Yes my solution works just fine. Tested with your file:
..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.PHP Code:
='Source Data'!$A$1:INDEX('Source Data'!$A:$F,MATCH(REPT("z",255),'Source Data'!$A:$A),6)
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