Macro for multiple charts
 

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 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