Auto Populate Cells In A Table, Based On New Month

[XcelXpert]

Hi everyone!

I have a table, with Monthly Expenses. Each Month should have recurring Expenses, and I don't wish to have to type the information every Month. If there's a way to automatically import the data into the corresponding cells in my table, it would be greatly appreciated! I have included screenshots and the existing macro code that I am running. I'm not great with VBA, so I created a Recorded Macro. This only works with January, in cell A2, but I'd like to get it to run on the first occurrence of every Month.

Sub Recurring()
'
' Recurring Macro
'

'
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "January"
    Range("$J$11:$J$20").Select
    Selection.Copy
    Range("B2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("$L$11:$L$20").Select
    Selection.Copy
    Range("C2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("$N$11:$N$20").Select
    Selection.Copy
    Range("D2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("A2").Select
    Selection.Copy
    Range("A3:A11").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("A12").Select
End Sub

 

[onlyadrafter]

Hello,

not 100% sure of how you require this to work, but the following code (which needs to go into the relevant sheet code window, not a standard module, will copy the relevant columns when something is entered in cell A2

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    If Target.Address = "$A$2" Then
        MY_CELL = Target.Row
        Range("J11:J" & Range("J" & Rows.Count).End(xlUp).Row).Copy
        Cells(Target.Row, 2).PasteSpecial (xlPasteValues)
        Range("L11:L" & Range("L" & Rows.Count).End(xlUp).Row).Copy
        Cells(Target.Row, 3).PasteSpecial (xlPasteValues)
        Range("N11:N" & Range("N" & Rows.Count).End(xlUp).Row).Copy
        Cells(Target.Row, 4).PasteSpecial (xlPasteValues)
        Range(Target.Address).Copy
        Range("A" & Target.Row & ":A" & Range("B" & Rows.Count).End(xlUp).Row).PasteSpecial (xlPasteAll)
    End If
    Application.ScreenUpdating = True
    Application.CutCopyMode = False
    Application.EnableEvents = True
End Sub