Autofill dates when 1 is entered in a cell

[Jock]

If a 1 is entered in any of C7 - C13, I would like to autofill sequential
numbers down column C up as far as the number 31. It's for a monthly sheet
with days of the week in column B. So regardless of which day is the 1st, the
code will copy the numbers down for the user.

Thanks,

 

[Bernie Deitrick]

Jock,

Copy the code below into the codemodule of the ThisWorkbook object.

If you didn't understand that line, then follow the advice from David McRitchie:
"Unlike standard macros which are installed in standard modules, Workbook Events are installed in
ThisWorkBook in the following manner: F11 (Visual Basic Editor), Get into your project library
(name of your workbook) with Ctrl+R (View, Project Explorer), under the name of your workbook you
see Microsoft Excel Objects, then before Modules you see ThisWorkBook, doubleclick and paste the
code into the code window (F7)."


HTH,
Bernie
MS Excel MVP


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Intersect(Target, Sh.Range("C7:C13")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
If Target.Value <> 1 Then Exit Sub
Application.EnableEvents = False
Target.AutoFill Destination:=Target.Resize( _
Day(DateSerial(Year(Now), Month(Now) + 1, 0)), 1), _
Type:=xlFillSeries
Application.EnableEvents = True
End Sub

 

[Jock]

Great Bernie, thanks. Works better than expected.

Should a user enter the '1' in the wrong cell then proceed to enter it in
the correct cell, can your code be 'tweaked' to clear down column C7:C43
before autofilling from the new '1' thus removing numbers which would
otherwise be outside the autofill range and would not be overwritten?
--
Traa Dy Liooar

Jock

Jock,

Copy the code below into the codemodule of the ThisWorkbook object.

If you didn't understand that line, then follow the advice from David McRitchie:
"Unlike standard macros which are installed in standard modules, Workbook Events are installed in
ThisWorkBook in the following manner: F11 (Visual Basic Editor), Get into your project library
(name of your workbook) with Ctrl+R (View, Project Explorer), under the name of your workbook you
see Microsoft Excel Objects, then before Modules you see ThisWorkBook, doubleclick and paste the
code into the code window (F7)."


HTH,
Bernie
MS Excel MVP


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Intersect(Target, Sh.Range("C7:C13")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
If Target.Value <> 1 Then Exit Sub
Application.EnableEvents = False
Target.AutoFill Destination:=Target.Resize( _
Day(DateSerial(Year(Now), Month(Now) + 1, 0)), 1), _
Type:=xlFillSeries
Application.EnableEvents = True
End Sub

 

[Bernie Deitrick]

Jock,

Try this version...

HTH,
Bernie
MS Excel MVP


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Intersect(Target, Sh.Range("C7:C13")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
If Target.Value <> 1 Then Exit Sub
Application.EnableEvents = False
'Clear old stuff below
Target.Offset(1).Resize(43 - Target.Row).ClearContents
'Clear old stuff above
If Target.Row <> 7 Then
Target.Offset(7 - Target.Row).Resize(Target.Row - 7).ClearContents
End If
Target.AutoFill Destination:=Target.Resize( _
Day(DateSerial(Year(Now), Month(Now) + 1, 0)), 1), _
Type:=xlFillSeries
Application.EnableEvents = True
End Sub

Great Bernie, thanks. Works better than expected.

Should a user enter the '1' in the wrong cell then proceed to enter it in
the correct cell, can your code be 'tweaked' to clear down column C7:C43
before autofilling from the new '1' thus removing numbers which would
otherwise be outside the autofill range and would not be overwritten?

 

[Jock]

Fantastic Bernie, thanks!