Need help in repeating Chart Build across multiple series

[ashwin390]

Hi,

I am a noob in VBA, and this is the first time i am trying to use VBA for charting.

I have 5 sheets, with 32 to 34 rows of data. For each row of data, on a separate sheet, I have calculated the minimum & maximum value, to be used set the chart Axis scale. The tables used for building the Chart has the category labels in the first row, and the subsequent 31-33 rows have to be plotted as independent charts. the first column of the table is going to be the title of the chart as well. I am using the following VBA code to create the chart i want, with custom font fromat, and manually selected maximum & minimum values for Axis scale. At the end of creating the chart, I am moving it to another sheet so that I can move all charts to a powerpoint slide and make reports. This VBA code is for the first chart (2nd row) of the first table.

How can I turn this into a zubroutine, and call it using a For loop, so that I can call that for building all 30+ charts of one table? This is for a report that I run on a quarterly basis, so I expect that I will need to keep adding rows across the different tables, and may be additional tables too. I plan to replicate the for loop of this table for the remaining 4 tables.

'Selecting Active Worksheet
    Sheets("Country Table").Select
'Building Line Chart
    Range("1:1,2:2").Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range( _
        "'Country Table'!$1:$1,'Country Table'!$2:$2")
    ActiveChart.ChartType = xlLine
'Moving Legend to Bottom
    ActiveChart.SetElement (msoElementLegendBottom)
'Reformatting Chart Category Axis format
    With ActiveChart.Axes(xlValue, xlPrimary)
        .MinimumScale = Sheets("Country Charting Data").Range("D2").Value
            ' Constant value
        .MaximumScale = Sheets("Country Charting Data").Range("G2").Value
            ' VBA variable
    End With
'Reformatting Fonts
    ActiveSheet.ChartObjects(1).Activate
    'ActiveChart.ChartTitle.Text = CONCATENATE(R1C1, " ", R3C1)
    ActiveChart.ChartTitle.Font.Size = 10
    ActiveChart.ChartTitle.Font.Name = "Arial"
    ActiveChart.ChartTitle.Font.FontStyle = "Regular"
    With ActiveChart.Axes(xlValue).TickLabels
         .AutoScaleFont = True
         With .Font
              .Name = "Arial"
              .FontStyle = "Regular"
              .Size = 7
              .Strikethrough = False
              .Superscript = False
              .Subscript = False
              .OutlineFont = False
              .Shadow = False
              .Underline = xlUnderlineStyleNone
              .ColorIndex = xlAutomatic
              .Background = xlAutomatic
         End With
    End With
    With ActiveChart.Axes(xlCategory).TickLabels
         .AutoScaleFont = True
         With .Font
              .Name = "Arial"
              .FontStyle = "Regular"
              .Size = 7
              .Strikethrough = False
              .Superscript = False
              .Subscript = False
              .OutlineFont = False
              .Shadow = False
              .Underline = xlUnderlineStyleNone
              .ColorIndex = xlAutomatic
              .Background = xlAutomatic
         End With
    End With
    ActiveChart.Legend.Font.Size = 7
    ActiveChart.Legend.Font.Name = "Arial"
    ActiveChart.Legend.Font.FontStyle = "Regular"
'Resizing Chart
    With ActiveChart.ChartArea
        .Height = 139
        .Width = 283
        .Left = 0
    End With
'Moving Chart
    ActiveSheet.ChartObjects(1).Cut
    Sheets("Country Charts").Select
    Range("A1").Select
    ActiveSheet.Paste

Any and all help is appreciated.