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

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


.

Hi,

I tried this out, but somehow excel doesn´t like the command "(x).Activate".
Maybe I misunderstood something, but the "(x).Activate" command stands at
the beginning of a line, which is not accepted by excel.

Juergen




"DMoney" schrieb im Newsbeitrag
...
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


.

the post just truncated the text try this.

Sub ChartMakro()

Dim vSheet As String
Num_Sheets = Sheets.Count
For x = 1 To Sheets.Count
Sheets(x).Activate
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

End Sub
-----Original Message-----
Hi,

I tried this out, but somehow excel doesn´t like the
command "(x).Activate".
Maybe I misunderstood something, but the "(x).Activate"
command stands at
the beginning of a line, which is not accepted by excel.

Juergen




"DMoney" schrieb im
Newsbeitrag
...
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


.



.