Dynamic vs hardcoded macro

Hey folks,

New to the site and to programming, but I think this might be an easy one for the seasoned pros on here.

I need a chart that shows the last 36 months of data I've put in. I also would like to keep the old data and just keep adding to the columns for every new month, so I would like a macro that I can have move the data captured down a row every month. I recorded one, but it hard codes to the same set of 36 rows, as opposed to moving down a row every month. Any help? I have included the macro I recorded called "chartupdater" below:

Sub ChartUpdater()
'
' ChartUpdater Macro
' Once the numbers get in, this is how to update the chart
'

'
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SetSourceData Source:=Range( _
"APPROVED!$A$3:$C$3,APPROVED!$A$132:$C$179")
End Sub

Originally Posted by wptaylor4

Hey folks,

New to the site and to programming, but I think this might be an easy one for the seasoned pros on here.

I need a chart that shows the last 36 months of data I've put in. I also would like to keep the old data and just keep adding to the columns for every new month, so I would like a macro that I can have move the data captured down a row every month. I recorded one, but it hard codes to the same set of 36 rows, as opposed to moving down a row every month. Any help? I have included the macro I recorded called "chartupdater" below:

Sub ChartUpdater()
'
' ChartUpdater Macro
' Once the numbers get in, this is how to update the chart
'

'
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SetSourceData Source:=Range( _
"APPROVED!$A$3:$C$3,APPROVED!$A$132:$C$179")
End Sub

Your example code charts 48 rows
APPROVED!$A$132:$C$179

This code will chart the last used 48 rows on sheet Approved. Change the 48 to suit.

Sub ChartUpdater()
'
' ChartUpdater Macro
' Once the numbers get in, this is how to update the chart
'
    Const lSize As Long = 48
    
    With Sheets("Approved")
        With .Range("A" & Rows.Count).End(xlUp).Offset(-lSize + 1).Resize(lSize, 3)
            ActiveSheet.ChartObjects("Chart 1").Chart.SetSourceData _
                Source:=.Range("A3:C3", .Cells)
        End With
    End With
End Sub