EXCEL VBA - Auto populate range with current month's dates

[TheShyButterfly]

Hi and thank you for checking this out.

I have a Excel Macro-enabled template (.xltm) and each month a new workbook is generated from it.

My objective is to have the dates of the month automatically populated on a separate sheet ("Patrols Per Day").

If the user enters the date in the "Daily Log Sheet", D2, that date is linked to another sheet "Patrols Per Day" in A2.

I'd only need the code run the first time the user enters the date in the Daily Log Sheet (D2), once the dates of the month have been entered in, there is no need to have the code run again.

What I would like is when the user has entered the date in the "Daily Log Sheet", D2, VBA will automatically fill down column A of the "Patrols Per Day" sheet, starting from A2 (A2-A32) with the dates of the current month (see screen shot of the two worksheet (same workbook).

I have tried to modify the code from Macro for Monthly Auto-Fill? however I get a Run-Time Error 13 Type mismatch on row "startRng = "A2" ' Start cell"

I placed the code in a module, but should it be in the ThisWorkbook?

For referencing Patrols Per Day, I use the worksheet codename (so that if someone changes the tab name on the sheet it won't affect the code).

Option Explicit

Sub fillDates()

Dim ws As Worksheet
Dim startRng As Long
Dim endRng As Long

Set ws = cnPatrols_Per_Day
startRng = "A2" ' Start cell
endRng = "A32" ' End cell

    With Range(startRng & endRng)
        .AutoFill Destination:=ws.Range(startRng & endRng), Type:=xlFillDays
   
    End With
   
End Sub

I'd appreciate any assistance or suggestions

Thanking you in advance.

TheShyButterfly

 

[Norie]

There should be a semicolon between startRng and endRng

 

[Rick Rothstein]

Besides what Norie has said, you also need to change your declarations for startRng and endRng from Long to String.

 

[TheShyButterfly]

There should be a semicolon between startRng and endRng

Thank you for pointing that out Couldn't see the trees for the forest

 

[Norie]

Try this.

1 Remove any formulas you have in A2:A32 on the 'Patrols Per Day'.

2 Right click the 'Daily Log Sheet' tab and select View Code.

3 Paste the code below in the module that has opened.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lngNoDays As Long

    If Target.Address(0, 0) = "D2" And Sheets("Patrols Per Day").Range("A2").Value = "" Then
    
        lngNoDays = DateSerial(Year(Target.Value), Month(Target.Value) + 1, 1) - DateSerial(Year(Target.Value), Month(Target.Value), 1)
        With Sheets("Patrols Per Day")
            .Range("A2").Value = DateSerial(Year(Target.Value), Month(Target.Value), 1)
            .Range("A2").AutoFill Destination:=.Range("A2").Resize(lngNoDays), Type:=xlFillDays
        End With
    End If
    
End Sub

 

[TheShyButterfly]

Besides what Norie has said, you also need to change your declarations for startRng and endRng from Long to String.

Thank you … I'm only an amateur at best and wasn't too sure what to declare it as. Thank you very much.

 

[TheShyButterfly]

Try this.

1 Remove any formulas you have in A2:A32 on the 'Patrols Per Day'.

2 Right click the 'Daily Log Sheet' tab and select View Code.

3 Paste the code below in the module that has opened.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lngNoDays As Long

    If Target.Address(0, 0) = "D2" And Sheets("Patrols Per Day").Range("A2").Value = "" Then
  
        lngNoDays = DateSerial(Year(Target.Value), Month(Target.Value) + 1, 1) - DateSerial(Year(Target.Value), Month(Target.Value), 1)
        With Sheets("Patrols Per Day")
            .Range("A2").Value = DateSerial(Year(Target.Value), Month(Target.Value), 1)[/B][/COLOR][/I]
            .Range("A2").AutoFill Destination:=.Range("A2").Resize(lngNoDays), Type:=xlFillDays
        End With
    End If
  
End Sub

Wow, thank you … works like a dream!!! … the only issue I came across as I was testing it, as a User would normally do …
When I clear Column A on the Patrols Per Day, and I clear the date in D2 of the Daily Log Sheet, I get a Run-time error '1004' Application-defined or object-defined error .. when I debug, it takes me to the line:

.Range("A2").Value = DateSerial(Year(Target.Value), Month(Target.Value), 1)

I was just trying to re-enact the User entering in the date, but then realising that it was wrong and deleted it and re-entered it, as it could be likely to happen.
Would I need a error handler? if so, you be kind enough show me where it would go? (I'm not that familiar with the error handling yet).

With much gratitude
TheShyButterfly

 

[Norie]

Try this.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lngNoDays As Long

    If Target.Address(0, 0) = "D2" And Sheets("Patrols Per Day").Range("A2").Value = "" Then
    
        If IsDate(Target.Value) Then
            lngNoDays = DateSerial(Year(Target.Value), Month(Target.Value) + 1, 1) - DateSerial(Year(Target.Value), Month(Target.Value), 1)
            With Sheets("Patrols Per Day")
                .Range("A2").Value = DateSerial(Year(Target.Value), Month(Target.Value), 1)
                .Range("A2").AutoFill Destination:=.Range("A2").Resize(lngNoDays), Type:=xlFillDays
            End With
        End If
    End If
    
End Sub

 

[TheShyButterfly]

Try this.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lngNoDays As Long

    If Target.Address(0, 0) = "D2" And Sheets("Patrols Per Day").Range("A2").Value = "" Then
   
        If IsDate(Target.Value) Then
            lngNoDays = DateSerial(Year(Target.Value), Month(Target.Value) + 1, 1) - DateSerial(Year(Target.Value), Month(Target.Value), 1)
            With Sheets("Patrols Per Day")
                .Range("A2").Value = DateSerial(Year(Target.Value), Month(Target.Value), 1)
                .Range("A2").AutoFill Destination:=.Range("A2").Resize(lngNoDays), Type:=xlFillDays
            End With
        End If
    End If
   
End Sub

Excellent !!! Thank you so much LOL … it's unbreakable (just what I need) perfecto!
Thank you for responding so quickly … this is the best forum, thanks to all those that have helped me.
Have a wonderful weekend
TheShyButterfly

 

[lmartins]

How can we update the last column with dates already filled ?

For example, you run one time putting the date "01/01/2021", and it fills all cells until 31/01/2021, but if we change the cell that have the initial value "01/01/2021" to "01/08/2021", it doesn't update the cells from 01/08/2021 to 31/08/2021