Me again.

I am trying to create a macro to create a chart as a new sheet. The
data I am charting is consistently in the same cells on multiple "data"
sheets. I am trying to create 1 chart per sheet.

I recorded a macro, and at this point:

Selection.Text = "='A-1 Data'!R4C5:R4C10"
ActiveChart.PlotArea.Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlNone

I would like to be able to automatically refer to the sheet that was
active when the macro was started. I would also like for the chart
sheet to be automatically named as per the chart title.

Can the macro also give me a user input with GUI where I can select the
xls file in which I would like the chart to be created?

Am I dreaming?

Thanks
CaliberX

Start by declaring a worksheet variable

Dim wsChartData as Worksheet

Then define it as the activesheet when the macro starts, and refer to
the data range on this sheet.

Set wsChartData = ActiveSheet

Charts.Add
With ActiveChart
.SetSourceData wsChartData.Range("A1:E10"), xlByColumn
With .PlotArea.Border
.Weight = xlThin
.LineStyle = xlNone
End With
End With

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


CaliberX wrote:

Me again.

I am trying to create a macro to create a chart as a new sheet. The
data I am charting is consistently in the same cells on multiple "data"
sheets. I am trying to create 1 chart per sheet.

I recorded a macro, and at this point:

Selection.Text = "='A-1 Data'!R4C5:R4C10"
ActiveChart.PlotArea.Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlNone

I would like to be able to automatically refer to the sheet that was
active when the macro was started. I would also like for the chart
sheet to be automatically named as per the chart title.

Can the macro also give me a user input with GUI where I can select the
xls file in which I would like the chart to be created?

Am I dreaming?

Thanks
CaliberX