Copy/Paste if cell equals a word

[steven426]

Hi:

I am trying to figure out how to go down a list of words and copy/paste a row if it equals a specific ring of words. For example, if want to go down a column and copy the word "Dog" if the word is "Dog" and paste it to the second sheet in a certain column... The thing that I can't figure out is how to step down to the next row until the cell is empty...

I tried the code below, which is nowhere close to working... thanks

Sub Cats_and_Dogs()
For i = 1 To 100
If Range("i, 1") = "Dog" Then
Range("i,1").Select
Selection.Copy
Sheet2.Paste
End If
Next
End Sub

 

[dave3009]

Hi Steven

Welcome to the Board

Sub Cats_and_Dogs()
Dim i, j As Long
For i = 1 To 100
j = Sheet2.Range("A" & Rows.Count).End(xlUp).Row + 1
If Sheet1.Cells(i, 1).Value = "Dog" Then
Sheet1.Cells(i, 1).Copy Sheet2.Range("A" & j)
End If
Next
End Sub

 

[TheNoocH]

Welcome to the board...

best way is to find the last used row ...

eg

Sub test()
For Each cell In Range(Cells(1, "A"), Cells(Cells(Rows.Count, "A").End(xlUp).Row, "A"))
    If cell.Value = "Dog" Then cell.Copy Destination:=Sheet2.Cells(Sheet2.Cells(Rows.Count, "A").End(xlUp).Row + 1, "A")
Next cell
End Sub

 

[steven426]

Thanks a lot guys... This really helps... also, since my sheets have difference names than the ones in the example... do I need to define the sheet names somehow at the start of the code?

Thanks

 

[TheNoocH]

you could try something like this...then you just have to change Sheet1 and Sheet2 to the actual sheet names you are using

Sub test()
set cpySht=Sheets("Sheet1")
set pstSht=Sheets("Sheet2")
For Each cell In cpySht.Range(cpySht.Cells(1, "A"), cpySht.Cells(cpySht.Cells(Rows.Count, "A").End(xlUp).Row, "A"))
    If cell.Value = "Dog" Then cell.Copy Destination:=pstSht.Cells(pstSht.Cells(Rows.Count, "A").End(xlUp).Row + 1, "A")
Next cell
End Sub

 

[steven426]

To clarify, is cpySht the actual name of the sheet (what I would see on the tab in the lower left corner of the screen) or is Sheet1 what would be written on the tab?

Thanks.

 

[TheNoocH]

cpySht is the variable that holds the name of the sheet....the actual sheet name in the example above is Sheet1...

 

[steven426]

OK, so I've constructed this code from your posts, but it does not seem to be working.

Note, the sheet i am copying from is called Master Sheet and I am pasting to AGCY

 

[steven426]

Sub Cats_and_Dogs()
Set cpySht = Sheets("Master List")
Set pstSht = Sheets("AGCY")
Dim i, j As Long
For i = 1 To 200
j = pstSht.Range("K" & Rows.Count).End(xlUp).Row + 1
If cpySht.Cells(i, 1).Value = "Dog" Then
cpySht.Cells(i, 1).Copy pstSht.Range("A" & j)
End If
Next
End Sub

 

[TheNoocH]

OK, so I've constructed this code from your posts, but it does not seem to be working.

Note, the sheet i am copying from is called Master Sheet and I am pasting to AGCY

where are you pasting the code?

To install code:
Press ALT-F11, Insert, Module
Paste the code from Sub ... End Sub into the blank area