MrExcel.com - 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!!

Charts.Add
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=Sheets("17").Range("A1:AM5"), PlotBy:= _
xlRows
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
Charts.Add
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=ShName.Range("A1:AM5"), PlotBy:= _
xlRows
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