Copying cells works but not on another sheet?

[Sdwd76]

So I have the below code working on another sheet where it will copy and paste where a word if found in a row. So if a word is on 4 rows - Then 4 rows data is copied.

So i have tried to use the same on other sheets but it only copies one row (The last one so not including other rows) I am unsure why this is? Any help would be grateful. I have checked modules names and sheet names are all different

Sub Removetwo()

Application.ScreenUpdating = False

Dim ts1 As Worksheet, ts2 As Worksheet
Set ts1 = Sheets("Remove")
Set ts2 = Sheets("Leave")
Dim lr, lr2, r

lr = ts1.Range("A" & Rows.Count).End(xlUp).Row

For r = 1 To lr


If ts1.Cells(r, "Y") = "Scrapped" Then
lr2 = ts2.Range("A" & Rows.Count).End(xlUp).Row
ts1.Cells(r, "E").Copy
ts2.Range("C" & lr2).Offset(1).PasteSpecial Paste:=xlPasteValues




End If

Next r




Application.ScreenUpdating = True

End Sub

 

[jasonb75]

I have checked modules names and sheet names are all different

If the names used are correct to the workbook then the only possible cause would be a mismatch in the data, possibly leading / trailing spaces in the cells with "Scrapped"

Also, note that If in vba is case sensitive, meaning that "Scrapped" and "scrapped" are different.

 

[Fluff]

You are using col A to determine the last used row on sheet Leave, but then pasting data into col C. Therefore your lr2 value will never change.

 

[jasonb75]

Good spot @Fluff, I missed that one.

That said, I did have to go over the code more than once to even find the lr2 definition. I'm gonna blame lack of formatting

 

[Sdwd76]

Thanks gents in my other one i was copying to A in this one to C so changed this line - "C"
lr2 = ts2.Range("C" & Rows.Count).End(xlUp).Row

works a dream thanks again

 

[Fluff]

Glad we could help & thanks for the feedback.