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

Hi Larry,

I qualified the line:

Worksheets("22085").Move
After:=CJobs.Worksheets(CJobs.Worksheets.Count)

with its parent workbook:

OJobs.Worksheets("Sheet3").Move _
After:=CJobs.Worksheets(CJobs.Worksheets.Count)

Then, providing that the 22085 sheet exists in the Mike.xls workbook, the
code ran without problem for me.

If you were, however, to run the code again, it would raise a 1004 run-time
error, because the 22085 sheet would have been deleted from Mike.xls on the
first run of the code.

If it was your intention to copy the 22085 sheet between workbooks and not
to delete it from Mike.xls, then post back.

Incidentally, your code lines:

Workbooks.Open ("c:\MikeClosedJobs.xls")
Set CJobs = Workbooks("MikeClosedJobs.xls")

can be rendered in the single line:

Set CJobs = Workbooks.Open("c:\MikeClosedJobs.xls")

Additionally, the line:

Workbooks("MikeClosedJobs.xls").Close

can be expressed as:

CJobs.Close

Finally, to avoid the "Do you want to save changes..." type prompt, I
changed this line to:

CJobs.Close saveChanges = True


With the above changes, the code becomes:

Sub TestB01()

Dim CJobs As Workbook
Dim OJobs As Workbook

Set CJobs = Workbooks.Open("c:\MikeClosedJobs.xls")

Set OJobs = Workbooks("Mike.xls")

OJobs.Worksheets("Sheet3").Move _
After:=CJobs.Worksheets(CJobs.Worksheets.Count)

CJobs.Close saveChanges = True

End Sub


---
Regards,
Norman



"South Bend Larry" wrote in
message ...
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

Hi Larry,

: OJobs.Worksheets("Sheet3").Move _
After:=CJobs.Worksheets(CJobs.Worksheets.Count)

Should read:

OJobs.Worksheets("22085").Move _
After:=CJobs.Worksheets(CJobs.Worksheets.Count)

(Sheet3 was my test sheet and I omitted changing this to your 22085 sheet
name)

Similarly, the revised code should read:

Sub TestB01()

Dim CJobs As Workbook
Dim OJobs As Workbook

Set CJobs = Workbooks.Open("c:\MikeClosedJobs.xls")

Set OJobs = Workbooks("Mike.xls")

OJobs.Worksheets("22085").Move _
After:=CJobs.Worksheets(CJobs.Worksheets.Count)

CJobs.Close saveChanges = True

End Sub


---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Larry,

I qualified the line:

Worksheets("22085").Move
After:=CJobs.Worksheets(CJobs.Worksheets.Count)

with its parent workbook:

OJobs.Worksheets("Sheet3").Move _
After:=CJobs.Worksheets(CJobs.Worksheets.Count)

Then, providing that the 22085 sheet exists in the Mike.xls workbook, the
code ran without problem for me.

If you were, however, to run the code again, it would raise a 1004
run-time error, because the 22085 sheet would have been deleted from
Mike.xls on the first run of the code.

If it was your intention to copy the 22085 sheet between workbooks and not
to delete it from Mike.xls, then post back.

Incidentally, your code lines:

Workbooks.Open ("c:\MikeClosedJobs.xls")
Set CJobs = Workbooks("MikeClosedJobs.xls")

can be rendered in the single line:

Set CJobs = Workbooks.Open("c:\MikeClosedJobs.xls")

Additionally, the line:

Workbooks("MikeClosedJobs.xls").Close

can be expressed as:

CJobs.Close

Finally, to avoid the "Do you want to save changes..." type prompt, I
changed this line to:

CJobs.Close saveChanges = True


With the above changes, the code becomes:

Sub TestB01()

Dim CJobs As Workbook
Dim OJobs As Workbook

Set CJobs = Workbooks.Open("c:\MikeClosedJobs.xls")

Set OJobs = Workbooks("Mike.xls")

OJobs.Worksheets("Sheet3").Move _
After:=CJobs.Worksheets(CJobs.Worksheets.Count)

CJobs.Close saveChanges = True

End Sub


---
Regards,
Norman



"South Bend Larry" wrote in
message ...
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

That worked great!!! thanks. and thanks for the heads up on cleaning up my
code. Sure do appreciate it.
Thanks again
Larry

"Norman Jones" wrote:

Hi Larry,

: OJobs.Worksheets("Sheet3").Move _
After:=CJobs.Worksheets(CJobs.Worksheets.Count)

Should read:

OJobs.Worksheets("22085").Move _
After:=CJobs.Worksheets(CJobs.Worksheets.Count)

(Sheet3 was my test sheet and I omitted changing this to your 22085 sheet
name)

Similarly, the revised code should read:

Sub TestB01()

Dim CJobs As Workbook
Dim OJobs As Workbook

Set CJobs = Workbooks.Open("c:\MikeClosedJobs.xls")

Set OJobs = Workbooks("Mike.xls")

OJobs.Worksheets("22085").Move _
After:=CJobs.Worksheets(CJobs.Worksheets.Count)

CJobs.Close saveChanges = True

End Sub


---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Larry,

I qualified the line:

Worksheets("22085").Move
After:=CJobs.Worksheets(CJobs.Worksheets.Count)

with its parent workbook:

OJobs.Worksheets("Sheet3").Move _
After:=CJobs.Worksheets(CJobs.Worksheets.Count)

Then, providing that the 22085 sheet exists in the Mike.xls workbook, the
code ran without problem for me.

If you were, however, to run the code again, it would raise a 1004
run-time error, because the 22085 sheet would have been deleted from
Mike.xls on the first run of the code.

If it was your intention to copy the 22085 sheet between workbooks and not
to delete it from Mike.xls, then post back.

Incidentally, your code lines:

Workbooks.Open ("c:\MikeClosedJobs.xls")
Set CJobs = Workbooks("MikeClosedJobs.xls")

can be rendered in the single line:

Set CJobs = Workbooks.Open("c:\MikeClosedJobs.xls")

Additionally, the line:

Workbooks("MikeClosedJobs.xls").Close

can be expressed as:

CJobs.Close

Finally, to avoid the "Do you want to save changes..." type prompt, I
changed this line to:

CJobs.Close saveChanges = True


With the above changes, the code becomes:

Sub TestB01()

Dim CJobs As Workbook
Dim OJobs As Workbook

Set CJobs = Workbooks.Open("c:\MikeClosedJobs.xls")

Set OJobs = Workbooks("Mike.xls")

OJobs.Worksheets("Sheet3").Move _
After:=CJobs.Worksheets(CJobs.Worksheets.Count)

CJobs.Close saveChanges = True

End Sub


---
Regards,
Norman



"South Bend Larry" wrote in
message ...
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