macro to create charts in worksheets with arbitrary names

I'm trying to write a macro to create and modify charts in
spreadsheets with many worksheets. The problem I cannot
solve is that the macro uses the current worksheet name
(here Channel_1) in the code, as in the following short
example:

' Keyboard Shortcut: Ctrl+a
'
Range("G2:H11").Select
Charts.Add
ActiveChart.ChartType = xlXYScatterLinesNoMarkers
ActiveChart.SetSourceData Source:=Sheets
("Channel_1").Range("G2:H11")
ActiveChart.Location Whe=xlLocationAsObject,
Name:="Channel_1"
End Sub

which prevents it from running in a different worksheet. I
tried invented statements like .ActiveWorksheet. etc, but
it didn't work...

How to make the worksheet name generic to the macro
without having to rename the worksheet before the macro is
run?

z.entropic

Excel doesn't have an ActiveWorksheet, but it does have an ActiveSheet.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

z.entropic wrote:

I'm trying to write a macro to create and modify charts in
spreadsheets with many worksheets. The problem I cannot
solve is that the macro uses the current worksheet name
(here Channel_1) in the code, as in the following short
example:

' Keyboard Shortcut: Ctrl+a
'
Range("G2:H11").Select
Charts.Add
ActiveChart.ChartType = xlXYScatterLinesNoMarkers
ActiveChart.SetSourceData Source:=Sheets
("Channel_1").Range("G2:H11")
ActiveChart.Location Whe=xlLocationAsObject,
Name:="Channel_1"
End Sub

which prevents it from running in a different worksheet. I
tried invented statements like .ActiveWorksheet. etc, but
it didn't work...

How to make the worksheet name generic to the macro
without having to rename the worksheet before the macro is
run?

z.entropic

Tried that, too--to no avail!

z.entropic

-----Original Message-----
Excel doesn't have an ActiveWorksheet, but it does have
an ActiveSheet.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

z.entropic wrote:

I'm trying to write a macro to create and modify charts
in
spreadsheets with many worksheets. The problem I
cannot
solve is that the macro uses the current worksheet name
(here Channel_1) in the code, as in the following short
example:

' Keyboard Shortcut: Ctrl+a
'
Range("G2:H11").Select
Charts.Add
ActiveChart.ChartType = xlXYScatterLinesNoMarkers
ActiveChart.SetSourceData Source:=Sheets
("Channel_1").Range("G2:H11")
ActiveChart.Location Whe=xlLocationAsObject,
Name:="Channel_1"
End Sub

which prevents it from running in a different
worksheet. I
tried invented statements like .ActiveWorksheet. etc,
but
it didn't work...

How to make the worksheet name generic to the macro
without having to rename the worksheet before the macro
is
run?

z.entropic

.

The Charts.Add creates a new chart in its own sheet. ActiveSheet at
that point refers to the newly minted chartsheet!

Use something like the untested:

dim SrcSheet as worksheet, aChart as chart
set srcsheet=activesheet
set achart=charts.add
achart.SetSourceData Source:=srcsheet.Range("G2:H11")
....

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Tried that, too--to no avail!

z.entropic

-----Original Message-----
Excel doesn't have an ActiveWorksheet, but it does have
an ActiveSheet.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

z.entropic wrote:

I'm trying to write a macro to create and modify charts
in
spreadsheets with many worksheets. The problem I
cannot
solve is that the macro uses the current worksheet name
(here Channel_1) in the code, as in the following short
example:

' Keyboard Shortcut: Ctrl+a
'
Range("G2:H11").Select
Charts.Add
ActiveChart.ChartType = xlXYScatterLinesNoMarkers
ActiveChart.SetSourceData Source:=Sheets
("Channel_1").Range("G2:H11")
ActiveChart.Location Whe=xlLocationAsObject,
Name:="Channel_1"
End Sub

which prevents it from running in a different
worksheet. I
tried invented statements like .ActiveWorksheet. etc,
but
it didn't work...

How to make the worksheet name generic to the macro
without having to rename the worksheet before the macro
is
run?

z.entropic

.

Missed that. Of course, the other solution is to use

ActiveSheet.ChartObjects.Add(L, T, W, H).Select
ActiveChart.SetSourceData Source:=ActiveSheet.Range("G2:H11")

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Tushar Mehta wrote:

The Charts.Add creates a new chart in its own sheet. ActiveSheet at
that point refers to the newly minted chartsheet!

Use something like the untested:

dim SrcSheet as worksheet, aChart as chart
set srcsheet=activesheet
set achart=charts.add
achart.SetSourceData Source:=srcsheet.Range("G2:H11")
....

Now you both lost me--how do I use your code if my recorded macro is full of references to the specific name of the renamed active worksheet:

' Keyboard Shortcut: Ctrl+a
'
Range("H2:H10").Select
Charts.Add
ActiveChart.ChartType = xlXYScatterLinesNoMarkers
ActiveChart.SetSourceData Source:=Sheets("Data").Range("H2:H10"), PlotBy:= _
xlColumns
ActiveChart.SeriesCollection(1).Values = "=Data!R2C8:R1000C8"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "=Data!R2C9:R1000C9"
ActiveChart.SeriesCollection(1).Name = "=Data!R1C9"
ActiveChart.SeriesCollection(2).XValues = "=Data!R2C10:R1000C10"
ActiveChart.SeriesCollection(2).Values = "=Data!R2C8:R1000C8"
ActiveChart.SeriesCollection(2).Name = "=Data!R1C10"
ActiveChart.Location Whe=xlLocationAsObject, Name:="Data"
With ActiveChart

z.entropic


"Jon Peltier" wrote:

Missed that. Of course, the other solution is to use

ActiveSheet.ChartObjects.Add(L, T, W, H).Select
ActiveChart.SetSourceData Source:=ActiveSheet.Range("G2:H11")

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Tushar Mehta wrote:

The Charts.Add creates a new chart in its own sheet. ActiveSheet at
that point refers to the newly minted chartsheet!

Use something like the untested:

dim SrcSheet as worksheet, aChart as chart
set srcsheet=activesheet
set achart=charts.add
achart.SetSourceData Source:=srcsheet.Range("G2:H11")
....