+ Reply to Thread
Results 1 to 4 of 4

Refresh pivot table generates run time error

  1. #1
    Registered User
    Join Date
    03-02-2016
    Location
    HK
    MS-Off Ver
    Excel 2016
    Posts
    10

    Refresh pivot table generates run time error

    Hello everyone,

    I have a chart that its x axis is controlled by a simple set of code that I wrote in the sheet as below:


    Private Sub Worksheet_Calculate()

    ActiveSheet.ChartObjects("Chart 4").Activate
    ActiveChart.Axes(xlValue).MinimumScale = Sheets("Chart").Range("S10").Value
    ActiveChart.Axes(xlValue).MaximumScale = Sheets("Chart").Range("S11").Value
    ActiveChart.Axes(xlValue).MajorUnit = Sheets("Chart").Range("S12").Value
    ActiveCell.Select

    End Sub


    It works just fine in the way that I want the X axis to change automatically with the data range. However, when I tried to refresh the other pivot tables in other separate tags (basing on the same set of data as the chart) or add more records to the source data, the error msg pop up and l know it has something to do with this line: ActiveSheet.ChartObjects("Chart 4").Activate

    So I tried to replace ActiveSheet. with Sheets("Chart") and the error disappear but the problem is that I would go back to the Sheet "Chart" whenever I refresh the other pivot tables in other separate sheets, so I know there must be better solution than this one, I want to stay at the same page when I refresh the other tables.

    Can anyone help? Attached is the file.

    Thanks in advance.
    1.PNG
    Attached Files Attached Files
    Last edited by Mskrn; 03-28-2016 at 11:45 PM.

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Refresh pivot table generates run time error

    Try this...

    Please Login or Register  to view this content.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Registered User
    Join Date
    03-02-2016
    Location
    HK
    MS-Off Ver
    Excel 2016
    Posts
    10

    Re: Refresh pivot table generates run time error

    Hi AlphalFrog,

    A big thank you to you! It is quite challenging for me to understand the difference in all these codes even though I know my question would be an easy one to those VBA expert as I don't have much knowledge on coding. You really help to resolve my question.

  4. #4
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Refresh pivot table generates run time error

    You're welcome. Thanks for the feedback.

+ 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. Retain pivot table data every time I refresh it?
    By athenavespa in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-24-2014, 07:34 PM
  2. Run time error 1004. Pivot Table Refresh
    By mlegge77 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-02-2013, 11:26 AM
  3. Pivot Table refresh - false error
    By Calieth in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 03-26-2010, 01:01 PM
  4. macro error on Pivot Table refresh
    By mheinmiller in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-22-2008, 02:23 PM
  5. Pivot Table Refresh Error?
    By skizz135 in forum Excel General
    Replies: 0
    Last Post: 08-21-2008, 11:36 AM
  6. Pivot table refresh error
    By Robert in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-06-2006, 06:30 AM
  7. [SOLVED] Pivot Table refresh error.
    By wel in forum Excel General
    Replies: 2
    Last Post: 11-01-2005, 10:05 AM

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