copying to new workbook

[kardifflad]

thanks to other posters on this site i now have a great bit of code tha
moves rows to other sheets for me based on the critera in column A. Th
code is below but i now need to expan on it a little by havign it loo
for data that says "cleared" in column A and moving it to an entirel
new workbook in another folder. As if that isn't hard enough it onl
needs to copy across columns B, C and D, then delete the row it
copied.
So basically if the user types 'cleared' in column A of row 10, then th
code will open the workbook "cleared data" in folder "\\my path\cleare
data.xls"
it will copy across the details in cells B10, C10 and D10, close th
cleared data workbook, then delete the entire row 10 in the workbook
have open.
For most of you out there i guess this is fairly easy so i woul
appreciate any help you can give please. Heres my code:


'\\ Now find any Scottish them to their own sheets

Set sh2 = Sheets("Scotland")
finalrow = Cells(Rows.Count, 1).End(xlUp).Row

For i = finalrow To 1 Step -1
If UCase(Left(Cells(i, 1), 2)) = "SC" Then
Rows(i).Copy sh2.Cells(sh2.Rows.Count, 1).End(xlUp)(2)
Rows(i).Delete
End If
Next i

' ** here it would then look for "CL" or "Cleared" and copy B:
to new workbook.



thank you