+ Reply to Thread
Results 1 to 5 of 5

Update my Pivot Tables automatically after X minutes

  1. #1
    Registered User
    Join Date
    02-17-2014
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    2

    Update my Pivot Tables automatically after X minutes

    Hi,

    Scratching my head over trying to get pivot tables to automatically update every 4 minutes.
    I have 3 worksheets:

    worksheet 1: SCANNER, this sheet pulls in data externally using an =RTD links
    worksheet 2: RAW, this manipulates the data above, and this is the data used to create the pivot tables
    worksheet 3: SUMMARY, the pivot tables are here, there are 9 of them.

    Worksheet RAW, changes every so often, and every 4 minutes I would like to have the pivot tables AUTO REFRESH
    to make sure its using the latest data from RAW.

    I see options to autorefresh on the opening of the workbook, but cant see anything to REFRESH EVERY X MINUTES.
    Is there any way to do this with VBA ?

  2. #2
    Forum Expert
    Join Date
    06-25-2009
    Location
    Sofia, Bulgaria, EU
    MS-Off Ver
    Excel 2003-2013
    Posts
    1,290

    Re: Update my Pivot Tables automatically after X minutes

    Hi, try this:

    put this code in a normal module

    Please Login or Register  to view this content.
    and this in ThisWorkbook module

    Please Login or Register  to view this content.
    If you are pleased with a member's answer then use the Star icon to rate it.

  3. #3
    Registered User
    Join Date
    02-17-2014
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Update my Pivot Tables automatically after X minutes

    Thanks alot, that worked !
    Once follow up question if I could.
    Once i run the code with, I dont find out for 4 minutes if the code actually worked.
    Can I add something to the code that maybe shows a count somewhere on sheet SUMMARY ?
    As long as its counting, I know the code is running

    Adrien

  4. #4
    Forum Expert
    Join Date
    06-25-2009
    Location
    Sofia, Bulgaria, EU
    MS-Off Ver
    Excel 2003-2013
    Posts
    1,290

    Re: Update my Pivot Tables automatically after X minutes

    Hi,
    of course you could add simple counter, but in my opinion it will not be very helpful as you may not notice that it has stopped increasing.
    In my opinion it's better to add info about last refresh time. something like this

    Please Login or Register  to view this content.
    In addition I would use Conditional Formatting to change A1 cell background color to attract my attention if there is difference between cell value and current system time that is greater than some preset interval e.g. 5 minutes

  5. #5
    Registered User
    Join Date
    09-30-2014
    Location
    london
    MS-Off Ver
    10
    Posts
    33

    Re: Update my Pivot Tables automatically after X minutes

    Hi,
    Brilliant work. Your code is working great for pivot table. But the problem is ,
    in my dashboard workbook, i have pivot table and i have cells which calculate how many rows are filled.
    That is i have two workbook
    Workbook1= have the main datas. It have 5 sheets in workbook1. Sheet 2 is the dashboard sheet which i copied to workbook 2 so that i can leave workbook 2 open all the time.
    Sheet2 in workbook1 have pivot table and some cells which link to sheet 1 indicating total number of rows filled in sheet 1.
    The problem is i can update the pivot table automatically but not the cells.
    any idea?
    thanks

+ 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. Automatically update all data connection & Pivot tables every hour
    By PWinkz in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-28-2013, 11:32 AM
  2. Update automatically tables with related headers that are in different sheets
    By cgkmal in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-09-2013, 08:39 PM
  3. link tables to update data automatically
    By nursekatie1978 in forum Excel General
    Replies: 2
    Last Post: 03-26-2011, 09:53 AM
  4. Averaging minutes in pivot tables
    By jermsalerms in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-27-2007, 09:58 AM
  5. Update pivot tables automatically
    By minrufeng in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-23-2005, 09:41 PM

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