Macro for charting

  • Thread starter Thread starter JS
  • Start date Start date
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
 
Juergen -

You were on the right track. A modified macro is shown below. There are a few changes:

* Use 'Option Explicit' at the top. This forces you to declare (Dim) all variables,
which is a good thing. You can make it the default in the VB Editor: Tools menu >
Options > Editor tab, CHECK the Require Variable Declaration box.

* All variables are declared. All declared variables are used. You set Num_Sheets
and sSheet (you called it vSheet) and never used either.

* Use Worksheets not Sheets. If you leave a stray chart sheet in the workbook,
you'll have problems.

Future enhancements include creating chart objects directly (not as charts which
then are relocated to the worksheets) and avoiding the selection and activation of
objects. You can do some extra credit by reading this web page prior to tomorrow's
class:

http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Option Explicit

Sub ChartMakro()

Dim sSheet As String
Dim Num_Sheets as Long
Dim x As Long

Num_Sheets = ActiveWorkbook.Worksheets.Count 'Determine the number of worksheets

For x = 1 To Num_Sheets 'Loop through all sheets,

ActiveWorkbook.Worksheets(x).Activate 'Activate the sheet

sSheet = ActiveSheet.Name

Charts.Add
ActiveChart.ChartType = xlLineStacked
ActiveChart.SetSourceData Source:=Worksheets(sSheet).Range("C12:X145"), _
PlotBy:=xlColumns
Worksheets(sSheet).Activate
ActiveChart.Location Where:=xlLocationAsObject, Name:=sSheet

With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = sSheet
End With

Next x 'Loop to next sheet

End Sub

_______
 
Hello Jon

thanks a lot for the support, I appreciate it a lot.

Unfortunatelly, the macro is not running. Every time I run it, it stops at
the following line:

ActiveChart.Location Where:=xlLocationAsObject, Name:=sSheet

The debugger tells me following:
Object variable or with-block-variable not specified.
(this is a translation of the german debugger message).

Could you help me to find out, what that means?

Regards
Juergen




Jon Peltier said:
Juergen -

You were on the right track. A modified macro is shown below. There are a few changes:

* Use 'Option Explicit' at the top. This forces you to declare (Dim) all variables,
which is a good thing. You can make it the default in the VB Editor: Tools menu >
Options > Editor tab, CHECK the Require Variable Declaration box.

* All variables are declared. All declared variables are used. You set Num_Sheets
and sSheet (you called it vSheet) and never used either.

* Use Worksheets not Sheets. If you leave a stray chart sheet in the workbook,
you'll have problems.

Future enhancements include creating chart objects directly (not as charts which
then are relocated to the worksheets) and avoiding the selection and activation of
objects. You can do some extra credit by reading this web page prior to tomorrow's
class:

http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Option Explicit

Sub ChartMakro()

Dim sSheet As String
Dim Num_Sheets as Long
Dim x As Long

Num_Sheets = ActiveWorkbook.Worksheets.Count 'Determine the number of worksheets

For x = 1 To Num_Sheets 'Loop through all sheets,

ActiveWorkbook.Worksheets(x).Activate 'Activate the sheet

sSheet = ActiveSheet.Name

Charts.Add
ActiveChart.ChartType = xlLineStacked
ActiveChart.SetSourceData
Click to expand...
Source:=Worksheets(sSheet).Range("C12:X145"), _
 
The offending line is the one above the error:

Worksheets(sSheet).Activate

This activates the worksheet, so you no longer have an active chart, so the next
line breaks. Remove the offending line above.

(Unfortunately I didn't take the time to test the code, I just wrote it up quickly.
Sorry.)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Hello Jon

thanks a lot for the support, I appreciate it a lot.

Unfortunatelly, the macro is not running. Every time I run it, it stops at
the following line:

ActiveChart.Location Where:=xlLocationAsObject, Name:=sSheet

The debugger tells me following:
Object variable or with-block-variable not specified.
(this is a translation of the german debugger message).

Could you help me to find out, what that means?

Regards
Juergen






few changes:


menu >


activation of


Source:=Worksheets(sSheet).Range("C12:X145"), _


sheets in
Click to expand...

by" -column. I
[/QUOTE]
 
Back
Top