Copy a row to a new sheet based on cell

[PatrickMoose]

SO here is the question and please understand I am not great with excel but do know how to copy paste and a code lol.

Sheet3 has all my data, i would like any row from sheet3 to copy itself and paste itself on sheet1 if the "W" column says "Late". here are the details

Sheet3 starts from A3 to W3 and goes down to A1400 to W1400

Sheet1 starts A3

I also would like the cells in sheet3 to not be deleted when copied and moved.

Any help would be much appreciated

 

[DanteAmor]

Welcome to the MrExcel Message Board!

Try this:

Sub copyRows()
  With Sheets("Sheet3")
    .Range("A2:W2").AutoFilter 23, "Late", xlFilterValues
    .AutoFilter.Range.Offset(1).EntireRow.Copy Sheets("Sheet1").Range("A3")
    .ShowAllData
  End With
End Sub

 

[PatrickMoose]

Thank you for taking time to send this but unfortunately when I pasted the code in, I went back to sheet3 and put "late" into W2 and although the screen flashed, nothing went to sheet1.

 

[DanteAmor]

Sheet3 starts from A3 to W3 ...

I went back to sheet3 and put "late" into W2

You must put "late" starting in W3

 

[PatrickMoose]

See image, i tried w3 and w4 but still no luck, I am definitely doing something wrong lol.

 

[DanteAmor]

Try this:

Sub copyRows()
  Dim lr As Long
  With Sheets("Sheet3")
    lr = .UsedRange.Rows(.UsedRange.Rows.Count).Row
    .Range("A2:W" & lr).AutoFilter 23, "Late", xlFilterValues
    .AutoFilter.Range.Offset(1).EntireRow.Copy Sheets("Sheet1").Range("A3")
    .ShowAllData
  End With
End Sub