The following macro will create a chart on each worksheet:
'=================================
Sub ChartMakro()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
Charts.Add
With ActiveChart
.ChartType = xlLineStacked
.SetSourceData Source:=ws.Range("C12:X145"), _
PlotBy:=xlColumns
.Location Whe=xlLocationAsObject, _
Name:=ws.Name
End With
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = ws.Name
End With
Next ws
End Sub
'======================
JS wrote:
Hello,
I have got a little problem with a macro, that is intended to create charts
on every sheet of a workbook. The macro runs and creates all the charts, but
on the sheet that was active when the macro was started. I.e. there are 17
sheets in a workbook, then there are 17 charts on the first sheet after the
macro was running. I really donīt know where the bug is. By the way, I am
not very familiar with macros and I am working with Excel 2000.
Another goody for the macro would be to select a range on each sheet, and
the macro recognizes these individual ranges for the chart creation.
Probably there must be a change in the code in this line:
"ActiveChart.SetSourceData Source:=Sheets(vSheet).Range("C12:X145"),
PlotBy:=xlColumns"
but I really donīt know how this should look like.
Please help me.
Following the macro, as it looks currently:
----------------------------------------------------------------------------
---
Sub ChartMakro()
Dim vSheet As String
Num_Sheets = Sheets.Count 'Determine the number of sheets in
the workbook
For x = 1 To Sheets.Count 'Loop through all sheets,
beginning with Sheet 2
Sheets(x).Activate 'Activate the sheet
vSheet = ActiveSheet.Name
Charts.Add
ActiveChart.ChartType = xlLineStacked
ActiveChart.SetSourceData Source:=Sheets(vSheet).Range("C12:X145"),
PlotBy:=xlColumns
Sheets(x).Activate
ActiveChart.Location Whe=xlLocationAsObject, Name:=ActiveSheet.Name
With ActiveChart
Sheets(x).Activate
.HasTitle = True
.ChartTitle.Characters.Text = ActiveSheet.Name
End With
Next x 'Loop to next sheet
End Sub
----------------------------------------------------------------------------
--------------
Probably my posting does not contain my realname in the "send by" -column. I
donīt know how to change this in Outlook 2000.
Juergen Schweizer
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html