ExcelBanter - ActiveSheet in a Macro?

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - ActiveSheet in a Macro? (https://www.excelbanter.com/excel-programming/322091-activesheet-macro.html)

ActiveSheet in a Macro?

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(TemplateQ T!
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 Whe=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?

ActiveSheet in a Macro?

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 Whe=xlLocationAsObject, _
Name:="TemplateQT"
ActiveChart.HasLegend = False
End Sub

--
Regards,
Tom Ogilvy

"Randy S" wrote in message
...
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(TemplateQ T!
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 Whe=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?

ActiveSheet in a Macro?

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

-----Original Message-----
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 Whe=xlLocationAsObject, _
Name:="TemplateQT"
ActiveChart.HasLegend = False
End Sub

--
Regards,
Tom Ogilvy

"Randy S" wrote in
message
...
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 Whe=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?

.

ActiveSheet in a Macro?

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 Whe=xlLocationAsObject, _
Name:=sName
ActiveChart.HasLegend = False
End Sub

--
Regards,
Tom Ogilvy

"Randy S" wrote in message
...
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

-----Original Message-----
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 Whe=xlLocationAsObject, _
Name:="TemplateQT"
ActiveChart.HasLegend = False
End Sub

--
Regards,
Tom Ogilvy

"Randy S" wrote in
message
...
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 Whe=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?

.