Could you provide VBA code for refreshing an Excel Pivot Table automatically upon activating the worksheet?
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:
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...
Last edited by Aussiebear; 04-29-2023 at 07:06 PM. Reason: Adjusted the code tags
~Anne Troy
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...
~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.
~Anne Troy
you're absolutely right. Apparently my table is PivotTable2
Great. Let us know if the code works for you.
~Anne Troy
and it works too! Thanks very much
Cool!! Marked solved, and added to the KB.
~Anne Troy
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.
Brandtrock
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.
~Anne Troy
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!
~Anne Troy
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)
Last edited by Aussiebear; 04-29-2023 at 07:09 PM. Reason: Added code tags
Regards, Zack Barresse
Check out the KB! :|: BOARD TAGS: WHAT ARE THEY AND HOW DO I USE THEM
What is a Microsoft MVP? | Free Microsoft Courses | My Book on Excel Tables
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.
~Anne Troy
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)
Last edited by Aussiebear; 04-29-2023 at 07:10 PM. Reason: Added code tags
Regards, Zack Barresse
Check out the KB! :|: BOARD TAGS: WHAT ARE THEY AND HOW DO I USE THEM
What is a Microsoft MVP? | Free Microsoft Courses | My Book on Excel Tables
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