Hi,

Thanks for the speedy response. It did work for me, however, I have now
been asked for something slightly different.

The job information is now to be held on one worksheet (instead of various
ones) and the aim is now to locate those jobs flagged with "Closed" in lets
say column J and moved to the Closed Jobs.xls. So I am looking for a row to
move not a column.

I should ask to go on a VB course!!!!

Thanks

"JBeaucaire" wrote:

With no info about the names of the active jobs book, or where this "closed"
flag is, you'll have to edit this down:

==================
Option Explicit

Sub ArchiveClosedJobs()
Dim ws As Worksheet

'Check if destination workbook is open already, open it if necessary
On Error Resume Next
Workbooks("Closed Jobs.xls").Activate
If Err < 0 Then Workbooks.Open "Closed Jobs.xls"
Workbooks("Open Jobs.xls").Activate

For Each ws In Worksheets
ws.Activate
If Range("J2").Value = "Closed" Then
MsgBox "J2 = closed"
ActiveSheet.Move After:=Workbooks("Closed
Jobs.xls").Sheets(Worksheets.Count)
Workbooks("Open Jobs.xls").Activate
End If
Next ws

End Sub
=================
This will go through ALL the worksheets in the workbook and check J2 for a
"closed" status, the ones that have that will be moved to the other workbook.

Let me know if that does the job for you.


--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Mustang" wrote:

I would like to achieve the following with a macro but not sure how to....

I have a multi sheet workbook which contains information on open jobs. I
have a status column and once this status has turned to 'Closed' I would like
to move this sheet to another workbook named Closed Jobs.

Any ideas would be gratefully received.

Many thanks