Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pivot recalculate macro (Excel 2003)

    We have a pivot table on a sheet. It is a summary of G/L activity from a Dynamic range name Data on another tab. We update the Data tab daily during the monthly close. Since the PT does not automatically referesh on the other tab, I have a little "refresh" button that users have to press. I'm wondering: Can I do a macro so that when a user chooses the sheet with the Pivot table it automatically refreshes at that point? I have yet to get sharp with macros, but I think I would put it on the code for that particular sheet, correct? And use an event trigger to do what? A method? Thanks for any suggestions.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Pivot recalculate macro (Excel 2003)

    You can add a code like this to the worksheet object containing the pivot table:

    <pre>Private Sub Worksheet_Activate()
    Me.PivotTables(1).PivotCache.Refresh
    End Sub</pre>


    Steve

  3. #3
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot recalculate macro (Excel 2003)

    What does the Me. refer to? thanks

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot recalculate macro (Excel 2003)

    In a worksheet module, Me refers to the worksheet. This is very handy, since it doesn't matter what the worksheet is called.
    Similarly, in the ThisWorkbook module, Me refers to the workbook as a whole.
    In a standard module (the kind you create by selecting Insert | Module in the Visual Basic Editor, or by recording a macro), you cannot use Me.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Pivot recalculate macro (Excel 2003)

    In this context it refers to the worksheet that the code is in. [if it were in a userform code, it would reference the userform]

    Steve

Posting Permissions

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