Macro to change new chart's source data to reference cells.

**jrod20** · 11-27-2012, 05:48 PM

Hello All,

Let me start by saying that I don't think a dynamically ranged chart will work for what I want. Myself and others will be making many more graphs as time goes on, and you'd have to go in and set up each and every new chart separately with the naming and whatnot.

I'm trying to write a macro that refers to two cells at the top of the worksheet to update a graph's source data. The columns for each series will not change, only the rows. I've copied & pasted a chart. Now, for the newly pasted chart, I want to change the source data, but just the rows in each of the series.

In cell A1, I have the value 1001.
In cell A2, I have the value 2000.

Here's the code I've recorded manually:

Please Login or Register  to view this content.

Can I reproduce the result of the above code by referencing the values in cells A1 and A2? My idea looks like this (but doesn't work):

Please Login or Register  to view this content.

Thanks for any tips thrown my way!!

**NickyC** · 11-27-2012, 09:53 PM

Hi
have you tried using offset and referring to the values in cells A1 and A2,
something like

Please Login or Register  to view this content.

**jrod20** · 11-28-2012, 11:20 AM

NickyC,
Thanks for the quick reply!

I think OFFSET will work, but I need a boost since I'm a newb at VBA. Here's what I tried (note that I've changed the name of the worksheet where the data table is located to 'Data Dump'):

ActiveChart.SeriesCollection(1).XValues = Sheets("'Data Dump'").Range("B1002:B2001").Offset(Sheets("'Data Dump'").Range("A1"), 0).Value
ActiveChart.SeriesCollection(1).Values = Sheets("'Data Dump'").Range("K1002:K2001").Offset(Sheets("'Data Dump'").Range("A1"), 0).Value

...and I got the error: "Subscript out of range".

In looking for the problem, I tried entering the actual value from A1 in the OFFSET block, rather than use a cell reference:

ActiveChart.SeriesCollection(1).XValues = Sheets("'Data Dump'").Range("B1002:B2001").Offset(1001, 0).Value
ActiveChart.SeriesCollection(1).Values = Sheets("'Data Dump'").Range("K1002:K2001").Offset(1001, 0).Value

...and I got the error: "Subscript out of range".
Your thoughts?

**jrod20** · 11-28-2012, 11:45 AM

NickyC,

I got it to work. Here's what I ended up with:

Please Login or Register  to view this content.

Thank you very much for your help!

Macro to change new chart's source data to reference cells.

LinkBack

Thread Tools

Rate This Thread

Display

Macro to change new chart's source data to reference cells.

Re: Macro to change new chart's source data to reference cells.

Re: Macro to change new chart's source data to reference cells.

Re: Macro to change new chart's source data to reference cells.

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions