Hello,

I think that the reason you have to run it repeatedly is that when the row is deleted, the macro skips a row. Try this adjustment, where the cells are searched from the bottom up. That way, as rows are deleted, it won't skip any rows:

For i = finalrow to 1 Step -1

If ucase(left(Cells(i, 1),4)) = "SCOT" Then
rows(i).Copy sh2.Cells(sh2.rows.count, 1).end(xlup)(2)
rows(i).Delete
end if
next i