Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
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
Posted to microsoft.public.excel.charting
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
multiple charts on one sheet | Charts and Charting in Excel | |||
Creating Multiple Pie Charts from Excel Sheet | Charts and Charting in Excel | |||
multiple pivote charts in one sheet | Excel Discussion (Misc queries) | |||
How do I place multiple charts in the same sheet? | Charts and Charting in Excel | |||
Print multiple excel charts within one sheet to fit to a page lik. | Excel Discussion (Misc queries) |