Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to change chart references
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" |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to change chart references
s = "'" & Activesheet.Name
ActiveChart.SeriesCollection(1).XValues = "=" & s & "'!R20C1:R54C1" and so forth. -- Regards, Tom Ogilvy "Jobe" wrote: 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" |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to change chart references
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" |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to change chart references
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to change chart references
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" -- Regards, Tom Ogilvy "Jobe" wrote: 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" |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to change chart references
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. -- Regards, Tom Ogilvy "Jobe" wrote: 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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to change chart references
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" wrote: 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" |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to change chart references
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" |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to change chart references
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. 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" Jobe wrote: 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to change chart references
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" wrote: 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" |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to change chart references
Thank you guys for your help!
It works now and you have saved me a great deal of time. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to change position of chart labels on line chart | Charts and Charting in Excel | |||
Macro to change chart name only once | Excel Discussion (Misc queries) | |||
Macro to change chart name only once | Excel Discussion (Misc queries) | |||
chart MAcro to change on activecell | Excel Discussion (Misc queries) | |||
change a macro so that it copies and pastes data as values rather than references | Excel Programming |