Autofill macro to autofill dates up to 31 days regardless of the month

Question

The task is to format the provided data into more helpful lists. The fact that this task will be done frequently each month is difficult. Therefore, when a macro is constructed, the essential dates should adjust to all months. It is not necessary for them to be specific for adaptation. That is to say, regardless of the month, all months can finish on the 31st day because we may then leave those cells unfilled. For instance, February 2023 finishes on the 28th day, but since a month can only have a maximum of 31 days, we want it to be the final day, thus the autofill will end on February 31, 2023. The 29th, 30th, and 31st can then be empty.

Another point is that in order to retrieve the required dates, we need to specify the month and year before running the macro. I considered using "01.mm.yyyy" as a manual entry, and the macro will utilise that cell as a reference for autofill. To avoid having to wait for a manual entry, I also want to know if we may insert the current date by pressing Ctrl+Shift+; (semi-colon). The issue with this is that, for instance, the current date may begin on the third day and the autofill will continue as the third, fourth, fifth, etc.

In a cell, I've tried entering "01.02.2023." Then I created a macro where I dragged it down and had it fill in every day of that month automatically. But as I said, it came to an end on the 28th day. I then continued to autofill till the 28th day (because it is the lowest number of days a month can have, which can be filled with autofill no problem). As for the 29th, 30th, and 31st, I tried using something like "29.mm(of the input cell).yyyy(of the input cell), "30.mm(of the input cell).yyyy(of the input cell), and "31.mm(of the input cell).yyyy(of the input cell)". But I was unable to make it work.

Answer 1

If you wish to utilise a single formula, have 2016+ with dynamic arrays, and are specifying the Month and Year to use, then just say:

A1: =TEXT(ROW(INDEX($A:$A,1):INDEX($A:$A,31)),"00\.") & TEXT(Mnth,"00\.") & Yr

With Yr = 2023 and Mnth = 2

To produce this using VBA, try this:

Option Explicit
Sub MonthDays()
    Dim Yr As Long, y As String
    Dim Mnth As Long, m As String
    Dim vDates(1 To 31, 1 To 1) As Variant
    Dim I As Long
    Dim WS As Worksheet, rDest As Range
    
 Yr = Application.InputBox(Prompt:="Enter the Year as 'yyyy'", Title:="Year Input", Default:=Year(Date), Type:=1)
 Mnth = Application.InputBox(Prompt:="Enter the Month as 'm'", Title:="Month Input", Type:=1)
 
 y = Format(Yr, "0000")
 m = Format(Mnth, "00\.")
 
 'Create array with the date strings
 For I = 1 To 31
    vDates(I, 1) = Format(I, "00\.") & m & y
 Next I
 
 'write to the worksheet
 'below example shows one method
 Set WS = ThisWorkbook.Worksheets("Sheet1")

With WS
    Set rDest = Range(.Cells(1, 1), .Cells(31, 1))
End With

With rDest
    .ClearContents
    .NumberFormat = "@"
    .Value = vDates
    .EntireColumn.AutoFit
End With

End Sub