+ Reply to Thread
Results 1 to 8 of 8

PivotTable/auto refresh

  1. 08-18-2009, 08:29 AM #1
    Trainee
    Trainee is offline
    Registered User
    Join Date
    08-07-2009
    Location
    Finland
    MS-Off Ver
    Excel 2003
    Posts
    8

    PivotTable/auto refresh

    My aim is to have pivotTables that would refresh automatically when there are changes in related data. I managed to create a macro which does this when the worksheet is visited, see below.

    Please Login or Register  to view this content.
    How should I modify the macro so that the pivotTables would autorefresh itself when there's a change in corresponding data so that the unnecessary clicking back and forth could be avoided? I have multiple worksheets for "raw data" and then all the pivotTables are in one separate sheet.

    Thank you in advance for any help.
    Last edited by DonkeyOte; 01-12-2010 at 04:11 AM.
    Reply With QuoteRegister To Reply
  2. 08-18-2009, 08:36 AM #2
    Palmetto
    Palmetto is offline
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: PivotTable/auto refresh

    You could run the code in the Worksheet_Calculate Event.

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  3. 08-19-2009, 03:47 AM #3
    Trainee
    Trainee is offline
    Registered User
    Join Date
    08-07-2009
    Location
    Finland
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: PivotTable/auto refresh

    Hello and thank you for your advice. With your code it works but there's one problem: it only works if I first open the sheet with Pivottables, from there "view code" and then run it once in Visual Basic. After that it works well but I would like to get it to work immediately without going into Visual Basic. Do you happen to have a solution for this?
    Reply With QuoteRegister To Reply
  4. 08-19-2009, 03:57 AM #4
    DonkeyOte
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: PivotTable/auto refresh

    Based on your narrative the code for Raw Data sheet would be as per Palmetto's post only you need iterate the PTs' on the PT as opposed to Raw Data sheet, ie:

    Please Login or Register  to view this content.
    change that in Red font to be the name of sheet on which the PT's of interest are contained

    So to reiterate the above should reside in Raw Data sheet object in VBE and not Pivot Table sheet object.... as mentioned it may be that a Change event is preferable (ie if no calculation is invoked when raw data is updated (ie no volatiles / dependents).
    My Recommended Reading:

    Volatility
    Sumproduct & Arrays
    Pivot Intro
    Email from XL - VBA & Outlook VBA
    Function Dictionary & Function Translations
    Dynamic Named Ranges
    Reply With QuoteRegister To Reply
  5. 01-12-2010, 02:56 AM #5
    synergy46
    synergy46 is offline
    Registered User
    Join Date
    12-29-2009
    Location
    Washington State, USA
    MS-Off Ver
    Excel 2008 - Mac
    Posts
    15

    Re: PivotTable/auto refresh

    I am running XL 2004

    I too have a 'refresh pivot table' problem; ie, it will not refresh and show additions to the source LIST.

    I have tried making a 'dynamic' range of my list and that doesn't seem to do anything. But, without it, when I refresh, (manually or through the above VBA code, nothing updates.

    Question: "Is it necessary to make the LIST dynamic to be able to refresh a PT and see additional rows in the source? Or, can I just define the source and let the automatic LIST expansion somehow be reflected in the PT?

    Thanks
    Reply With QuoteRegister To Reply
  6. 01-12-2010, 04:11 AM #6
    DonkeyOte
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: PivotTable/auto refresh

    synergy46, welcome to the board, however, please note:

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.
    Reply With QuoteRegister To Reply
  7. 01-12-2010, 03:52 PM #7
    synergy46
    synergy46 is offline
    Registered User
    Join Date
    12-29-2009
    Location
    Washington State, USA
    MS-Off Ver
    Excel 2008 - Mac
    Posts
    15

    Question PivotTable/auto refresh

    oops.... wrong location. I intended to delete this message but I can't???
    Reply With QuoteRegister To Reply
  8. 06-16-2011, 05:19 PM #8
    avip2u
    avip2u is offline
    Registered User
    Join Date
    06-16-2011
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: PivotTable/auto refresh

    Expanding on the code in the prior post, this one uses the Change Event, and refreshes all pivot tables on each worksheet. As mentioned before, this macro must reside in each worksheet for which you desire a change to refresh your tables.

    I hope this will be helpful !

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1