How to Auto Update date in excel VBA?

Melissa Dmello 0
Viewed 36

I am new to VBA and I would really appreciate help with the following:

  1. On button click I want documents to be printed each with a new date for all months in Cell A2.

The code below only updates the date once and the first date is the current date. (I need the first document to be printed not with the current date but the first date of the month.)

So when the first document is printed cell A2 has the first date of the month and the last document has the last date of the month printed.

Thanks in advance!

Private Sub CommandButton1_Click()

Range("A2").Value = Format(Date, "dd MMMM yy")

ActiveWindow.SelectedSheets.PrintOut copies:=1, collate:=True

Range("A2").Value = Range("A2").Value + 1

ActiveWindow.SelectedSheets.PrintOut copies:=1, collate:=True

End Sub
vba excel
2 Answers

Please, try the next updated code. It firstly print the sheet with first day of the current month and second time with the last:

Private Sub CommandButton1_Click()
 Dim firstD As Date, lastD As Date
 
 firstD = DateSerial(Year(Date), Month(Date), 1)
 lastD = WorksheetFunction.EoMonth(Date, 0)

 Range("A2").Value = Format(firstD, "dd MMMM yy")
 ActiveWindow.SelectedSheets.PrintOut copies:=1, collate:=True

 Range("A2").Value = Format(lastD, "dd MMMM yy")
 ActiveWindow.SelectedSheets.PrintOut copies:=1, collate:=True
End Sub

Is this what you want/need?

Edited:

Please, test the next version which should do what (I understood) you need:

Sub CommandButton1_Click()
   Dim d As Date, curYear As Long
   
   d = DateSerial(Year(Date), Month(Date), 1) 'initial day
   curYear  = Year(d)

   Do While Year(d) = curYear And Month(d) <= 12 And Day(d) <= 31
        Range("A2").Value = Format(d, "dd MMMM yy")
        
        ActiveWindow.SelectedSheets.PrintOut copies:=1, collate:=True
        
        d = d + 1
    Loop
End Sub
FaneDuru0

You can use the worksheet function EOMONTH to calculate the last day of the previous month. Then add 1 to the date to advance it to the start of the current month.

There are two options detailed below.

  1. Loop through the current month by day
  2. Loop through to an arbitrary date by day

Option 2 is commented out. To use it comment out Option 1 and uncomment Option 2.

Private Sub CommandButton1_Click()

Dim StartOfMonth As Date
Dim DateStep As Long
Dim DayCounter As Long

    StartOfMonth = WorksheetFunction.EoMonth(Date, -1)
'Calculate the last date of the prior month

    DateStep = Day(WorksheetFunction.EoMonth(Date, 0))
'Calculate the last day of the current month (Option 1)

'   DateStep = DateSerial(2022, 12, 31) - StartOfMonth
'Alternate for a longer date range instead of the current month (Option 2)

    For DayCounter = 1 To DateStep
    
        ActiveSheet.Range("A2").Value = Format(StartOfMonth + DayCounter, "dd MMMM yy")
    'Insert the date into a cell
    
        ActiveWindow.SelectedSheets.PrintOut copies:=1, collate:=True
    
    Next

End Sub
Robert Mearns0
Related