Fantastic Bernie, thanks!
--
Traa Dy Liooar
Jock
"Bernie Deitrick" wrote:
> 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
>
>
> "Jock" <(E-Mail Removed)> wrote in message
> news:51C336C2-3A57-47F1-AF14-(E-Mail Removed)...
> > 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
> >
> >
> > "Bernie Deitrick" wrote:
> >
> >> 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" <(E-Mail Removed)> wrote in message
> >> news:412FE6C3-E895-403B-A23A-(E-Mail Removed)...
> >> > 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,
> >> > --
> >> > Traa Dy Liooar
> >> >
> >> > Jock
> >>
> >>
> >>
>
>
>
|
Similar Threads
