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:

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

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:

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


In the sheet1 codmodule.

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



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.