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

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

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

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

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

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