Auto Create Charts from Different Rows in Excel

[Ashberrie]

Hello!

I'm very new to VBA and have managed to create most of what I need; but am stuck on the hardest part IMO - Looping through the rows to create each chart.

What I'm looking to achieve: Creating a chart for each row of data in my excel sheet. Each row of data has three years of monthly data; the chart needs to have January compared to January compared to January (and so on for each month). Each chart needs to be exported as a PDF into a specific folder then delete the chart. (I have 755 rows - yes, I know it's a lot of charts)

What I've been able to do: Create the chart in VBA for a single row.

Where I'm stuck: I don't know how to get it to loop through each row and create a chart for each row.

Here's the sample data I'm working with:

Category	Jan	Feb	Mar	Jan	Feb	Mar	Jan	Feb	Mar
A	29	58	13	42	18	33	107	44	84
B	38	76	17	54	23	43	138	57	109
C	62	124	28	89	37	71	226	94	179
D	77	153	35	110	46	87	280	117	222
E	71	143	32	103	43	81	260	108	206
F	62	123	28	89	37	70	225	94	178

<tbody>
</tbody>

This is the VBA code I have currently:

Sub ChartCreator()

'Create new Workbook
Workbooks.Add


'Add chart and define style
    Dim ChartSheet1 As Chart
    
    Set ChartSheet1 = Charts.Add
        With ChartSheet1
            .ChartType = xlColumnClustered
            .ChartStyle = 215
            .ChartColor = 10
            .HasDataTable = True
            .HasTitle = True
            .HasLegend = False
            .ChartTitle.Text = Workbooks("VBA Chart Creator.xlsm").Sheets("Sheet1").Range("A2")
            '.Name = Workbooks("VBA Chart Creator.xlsm").Sheets("Sheet1").Range("A2")
        End With


'1st year of data
    Set Series1 = ActiveChart.SeriesCollection.NewSeries
        With Series1
            .Name = "Year1"
            'Sets Vertical Axis/Data
            .Values = "='[VBA Chart Creator.xlsm]Sheet1'!B2:D2"
            'sets Horizontal Axis Label
            .XValues = "='[VBA Chart Creator.xlsm]Sheet1'!$B$1:$D$1"
        End With


'2nd year of data
    Set Series2 = ActiveChart.SeriesCollection.NewSeries
        With Series2
            .Name = "Year2"
            'Sets Vertical Axis/Data
            .Values = "='[VBA Chart Creator.xlsm]Sheet1'!E2:G2"
            'sets Horizontal Axis Label
            .XValues = "='[VBA Chart Creator.xlsm]Sheet1'!$B$1:$D$1"
        End With
        
'3rd year of data
    Set Series2 = ActiveChart.SeriesCollection.NewSeries
        With Series2
            .Name = "Year3"
            'Sets Vertical Axis/Data
            .Values = "='[VBA Chart Creator.xlsm]Sheet1'!H2:J2"
            'sets Horizontal Axis Label
            .XValues = "='[VBA Chart Creator.xlsm]Sheet1'!$B$1:$D$1"
        End With
        
'Names worksheet same as Item #/Chart Title
ActiveSheet.Name = Workbooks("VBA Chart Creator.xlsm").Sheets("Sheet1").Range("A2").Value


'Set Page size as Legal with 1/2 inch margins
With ActiveChart.PageSetup
    .PaperSize = xlPaperLegal
    .RightFooter = "&8Printed " & Format(DateTime.Now, "MM/DD/YYYY")
    .LeftMargin = Application.InchesToPoints(0.5)
    .RightMargin = Application.InchesToPoints(0.5)
    .TopMargin = Application.InchesToPoints(0.5)
    .BottomMargin = Application.InchesToPoints(0.5)
    .HeaderMargin = Application.InchesToPoints(0.3)
    .FooterMargin = Application.InchesToPoints(0.3)
End With


'Export Chart as PDF
Chart.ExportAsFixedFormat Type:=xlTypePDF
        
End Sub

OS: Mac OS Sierra 10.12.6
Version: Excel for Mac ver 16.15

Any help would be immensely appreciated! Thank you

[FONT=&quot]Save[/FONT][FONT=&quot]Save[/FONT]​