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).
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).
I'd appreciate any assistance or suggestions
Thanking you in advance.
TheShyButterfly
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).
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