Help with Copying a row if a value is in a column

[Aditi208]

Hello,

I am trying to make my excel sheet copy an entire row of data to a new sheet if the column contains a certain criteria. Specifically:

on the worksheets named: All results 1882-1900, All results 1900-1950, and All results 1950-2000

If Column F contains W then copy the whole row to a sheet named Wins

I have tried altering the code on several tutorials of the exact same thing and for the life of me I can't get it to work. Can anyone tell me how to properly write this code and put it in the excel sheet so it works?

Thanks!

 

[Michael M]

Try this ... UNTESTED

Sub MM1()
Dim lr As Long, r As Long, ws As Worksheet, lr2 As Long
lr2 = Sheets("Wins").Cells(Rows.Count, "A").End(xlUp).Row
For Each ws In Worksheets
If ws.Name = "All results 1882-1900" Or ws.Name = "All results 1900-1950" Or ws.Name = "All results 1950-2000" Then
    ws.Activate
    lr = ws.Cells(Rows.Count, "F").End(xlUp).Row
        For r = 1 To lr
            If ws.Range("F" & r).Value = "W" Then
                Rows(r).Copy Destination:=Sheets("Wins").Range("A" & lr2 + 1)
               lr2 = Sheets("Wins").Cells(Rows.Count, "A").End(xlUp).Row
            End If
        Next r
End If
Next ws
End Sub

 

[dermie_72]

you could do it a number of ways.
1. use autofilters to copy the ones across
excel - Autofilter Macro, then copy visible data ONLY and paste to next available row - Stack Overflow
2. you could go down each cell in the column you are looking at, and if the value = "W" copy.entirerow destination:=target

I suggest the filter option as it should be quicker, but it's up to you.