[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 205
  • Last Modified:

Graphs not updating from VBA run code from other sheet

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?
0
ThomasFoege
Asked:
ThomasFoege
  • 2
  • 2
1 Solution
 
Rory ArchibaldCommented:
Are you sure you are passing the right values? Also, are you getting an error, or simply nothing happening? (sample workbook might help)
0
 
ThomasFoegeAuthor Commented:
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
0
 
sah18Commented:
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!
0
 
sah18Commented:
Glad you found a fix!
0
 
ThomasFoegeAuthor Commented:
Found the problem
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now