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
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