copying to new workbook
 

thanks to other posters on this site i now have a great bit of code that moves rows to other sheets for me based on the critera in column A. The code is below but i now need to expan on it a little by havign it look for data that says "cleared" in column A and moving it to an entirely new workbook in another folder. As if that isn't hard enough it only needs to copy across columns B, C and D, then delete the row its copied.
So basically if the user types 'cleared' in column A of row 10, then the code will open the workbook "cleared data" in folder "\\my path\cleared data.xls"
it will copy across the details in cells B10, C10 and D10, close the cleared data workbook, then delete the entire row 10 in the workbook i have open.
For most of you out there i guess this is fairly easy so i would 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:D to new workbook.



thank you.