Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 1
Default Charts in Multiple Sheet in Excel

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 Whe=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
  #2   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 1,549
Default Charts in Multiple Sheet in Excel

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 Befo=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"
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 Whe=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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
multiple charts on one sheet afdmello Charts and Charting in Excel 1 June 20th 09 10:52 PM
Creating Multiple Pie Charts from Excel Sheet geezey Charts and Charting in Excel 1 September 5th 08 03:37 AM
multiple pivote charts in one sheet squared chart area[_2_] Excel Discussion (Misc queries) 0 June 13th 07 01:09 PM
How do I place multiple charts in the same sheet? ledmari Charts and Charting in Excel 4 September 19th 05 06:56 PM
Print multiple excel charts within one sheet to fit to a page lik. lal Excel Discussion (Misc queries) 1 February 18th 05 10:47 PM


All times are GMT +1. The time now is 03:49 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"