View Full Version : Solved: Inserting a Specific Date

01-30-2012, 02:49 PM
I have worksheet runs from ThisWorkbook which is triggered based on whether the sheet if opened on the last day of the month. If the sheet is opened and it is indeed the last day of the month, the script automatically inserts five new rows in the designated worksheet and inserts the current date in Column A for each of the five new rows.

I am attempting to create a snippet of code that I can run manually to accomplish the same thing in the event I happen not to open the sheet until after the end of the month has passed. I have everything working but I am drawing a blank on how to manually force the date in Column A to be the last day of the previous month. It is currently inserting the current date. What is the most simple way, using VBA, to change these dates to the last day of the previous month?

Jacob Hilderbrand
01-30-2012, 03:45 PM
Range("A1").Value = DateSerial(Year(Date), Month(Date), 0)

0 for the day argument will make it go back 1 day, to the last day of the previous month.

01-30-2012, 03:46 PM
Thanks! I appreciate the help.