Automatically populate cellls when date reached

mbtaichi

Board Regular
Joined
Jan 5, 2016
Messages
71
Hi, is there a way to automatically update a sheet when a date has been reached.
This would be in a money file tracking bank accounts.
so if I have a debit going out on the 1st of every month is there a way to automatically populate the next available row with the required data when the date has been reached ?
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
in A1 put this formula
=if(today()=B1,"date reached","")
where B1 is your date

then apply it to your own circumstances
 
Upvote 0
Try this:

In this script put the Date 1/1/2018 in Range("A1")
If todays date is less then 1/1/2018 then:

The value "Electric Bill" will be put in next empty cell in Column "A"
The value "102.36"will be put in Column "B"
And the date in Range("A1") will be advanced by one month.

These actions will happen any time you activate the sheet.

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Code:
Private Sub Worksheet_Activate()
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row + 1
If Cells(1, 1).Value < Date Then
Cells(Lastrow, 1).Value = "Electric Bill"
Cells(Lastrow, 2).Value = "102.36"
Cells(1, 1).Value = DateAdd("m", 1, Cells(1, 1).Value)
End If
End Sub

Now you may need to modify what you want to be entered in the sheet.
Let me know if you need more help. Give specifics what you want entered and where.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,700
Members
448,293
Latest member
jin kazuya

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
Back
Top