VB Dynamic Graph Excel Range Error

Hi experts,

I have a small vb script that I want to run to change the source data on a graph.  No matter what I try I can not get the graph to accept the defined range.  Here is the code:

intLastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
        intLastCol = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column
        Sheets("Graph").Select
        ActiveChart.ChartArea.Select
        ActiveChart.SetSourceData Source:=Sheets("Chart Data").Range(Cells(5, 1), Cells(intLastRow, intLastCol))

Open in new window


The error is always shown on the last line.

Any help would really be appreciated.

LVL 1
GlenmoranUKAsked:
Who is Participating?
 
Nico BontenbalConnect With a Mentor Commented:
Try this:
ActiveChart.SetSourceData Source:=Sheets("Chart Data").Range(Sheets("Chart Data").Cells(5, 1), Sheets("Chart Data").Cells(intLastRow, intLastCol))
0
 
Nico BontenbalCommented:
The Cells(5, 1) in your code refers to the cells of the active sheet. Being the Graph sheet, which doesn't have cells, hence the error.
0
 
GlenmoranUKAuthor Commented:
Fantastic..  That has had me going round in circles for a good couple of hours..  :o)

Many thanks for the code and more importantly the explanation.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.