Macro for multiple charts

J

JS

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 Where:=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
 
D

DMoney

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 Where:=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
Click to expand...
(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 Where:=xlLocationAsObject, Name:=ActiveSheet.Name

With ActiveChart
Sheets(x).Activate
.HasTitle = True
.ChartTitle.Characters.Text = ActiveSheet.Name

End With


Next
Click to expand...
x 'Loop
to next sheet
 
J

JS

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




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 Where:=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
Click to expand...
(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 Where:=xlLocationAsObject, Name:=ActiveSheet.Name

With ActiveChart
Sheets(x).Activate
.HasTitle = True
.ChartTitle.Characters.Text = ActiveSheet.Name

End With


Next
Click to expand...
x 'Loop
to next sheet
 
D

DMoney

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 Where:=xlLocationAsObject, _
Name:=Sheets(vSheet).Name
With ActiveChart
Sheets(x).Activate
.HasTitle = True
.ChartTitle.Characters.Text = ActiveSheet.Name
End With
Next x

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro for charting 4
Trying to Learn Code to Copy Charts 2
conso macro 0
Multiple charts in microsoft excel VBA macro 1
Run macro from active workbook whose name keeps changing 20
Excel Macro for Charts 1
Loop to create charts 1
Copy Data from Closed File to Existing File 0

Top