Need macro to execute when a date is put in a column

[sspatriots]

Hi,

I want to use the code below that moves a row of information to another worksheet when the word "Done" is put into this one column. I would like to use it to do the same thing, but instead of the word "Done", I want the code to execute when any date is put in that column.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("F:F")) Is Nothing Then Exit Sub
If Target = "Done" Then
Target.EntireRow.Copy Sheets(Range("A" & Target.Row).Value).Cells(Sheets(Range("A" & Target.Row).Value).Rows.Count, "A").End(xlUp).Offset(1, 0)
End If
End Sub



I've played with this for the last hour and googling the If Target statements, but nothing will work. This is not for the same spreadsheet my other post was for. Trying to optimize about 8 spreadsheets for this company I just started working at. Any assistance would be much appreciated.


Steve

 

[mumps]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("F:F")) Is Nothing Then Exit Sub
    If IsDate(Target) Then
        Target.EntireRow.Copy Sheets(Range("A" & Target.Row).Value).Cells(Sheets(Range("A" & Target.Row).Value).Rows.Count, "A").End(xlUp).Offset(1, 0)
    End If
End Sub

 

[sspatriots]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("F:F")) Is Nothing Then Exit Sub
    If IsDate(Target) Then
        Target.EntireRow.Copy Sheets(Range("A" & Target.Row).Value).Cells(Sheets(Range("A" & Target.Row).Value).Rows.Count, "A").End(xlUp).Offset(1, 0)
    End If
End Sub

Thank you!!!

That does exactly as I had hoped it would. Is there anything I can add to it to make the top row be selected after I make an entry? I added the 02/28/2022 date and it bounced up to row three and highlighted it.

 

[sspatriots]

Correction to my question. Should have read: Is there anything I can add to it to make the row near the top not be selected after I make an entry?

Is there anything I can add to it to make the top row be selected after I make an entry?

 

[mumps]

Which row do you want selected?

 

[sspatriots]

If I make a date entry in that column anywhere, I would like it to stay at that active cell where the date is placed.

 

[mumps]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("F:F")) Is Nothing Then Exit Sub
    If IsDate(Target) Then
        Target.EntireRow.Copy Sheets(Range("A" & Target.Row).Value).Cells(Sheets(Range("A" & Target.Row).Value).Rows.Count, "A").End(xlUp).Offset(1, 0)
        Target.Select
    End If
End Sub

 

[sspatriots]

That did it. Thanks and worked perfectly.

 

[mumps]

You are very welcome.