Graphs not updating from VBA run code from other sheet

Posted on 2011-10-07
Last Modified: 2012-05-12
Hello EE!

I have a Sheet1 containing a pivot slicer and a graph
While I in sheet2 have the actual pivot

In Sheet2, under a
Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

Open in new window

It calculates data for a graph based on the pivot data. This I've checked to work.
What does not seem to work though, is this module:

Sub DoGraph(TheSheet As String, TheChart As String, TheSeries As Integer, XValues As Variant, YValues As Variant)
    With Worksheets(TheSheet).ChartObjects(TheChart).Chart
        With .SeriesCollection(TheSeries)
            .XValues = XValues
            .Values = YValues
        End With
    End With
End Sub

Open in new window

I'm then using
DoGraph "Sheet1", "name of graph", "1", XValues, YValues

The slicer, the pivot, the module and the call of the module all worked when it was done in one sheet. However after I moved the pivot to sheet 2, it suddenly does not update the graph.

Is this a restriction in Excel or did my DoGraph break when i started adding Worksheet(TheSheet) instead of the previous Me?
Question by:ThomasFoege
    LVL 85

    Expert Comment

    by:Rory Archibald
    Are you sure you are passing the right values? Also, are you getting an error, or simply nothing happening? (sample workbook might help)

    Accepted Solution

    I found the problem

    After having moved the code that creates the arrays for the X/Y values into Sheet2, I was still using Activesheet.

    While Activesheet of course was Sheet1, I should have been using Me.

    Embarresing :) So it all was working, I was just looking the wrong place for the error
    LVL 9

    Expert Comment

    I'm then using
    DoGraph "Sheet1", "name of graph", "1", XValues, YValues

    Open in new window

    Try removing the quotes surrounding "1" in your parameters... the argument type shows it should be an Integer not a string.

    Not sure if this is all that is wrong, but it's a start!
    LVL 9

    Expert Comment

    Glad you found a fix!

    Author Closing Comment

    Found the problem

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
    Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
    Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now