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

TheShyButterfly

New Member
Joined
Nov 6, 2011
Messages
43
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).

Sample of 2 sheets of same workbook.JPG


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).

VBA 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
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,919
Office Version
  1. 365
Platform
  1. Windows
There should be a semicolon between startRng and endRng
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,420
Office Version
  1. 2010
Platform
  1. Windows
Besides what Norie has said, you also need to change your declarations for startRng and endRng from Long to String.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,919
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
VBA Code:
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

New Member
Joined
Nov 6, 2011
Messages
43

ADVERTISEMENT

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.
VBA Code:
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:
VBA Code:
.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

Well-known Member
Joined
Apr 28, 2004
Messages
75,919
Office Version
  1. 365
Platform
  1. Windows
Try this.
VBA Code:
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

New Member
Joined
Nov 6, 2011
Messages
43
Try this.
VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,139
Messages
5,546,177
Members
410,731
Latest member
keobongmacao
Top