Excel macro to loop through worksheets and graph data from each worksheet

[deb]

I am trying to create an excel macro that will loop through 22
worksheets in a workbook and create an graph with data from each
worksheet. These data are located in the same positions in each
worksheet, however, the range of data varies across worksheets. I
found a code to loop through worksheets. How do I code a macro that
produces a graph based on each active worksheet and current region of
cells in each worksheet as it loops through the worksheets? Any
ideas???? I have never coded in VB before and do not have a lot of
experience with coding excel macros. I posted my novice code here so
that someone who has more experience with VB or excel macro may,
hopefully, get a better idea of what I am trying to do.




Sub WorksheetLoop()

Dim WS_Count As Integer
Dim I As Integer

' Set WS_Count equal to the number of worksheets in the active
' workbook.
WS_Count = ActiveWorkbook.Worksheets.Count

' Begin the loop.
For I = 1 To WS_Count
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData
Source:=ActiveWorkbook.Worksheets(I).Range("A1").CurrentRegion,
PlotBy:=
xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text =
"Marker"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "LOD
Score"
End With
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
ActiveChart.HasDataTable = False
' The following line shows how to reference a sheet within
' the loop by displaying the worksheet name in a dialog box.
MsgBox ActiveWorkbook.Worksheets(I).Name

Next I

End Sub

 

[Paul D]

why not loop through the sheets collection? I did not test this so no
guarantee

Sub WorksheetLoop()
Dim WS_Count As Integer
Dim I As Integer
Dim Sh As Worksheet

For Each Sh In Sheets
sh.activate
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData
Source:=ActiveWorkbook.Sh.Range("A1").CurrentRegion,
PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Marker"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "LOD Score"
End With
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
ActiveChart.HasDataTable = False
' The following line shows how to reference a sheet within
' the loop by displaying the worksheet name in a dialog box.
MsgBox ActiveWorkbook.Sh.Name
Next Sh
End Sub

 

[Don Guillett]

Instead why don't you create ONE chart and use an input box and import the
requested page's data into the sheet where the data range exists. Sure would
be a lot less overhead on the workbook and memory.

 

[steve]

Don,

Ran into a similar problem a while back where I had about 50 sheets with 10
charts each. The workbook became unbearable. So I made a 'template' sheet
and had the print macro just adjust the formula references to pull in the
data from the main worksheets. Works fast and furious and sure cut down on
the overhead...

 

[deb]

I tried this macro but it won't run(macro below). I think that these
lines are the problems:

"ActiveChart.SetSourceData
Source:=ActiveWorkbook.Sh.Range("A1").CurrentRegion,
PlotBy:=xlColumns"

Any ideas if the coding is correct? Anyone?

 

[steve]

Deb,

Source data has to take the form: $A$1:$E1 or $A$1:$A$5
Note that this is a single row or single column range.

Try
Source:=ActiveWorkbook.Sh.Range("A1").CurrentRegion.Address

Try using the recorder to double check your code. Include Goto Special
CurrentRegion.

This is what I got:

Dim x As String
x = Selection.CurrentRegion.Address
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("Sheet3").Range(x)
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet3"