Updating a chart

[Khurram]

Hi all,
I'm using the following code for updating a chart on an existing
worksheet. The chart contains data covering a period of 13 months
(Jan-Jan) and the code prior to this adds an extra month's worth of
data to the source. What I need is for the update process to drop one
month at the begining when a new month is added. The below code ends
up taking everything is the rows rather than the specific range I'm
trying to use. I think I'm making a trivial mistake somewhere but I
cant locate it. Please help.


Dim bottomRightOfGraphData As String
Dim topLeftOfGraphData As String

bottomRightOfGraphData = ActiveCell.Address
Range(bottomRightOfGraphData).Select
ActiveCell.Offset(-1, -12).Select
topLeftOfGraphData = ActiveCell.Address

'update related graph
ActiveSheet.ChartObjects("Chart 8").Activate
ActiveChart.ChartArea.Select
ActiveChart.SetSourceData Source:=Sheets("HP Analyst & Q
time").Range("A1:A3", topLeftOfGraphData & ":" &
bottomRightOfGraphData), PlotBy:=xlRows

Thank you kindly
Deecrypt

 

[Jon Peltier]

Are you deleting cells during the process, or just changing the range
pointed at by the chart? If it's the latter, make a dynamic chart, which
does not require VBA coding:

http://peltiertech.com/Excel/Charts/Dynamics.html

- Jon

 

[Khurram]

Hi Jon,
Thank you for your help. Unfortunately I did need to use VBA but
thankfully I managed to sort the problem out. It was to do with the
following statement.

ActiveChart.SetSourceData Source:=Sheets("HP Analyst & Q
time").Range("A1:A3", topLeftOfGraphData & ":" &
bottomRightOfGraphData), PlotBy:=xlRows

As I had gathered, I was making a trivial mistake and solution required
changes in the "Range" part of the statement where I simply moved the
last of first set of double quotes past the comma and added an "&"
symbol next to the variable "topLeftOfGraphData". The statement now
looks like this.

ActiveChart.SetSourceData Source:=Sheets("HP Analyst & Q
time").Range("A1:A3," & topLeftOfGraphData & ":" &
bottomRightOfGraphData), PlotBy:=xlRows

Thank you kindly
Khurram



Khurram