Macro to change chart references

J

Jobe

I have a work book that contains 70 graphs that need to be modified. I am
attempting to record a macro that will change the the location of the series
of cells that the chart graphs. The problem is that my macro changes all the
charts to refferance a specific sheet instead of the active sheet. Below is
an exert of the macro. I want to change the 'Test 1 (2)' to the activesheet.

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).XValues = "='Test 1 (2)'!R20C1:R54C1"
ActiveChart.SeriesCollection(1).Values = "='Test 1 (2)'!R20C7:R54C7"
ActiveChart.SeriesCollection(2).XValues = "='Test 1 (2)'!R20C1:R54C1"
ActiveChart.SeriesCollection(2).Values = "='Test 1 (2)'!R20C5:R54C5"
ActiveChart.SeriesCollection(3).XValues = "='Test 1 (2)'!R20C1:R54C1"
ActiveChart.SeriesCollection(3).Values = "='Test 1 (2)'!R20C6:R54C6"
 
T

Tom Ogilvy

s = "'" & Activesheet.Name
ActiveChart.SeriesCollection(1).XValues = "=" & s & "'!R20C1:R54C1"

and so forth.
 
J

Jobe

Thanks Tom,

But it did not work. This is what I tried,

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).XValues = "=" & s & "'!R20C1:R54C1"
 
J

Jobe

Thanks Tom,

But it did not work.. This is what I tried.

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).XValues = "=" & s & "'!R20C1:R54C1"

Is this what you meant?
 
T

Tom Ogilvy

It didnt' work because you didn't do what I showed you.


s = "'" & ActiveSheet.Name
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).XValues = "=" & s & "'!R20C1:R54C1"
 
T

Tom Ogilvy

It isn't want I said. I meant what I said. Perhaps you should read all the
message and adapt your code to reflect what I posted.
 
J

JLGWhiz

If you open your chart and click on the series, and from the menu click
Source Data, then click the Series Tab, you will see that the Sheet name
reference is the syntax used.
 
J

Jobe

I apologize for my incompetence Tom,

This works for SeriesCollection(1) but for some reason stops at
SeriesColection(2).

It appears to me that you are creating a varible "s" and setting it equal to
ActiveSheet.Name.

I do not know what the "'" & are for.

Would you please explain.

This is what I have know.

Please excuse my ignorance.

s = "'" & ActiveSheet.Name
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).XValues = "=" & s & "'!R20C1:R54C1"
ActiveChart.SeriesCollection(1).Values = "=" & s & "'!R20C7:R54C7"
ActiveChart.SeriesCollection(2).XValues = "=" & s & "!R20C1:R54C1"
ActiveChart.SeriesCollection(2).Values = "=" & s & "!R20C5:R54C5"
ActiveChart.SeriesCollection(3).XValues = "=" & s & "!R20C1:R54C1"
ActiveChart.SeriesCollection(3).Values = "=" & s & "!R20C6:R54C6"
 
D

Dave Peterson

Sometimes the worksheet name needs to be enclosed in apostrophes (when there's a
space in the worksheet name for example).

Tom's example had this:
....= "=" & s & "'!R20C1:R54C1"

In your code, you added the apostrophe to the top two lines, but not the bottom
four lines:

Fix them and try again.
 
J

JLGWhiz

I do not know what the "'" & are for.
Would you please explain.

The "'" & is to place a single quote mark before the Worksheet name
contained in the s variable so that when it is concatenated to the rest of
the code line & !'R20C1:R54C1" it will post to the file as 'Test 1 (2)!' or
whatever sheet you happen to be on at the time.

Like Dave said, without the single quotes and double quotes in the right
places, it won't work.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top