Macro to change chart references

[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"

 

[Tom Ogilvy]

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

and so forth.

 

[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"

 

[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?

 

[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"

 

[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.

 

[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.

 

[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"

 

[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.

 

[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.

 

[Jobe]

Thank you guys for your help!
It works now and you have saved me a great deal of time.