1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Pivot tables update vba

Discussion in 'Business Applications' started by wallacealeco, Jan 21, 2011.

Thread Status:
Not open for further replies.
  1. wallacealeco

    wallacealeco Thread Starter

    Joined:
    Jan 19, 2011
    Messages:
    4
    In Office 2007 (Windows7 64 bit) I have a spreadsheet that links to data in an Access database. The spreadsheet has a pivot table linked to the imported data. I have a macro that updates the data, extract various figures and finally updates the pivot table, except that it doesn't. When I run the code on it's own it works but when the code is tagged on the end of other code it doesn't.

    Worksheets("destination pivot").Select ' The sheet the pivot table is on
    For Each pt In ActiveSheet.PivotTables ' there are a couple of pivot tables
    pt.RefreshTable
    Next pt
    End Sub


    Any ideas please.
     
  2. jimr381

    jimr381

    Joined:
    Jul 20, 2007
    Messages:
    4,189
    First Name:
    Jim
    Welcome to the forum. What exactly is all of the code doing? You have automatic refresh options that might not need to be done through VBA.
     
  3. wallacealeco

    wallacealeco Thread Starter

    Joined:
    Jan 19, 2011
    Messages:
    4
    Thanks for your reply. The database, or particular database table, records trips by car from and to various destinations. When the spreadsheet loads, the data is imported and the code via various 'for-next' loops extracts detailed information on destinations, distance and other things and is presented to the user as graphs or written reports. This was begining to take too long so I started experimenting with Pivot Charts. The paticular Pivot shows a count of trips to destinations. When data is added to the database it gets imported to the spreadsheet but the pivot chart (set to update of load) does not reflect the new data until I run the above code on its own. Roughly the code is as follows.
    For counter = 1 to 'number of rows of data'
    if critera met add to total
    Next counter
    Update Pivot chart

    The 'Update pivot chart' code works as a seperate sub but not on the end of code as above.
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/976026

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice