Charts in Multiple Sheet in Excel

  • Thread starter Thread starter Alice
  • Start date Start date
A

Alice

I am new to the VBA programming & I am trying to develop a VBA script
which will create charts (as a separate worksheet) for each worksheet
in a workbook.

Sub drill()

For i = 1 To Sheets.Count
Sheets("Sheet(i)").Select
Range("A8:I12").Select
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("Sheet(i)").Range( _
"A8:I12"), PlotBy:=xlRows
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Oxygen
Chart"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Oxygen Chart"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "US
Average"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Amount
Serviced"
End With
ActiveChart.ChartArea.Select
ActiveWindow.Zoom = 100
ActiveChart.Axes(xlValue).Select
Selection.TickLabels.NumberFormat = "$#,##0_);[Red]($#,##0)"
Sheets("Sheet(i)").Select
Range("A7").Select
Next i

End Sub

I get the error 'Subscript out of Range' Error.
If you can provide any hint to teh solution it will be of great help.

Also, if you have any suggestion of a good book/ resource for VBA,
please let me know.

Thanks,
Alice
 
Alice,
Since you are new to VBA programming and I assume new to posting to the messages boards, some tips
on getting the answer you need...
1. Provide your Excel version - only about 40% of Office users have switched to the "Ribbon"
version.
2. Along with the error description, identify the code line that caused it.
3. You provided a short description of what you are trying to accomplish (at the beginning);
this helps greatly with coming up with an answer and can provide a "hook" to capture somebody
just skimming messages.
4. "Doesn't work" - you didn't use it and please don't start.
5. Always acknowledge respondents messages.
'---
Almost any book by John Walkenbach would be helpful. I like "Power Programming with VBA...".
Debra Dalgleish has solutions to almost everything Excel here...
http://www.contextures.com/tiptech.html
Jon Peltier is the go to chart guy here... http://peltiertech.com/Excel/Charts/index.html
'---
Now for a stab at the problem...
A new sheet generally gets added to the left of the active sheet.
Your code is going from sheet 1 to sheet x, but as chart sheets are added what was Sheet(4) -for
instance- is no longer Sheet(4).
You should be able to keep things straight by looping thru the "Worksheets" instead of "Sheets".
The "Sheets" collection includes chart sheets and worksheets. The "Worksheets" collection is only
worksheets.
I would also dump the new chart sheets all over on the left side...

For i = 1 to Worksheets.Count
-and-
Charts.Add Before:=Sheets(1)
--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(Formats & Styles: lists/removes unused styles & number formats - in the free folder)




"Alice" <[email protected]>
wrote in message
I am new to the VBA programming & I am trying to develop a VBA script
which will create charts (as a separate worksheet) for each worksheet
in a workbook.

Sub drill()

For i = 1 To Sheets.Count
Sheets("Sheet(i)").Select
Range("A8:I12").Select
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("Sheet(i)").Range( _
"A8:I12"), PlotBy:=xlRows
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Oxygen
Chart"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Oxygen Chart"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "US
Average"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Amount
Serviced"
End With
ActiveChart.ChartArea.Select
ActiveWindow.Zoom = 100
ActiveChart.Axes(xlValue).Select
Selection.TickLabels.NumberFormat = "$#,##0_);[Red]($#,##0)"
Sheets("Sheet(i)").Select
Range("A7").Select
Next i

End Sub

I get the error 'Subscript out of Range' Error.
If you can provide any hint to teh solution it will be of great help.

Also, if you have any suggestion of a good book/ resource for VBA,
please let me know.

Thanks,
Alice
Click to expand...
 
Back
Top