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:
[TABLE="class: grid, width: 450"]
<tbody>[TR]
[TD="width: 67"]Category[/TD]
[TD="width: 31, align: right"]Jan[/TD]
[TD="width: 33, align: right"]Feb[/TD]
[TD="width: 36, align: right"]Mar[/TD]
[TD="width: 33, align: right"]Jan[/TD]
[TD="width: 33, align: right"]Feb[/TD]
[TD="width: 36, align: right"]Mar[/TD]
[TD="width: 33, align: right"]Jan[/TD]
[TD="width: 33, align: right"]Feb[/TD]
[TD="width: 36, align: right"]Mar[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]107[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]84[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]76[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]138[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]109[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]62[/TD]
[TD="align: right"]124[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]89[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]71[/TD]
[TD="align: right"]226[/TD]
[TD="align: right"]94[/TD]
[TD="align: right"]179[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD="align: right"]77[/TD]
[TD="align: right"]153[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]87[/TD]
[TD="align: right"]280[/TD]
[TD="align: right"]117[/TD]
[TD="align: right"]222[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD="align: right"]71[/TD]
[TD="align: right"]143[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]103[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]81[/TD]
[TD="align: right"]260[/TD]
[TD="align: right"]108[/TD]
[TD="align: right"]206[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD="align: right"]62[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]89[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]225[/TD]
[TD="align: right"]94[/TD]
[TD="align: right"]178[/TD]
[/TR]
</tbody>[/TABLE]

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]​