如何在Excel VBA中自动更新日期?

5lhxktic  于 2022-12-14  发布在  其他
关注(0)|答案(2)|浏览(441)

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.
When I click on the button, cell A2 needs to be filled with the first date of the month and then this document should be printed.
Once this document is printed cell A2 needs to be updated with the next date and the document again needs to be printed with the updated date.
This continues until the month of December.

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
2skhul33

2skhul331#

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
au9on6nz

au9on6nz2#

您可以使用工作表函数EOMONTH来计算上个月的最后一天。然后将日期加1,使其提前到本月的开始。
下面详细介绍了两个选项。
1.按天循环当前月份
1.按天循环到任意日期
选项2已注解掉。若要使用它,请注解掉选项1并取消注解选项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

相关问题