-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Trying to specify a range name in a chart series
- Thread starter Mr930
- Start date
Datsmart
Well-known Member
- Joined
- Jun 19, 2003
- Messages
- 7,985
Your question of, "not have subsequent files refer back to the original?", tells me you might be copying your chart. When you do that, it still refers back to the original table of data.
Couple of suggestions:
You can use code to rebuild the chart entirely like Greg Truby shows here:
http://www.mrexcel.com/board2/viewtopic.php?t=213900
-or-
You can use just portions of his code to assign new ranges to your existing chart.
On the following site are instructions on how to setup a Dynamic Chart that adjusts to changing ranges.
http://peltiertech.com/Excel/Charts/Dynamics.html
Hope this helps.
Couple of suggestions:
You can use code to rebuild the chart entirely like Greg Truby shows here:
http://www.mrexcel.com/board2/viewtopic.php?t=213900
-or-
You can use just portions of his code to assign new ranges to your existing chart.
On the following site are instructions on how to setup a Dynamic Chart that adjusts to changing ranges.
http://peltiertech.com/Excel/Charts/Dynamics.html
Hope this helps.
Upvote
0
I will be copying the whole workbook (.xls file) since I will change my data but still be using the same spreadsheet. I am worried if I need to hard code the workbook name into my chart range name, that it will not be correct once I copy the .xls to a new name. WOn't the chart still refer back to the orignal range name? I was thinking I could use the ActiveWorkbook property somehow in my chart but that gives me an error.
thanks
Fred
thanks
Fred
Upvote
0
Datsmart
Well-known Member
- Joined
- Jun 19, 2003
- Messages
- 7,985
You can assign the active workbook name to a variable and use the variable in your code.
This code will reassign the third series of Chart # 7 to a named range called "Data3".
Change "Chart 7" to whatever your chart number is.
Change "Data3" to whatever name your range is.
Add code to the macro to setup each of your chart Series to the current workbook.
Be sure to change the number in, "SeriesCollection(3)" for each series you are working with.
Hope you can use this.
This code will reassign the third series of Chart # 7 to a named range called "Data3".
Code:
Sub Macro1()
' Create variable for Workbook Name
MyWBName = ActiveWorkbook.Name
'input single quotes and Exclamation mark around wb name
MyWBName2 = "='" & MyWBName & "'!"
' Assign variable to third series of chart number 7
ActiveSheet.ChartObjects("Chart 7").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(3).Values = MyWBName2 & "Data3"
ActiveWindow.Visible = False
Windows(MyWBName).Activate
Range("A1").Select
End SubChange "Data3" to whatever name your range is.
Add code to the macro to setup each of your chart Series to the current workbook.
Be sure to change the number in, "SeriesCollection(3)" for each series you are working with.
Hope you can use this.
Upvote
0
Similar threads
- Question
- Question
