Macro for multiple charts
Try this out.
Sub ChartMakro()
Dim vSheet As String
Num_Sheets = Sheets.Count 'Determine the
number of sheets inthe 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
ActiveChart.Location Whe=xlLocationAsObject,
Name:=Sheets(vSheet).Name
With ActiveChart
Sheets(x).Activate
.HasTitle = True
.ChartTitle.Characters.Text = ActiveSheet.Name
End With
Next
x 'Loop
to next sheet
End Sub
-----Original Message-----
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
.
|