MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Generic Chart Macro

Posted by BB on October 04, 2001 3:32 PM

I have the following code that runs only on sheet name 17, but would like to make it generic to run on any other similar spreadsheets. Anyone who can help me out? Any help appreciated. Thanks!!

ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=Sheets("17").Range("A1:AM5"), PlotBy:= _
ActiveChart.SeriesCollection(1).XValues = "='17'!R7C2:R54C2"
ActiveChart.SeriesCollection(1).Values = "='17'!R7C33:R54C33"
ActiveChart.SeriesCollection(1).Name = "=""Actual"""
ActiveChart.Location Where:=xlLocationAsObject, Name:="17"

Posted by Juan Pablo on October 05, 2001 9:24 AM

Try with

Sub GenChart()
Dim ShName As Worksheet
Set ShName = ActiveSheet
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=ShName.Range("A1:AM5"), PlotBy:= _
ActiveChart.SeriesCollection(1).XValues = "='" & ShName.Name & "'!R7C2:R54C2"
ActiveChart.SeriesCollection(1).Values = "='" & ShName.Name & "'!R7C33:R54C33"
ActiveChart.SeriesCollection(1).Name = "=""Actual"""
ActiveChart.Location Where:=xlLocationAsObject, Name:=ShName.Name
End Sub

Juan Pablo