I keep getting "Subscript out of Range" on the move statement. I have tried
substituting "Sheet4" for the sheet name of "22085", I know this error
normally occurs when the sheet or file does not exist so it returns a value
of 0, but all exists.
Please help.
Thanks

Dim CJobs As Workbook
Dim OJobs As Workbook

Workbooks.Open ("c:\MikeClosedJobs.xls")
Set CJobs = Workbooks("MikeClosedJobs.xls")
Set OJobs = Workbooks("Mike.xls")
Worksheets("22085").Move After:=CJobs.Worksheets(CJobs.Worksheets.Count)
Workbooks("MikeClosedJobs.xls").Close


"South Bend Larry" wrote:

Thanks that was just what I was look for.
Larry

"Norman Jones" wrote:

Hi Larry,

That is slick, but it creates a "book 2" not add to an existing workbook.
Is there a way to slip a worksheet into an existing workbook. And what
would
be the command to remove a worksheet.
Thanks

A worksheet can be copied from one workbook to another. Alternatively, a
worksheet can be moved from one workbook to another.

The following demonstrates both processes:

Sub Tester01()
Dim WB1 As Workbook
Dim WB2 As Workbook

Set WB1 = Workbooks("MyBook1.xls")
Set WB2 = Workbooks("MyBook2.xls")


'Copy a sheet from WB1 to WB2
'-------------------------------

WB1.Sheets("Sheet2").Copy _
After:=WB2.Sheets(WB2.Sheets.Count)


'Move a sheet from WB1 to WB2
'--------------------------------
WB1.Sheets("Sheet3").Move _
After:=WB2.Sheets(WB2.Sheets.Count)
End Sub


---
Regards,
Norman



"South Bend Larry" wrote in
message ...
That is slick, but it creates a "book 2" not add to an existing workbook.
Is there a way to slip a worksheet into an existing workbook. And what
would
be the command to remove a worksheet.
Thanks

"Norman Jones" wrote:


Hi Larry,

The simple instruction:

Activesheet.Copy

produces a new single-sheet workbook, the single sheet being a copy of
the
active sheet.

At tis juncture, the new, single-sheet workbook is unsaved and is the
active
workbook.

Of course, instead of Activesheet, any sheet may be specified, e.g.:

Sheets("Sheet2").Copy



---
Regards,
Norman



"South Bend Larry" wrote in
message ...
I see questions which save an entire workbook, but I need to save a
worksheet, remove to an archive existing workbook. Each work book is a
job,
so this will create a history of jobs for each of our project managers.
Active jobs in one work book and completed jobs in another. Please
give
me
some vba guidence. Thanks
Larry