+ Reply to Thread
Results 1 to 2 of 2

Update Pivot Chart when table is on different sheet using macro

  1. #1
    Registered User
    Join Date
    08-31-2011
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    1

    Update Pivot Chart when table is on different sheet using macro

    Hi

    I have Pivot Tables connected to a data source on one sheet and corresponding pivot charts on another sheet.
    I need a macro to be able to update the charts without making the tables visible to the user.

    I need the Charts sheet to be Active at all times.
    I recorded the following macro for the same, but receive an error when I run it.
    "Unable to get Pivot Table property of the Worksheet class"

    ActiveSheet.ChartObjects("Chart 6").Activate
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
    "PivotTable1").CubeFields("[Measures].[Total Overall time]")

    As of now I use the following macro

    Sheets("Pivots").Select
    Sheets("Pivots").PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
    "PivotTable1").CubeFields("[Measures].[Total Overall time]")
    Sheets("Charts").Select

    In this case, the user sees the control being transferred to Sheet with Pivot table and then back to the Charts Sheet.
    Please let me know if the macro can update the table in the background, so that the user only sees the charts being updated.

    Thanks in advance

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Update Pivot Chart when table is on different sheet using macro

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here


    Do not use the select commands.

    Please Login or Register  to view this content.
    If that does not work try turning screen updating off and on
    Please Login or Register  to view this content.
    Cheers
    Andy
    www.andypope.info

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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