Enter date in cell

[pete4monc]

Hi guys
I have some vba that is button activated that looks in columns M and N and if there is a "Y" entered in a cell it copies the data in columns A,B,C etc to a spreadsheet with the same name as the column, then removes the "Y" and it all works fine.
But I'm stumbling, The below code inserts a date on the next available line when I need to put a date in column I in the active row as the corresponding "Y" before the vba deletes the "Y"?? (ie: if the "Y" is in cell "M5" it inserts the date in cell "I5" and not the next available row.
I hope the above makes sense?

Thanks guys

 Application.ScreenUpdating = False
    Dim lCol As Long, sAddr As String, fnd As Range, x As Long, ws As Worksheet
    lCol = ActiveSheet.Cells(4, Columns.Count).End(xlToLeft).Column
    x = ActiveCell.Row
    Set fnd = Rows(x).Find("y", LookIn:=xlValues, lookat:=xlWhole)
    If Not fnd Is Nothing Then
        sAddr = fnd.Address
        Do
            Set ws = Sheets(Cells(4, fnd.Column).Value)
            [B]Sheets("CustomerService").Range("I" & Rows.Count).End(xlUp).Offset(1).Value = Now[/B]
            With ws
                Range("A" & x & ":G" & x).Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
            End With
            Set fnd = Rows(x).FindNext(fnd)
        Loop While fnd.Address <> sAddr
        sAddr = ""
    End If
    Application.ScreenUpdating = True
    
    Range("K5:U999").Select
    Selection.ClearContents
    Range("B2").Select

 

[DanteAmor]

Try this

Sheets("CustomerService").Range("I" & f.Row).Value = Now

 

[pete4monc]

Try this

Sheets("CustomerService").Range("I" & f.Row).Value = Now

Hi DanteAmor. Thanks for your reply but unfortunately I get a runtime error "424"?

 

[pete4monc]

I have tried this code but it inserts the date in I2 every time and not in the I cell on the active row? Any ideas?

Sheets("CustomerService").Range("I" & (ActiveCell.Rows.Count).End(xlUp).Offset(1).Value = Now

 

[pete4monc]

Hi guys FYI
I have managed to sort it out I added this simple code into my original code.

Range("I" & (ActiveCell.Row)).Select
        ActiveCell.Value = Date

Thanks to everyone especially @DanteAmor

 

[DanteAmor]

I'm glad to know that it already works for you.