Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 23

Thread: Solved: Auto Refresh Pivot Table on Activate Worksheet

  1. #1

    Solved: Auto Refresh Pivot Table on Activate Worksheet

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

  2. #2
    Site Admin
    The Princess
    VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    I'm no coder, and I stole this, but you can try it unless and until someone better comes along. Copy this code:

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

    End Sub
    [/vba]

    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...
    ~Anne Troy

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

  4. #4
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    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

  5. #5
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    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

  6. #6
    you're absolutely right. Apparently my table is PivotTable2

  7. #7
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Great. Let us know if the code works for you.
    ~Anne Troy

  8. #8
    and it works too! Thanks very much

  9. #9
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Cool!! Marked solved, and added to the KB.
    ~Anne Troy

  10. #10
    VBAX Mentor Brandtrock's Avatar
    Joined
    Jun 2004
    Location
    Titonka, IA
    Posts
    399
    Location
    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.

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

  12. #12
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    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

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

  14. #14
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Excellent. Thanks very much!
    ~Anne Troy

  15. #15
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,938
    Location
    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}

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

  17. #17

    Auto Refresh Pivot Table based on a named dynamic range

    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.

  18. #18
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    yoooda bomb!
    I'll check it out tomorrow.
    ~Anne Troy

  19. #19
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,938
    Location
    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.

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •