ActiveSheet in a Macro?

[Randy S]

I've created a macro to display a variable-length chart
based on dynamic chart example from tushar-mehta.com. It
works fine.

My question is, is there any way to make the worksheet
name based on the currently active worksheet instead of a
fixed worksheet name?

Here's my example:

Sub h()
'
' h Macro
' Macro recorded 1/31/2005 by Randy Spaulding
'
ActiveWorkbook.Names.Add Name:="YValues",
RefersToR1C1:= _
"=OFFSET(TemplateQT!R2C21,0,0,COUNTA(TemplateQT!
C21),1)"
ActiveWorkbook.Names.Add Name:="XValues",
RefersToR1C1:= _
"=OFFSET(TemplateQT!R2C1,0,0,COUNTA(TemplateQT!
C21),1)"
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues
= "=TemplateQT.xls!XValues"
ActiveChart.SeriesCollection(1).Values
= "=TemplateQT.xls!YValues"
ActiveChart.Location Where:=xlLocationAsObject,
Name:="TemplateQT"
ActiveChart.HasLegend = False
End Sub


Instead of "TemplateQT" being hardcoded as the worksheet
name, can I replace all instances of "TemplateQT" with
the current active worksheet?

 

[Tom Ogilvy]

Untested, but try this.

Sub h()
'
' h Macro
' Macro recorded 1/31/2005 by Randy Spaulding
'
ActiveWorkbook.Names.Add Name:="YValues", _
RefersToR1C1:= _
"=OFFSET('" & ActiveSheet.Name & "'!R2C21,0,0,COUNTA('" & _
Activesheet.name & "'!C21),1)"
ActiveWorkbook.Names.Add Name:="XValues", _
RefersToR1C1:= _
"=OFFSET('" & ActiveSheet.Name & "'!R2C1,0,0,COUNTA('" & _
ActiveSheet.Name & "'!C21),1)"
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues _
= "='" &ActiveSheet.Name & "'!XValues"
ActiveChart.SeriesCollection(1).Values _
= "='" & Activesheet.Name & "'!YValues"
ActiveChart.Location Where:=xlLocationAsObject, _
Name:="TemplateQT"
ActiveChart.HasLegend = False
End Sub

 

[Randy S]

This works for defining the Names, but the line:

ActiveChart.SeriesCollection(1).XValues _
= "='" &ActiveSheet.Name & "'!XValues"

gives me the following error during execution:

Run-time error '1004':
Unable to set the XValues property of the Series class

is there a way to have the active sheet referenced when
defining the XValues property??

Randy

 

[Tom Ogilvy]

Sorry about that - at that point, the active sheet is the Chart sheet
itself - so here is a tested adjustment that works:

Sub h()

sName = ActiveSheet.Name
ActiveWorkbook.Names.Add Name:="YValues", _
RefersToR1C1:= _
"=OFFSET('" & sName & "'!R2C21,0,0,COUNTA('" & _
ActiveSheet.Name & "'!C21),1)"
ActiveWorkbook.Names.Add Name:="XValues", _
RefersToR1C1:= _
"=OFFSET('" & sName & "'!R2C1,0,0,COUNTA('" & _
ActiveSheet.Name & "'!C21),1)"
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues _
= "='" & sName & "'!XValues"
ActiveChart.SeriesCollection(1).Values _
= "='" & sName & "'!YValues"
ActiveChart.Location Where:=xlLocationAsObject, _
Name:=sName
ActiveChart.HasLegend = False
End Sub