Creating Multiple Charts

[allison]

I know what my problem is, but not sure how to fix it.

I have 100 rows of data - each row needs to be it's own chart. I keystroked the macro to make the chart, but I need to make the chart number a variable so that it will work for all the rows.

This is what I have:

[VBA]ActiveCell.Range("A1:J2").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range("Sheet2!$A$1:$J$2")
ActiveChart.ChartType = xlLine
ActiveChart.Legend.Select
Selection.Delete
ActiveSheet.ChartObjects("Chart 6").Activate
ActiveChart.SeriesCollection(1).Select
ActiveSheet.ChartObjects("Chart 6").Activate
ActiveChart.SeriesCollection(1).ApplyDataLabels
ActiveSheet.ChartObjects("Chart 6").Activate
ActiveChart.SeriesCollection(1).Trendlines.Add
ActiveSheet.ChartObjects("Chart 6").Activate
ActiveChart.SeriesCollection(1).Trendlines(1).Select
ActiveWindow.SmallScroll ToRight:=2
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
ActiveSheet.ChartObjects("Chart 6").Activate
ActiveChart.PlotArea.Select
Selection.ClearFormats
ActiveSheet.ChartObjects("Chart 6").Activate
ActiveChart.ChartArea.Select
ActiveChart.Parent.Delete
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
Selection.Delete Shift:=xlUp
End Sub[/VBA]

How is the best way to do this? Any suggestions?

[lucas]

I think Bob offered this solution a while back. I use it frequently:
In a standard module:
[vba]
Option Explicit
Sub PrintCharts()
Dim cel As Range
With Sheets("sheet1")
For Each cel In Range(.Cells(2, 1), .Cells(2, 1).End(xlDown))
cel.Select
Sheets("Chart1").PrintPreview '.PrintOut
Next
End With
Range("A1").Select
End Sub

[/vba]
In the sheet1 codmodule.
[VBA]
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Dest As Range, cel As Range
Set Dest = Range("AA2").Resize(1, 12)
Application.ScreenUpdating = False
If Target.Column = 1 And Target.Row > 1 Then
Target.Resize(1, 12).Copy Dest
For Each cel In Dest
If cel = "" Then
cel.ColumnWidth = 0
Else
cel.ColumnWidth = 12
End If
Next
End If
Application.ScreenUpdating = True
End Sub
[/VBA]


be sure to look at the code in sheet 1 because it copies the data to a different location for each chart to be created from.


This is not real easy to implement but you can figure it out if you study the example........you have to create a chart sheet for the code to use.

example attached.