Auto Populate Cells In A Table, Based On New Month

XcelXpert

New Member
Joined
Sep 17, 2020
Messages
9
Office Version
  1. 2013
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.

VBA Code:
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

Capture2.PNG

Capture3.PNG
 

Attachments

  • Capture2.PNG
    Capture2.PNG
    33.7 KB · Views: 3
  • Capture3.PNG
    Capture3.PNG
    54.7 KB · Views: 3

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
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

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,380
Messages
5,601,282
Members
414,440
Latest member
Kim0204

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top