+ Reply to Thread
Results 1 to 3 of 3

Macro that automatically updates pivot table when data in separate worksheet is updated

  1. #1
    Registered User
    Join Date
    01-14-2013
    Location
    New York, NY
    MS-Off Ver
    Excel 2010
    Posts
    14

    Macro that automatically updates pivot table when data in separate worksheet is updated

    Hi,

    Background situation: I have created a spreadsheet that tracks expenses (see attached). I have a control tab that lists the foundation for the information in all my other tabs. Then I created a transaction register which is where all the expenses are input. From the transaction register I created pivot tables used to update my dashboard tabs. I put a macro in the dashboard tabs that automatically updates the dashboards when the pivot table it refreshed.

    Question: What I have been researching is how to have the pivot table update automatically when I update my transaction register. I don't want to have to click on the pivot table tab for it to update nor do I want to have to right click on the table to update. Essentially I would want to hide the pivot tables since they are only used as a means to the dashboards.

    Macros are new to me and I had enough trouble creating the one that I did for the dashboard. And I have exhausted my google searches in hopes of trying to understand how I could do this.

    Any help would be great.

    Thanks!
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    05-08-2013
    Location
    California
    MS-Off Ver
    Excel 2013
    Posts
    102

    Re: Macro that automatically updates pivot table when data in separate worksheet is update

    Fermi,

    There are a few approaches that you can take. I will suggest two of them here.

    First Approach: You can make it so that once you click away from the "Transaction Register" tab, your two pivot tables will update. You will need to go into the navigation pane of your Visual Basic Editor (VBE), and instead of writing code into one of your Modules (which is where you have your code), double click on "Transaction Register" and write in the following code:

    Please Login or Register  to view this content.
    What the Deactivate event is saying is that each and every time you click away from the "Transaction Register" tab, the pivot tables (i.e., PivotTable4 and PivotTable1) will update themselves. Run the macro once just to make sure it gets run. Then it should continue to work in the background on an ongoing basis even after you close out of the file and reopen it again.

    Second Approach: You can write a macro that will update both pivot tables at once but at a time of your choosing (as opposed to it happening each time you click away from the "Transaction Register" tab). It would look something like this (and you ought place the code in a new module):

    Please Login or Register  to view this content.
    Under this second approach, you run the macro when you want to so that the pivot tables are updated at your discretion. Remember, place this code in its own module.

    Both approaches should work even with the pivot table tabs hidden. Hope this helps.
    Last edited by Dimitrov; 09-05-2013 at 01:44 PM.

  3. #3
    Registered User
    Join Date
    01-14-2013
    Location
    New York, NY
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Macro that automatically updates pivot table when data in separate worksheet is update

    Thanks so much for your help. I am not sure my last thank you went through so I am reposting.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 07-19-2013, 04:34 PM
  2. [SOLVED] How to Create a Rolling Summary Page that Automatically Updates from a Data Table
    By DontExcelAtMuch in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-28-2013, 03:34 PM
  3. Run a macro after a Pivot Table Page field has been updated
    By pdauction in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-20-2012, 10:33 AM
  4. Copy entire worksheet, values only, after Pivot table updates
    By gnoke in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-29-2010, 10:08 AM
  5. [SOLVED] Disappearing Data Items on Pivot Table -Updated Question
    By GeorgeChe in forum Excel General
    Replies: 4
    Last Post: 08-02-2005, 11:05 AM

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1