Save Sheet and Remove from Workbook

South Bend Larry · 08-17-2005, 05:05 PM

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

Norman Jones · 08-17-2005, 05:05 PM

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" <[email protected]> wrote in
message news:[email protected]...
>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

South Bend Larry · 08-17-2005, 06:05 PM

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" <[email protected]> wrote in
> message news:[email protected]...
> >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
>
>
>

Norman Jones · 08-17-2005, 06:05 PM

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" <[email protected]> wrote in
message news:[email protected]...
> 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" <[email protected]> wrote in
>> message news:[email protected]...
>> >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
>>
>>
>>

South Bend Larry · 08-18-2005, 09:05 AM

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" <[email protected]> wrote in
> message news:[email protected]...
> > 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" <[email protected]> wrote in
> >> message news:[email protected]...
> >> >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
> >>
> >>
> >>
>
>
>

South Bend Larry · 08-18-2005, 11:05 AM

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" <[email protected]> wrote in
> > message news:[email protected]...
> > > 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" <[email protected]> wrote in
> > >> message news:[email protected]...
> > >> >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
> > >>
> > >>
> > >>
> >
> >
> >

Norman Jones · 08-18-2005, 12:05 PM

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" <[email protected]> wrote in
message news:[email protected]...
>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" <[email protected]> wrote in
>> > message news:[email protected]...
>> > > 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" <[email protected]> wrote
>> > >> in
>> > >> message news:[email protected]...
>> > >> >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
>> > >>
>> > >>
>> > >>
>> >
>> >
>> >

Norman Jones · 08-18-2005, 12:05 PM

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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in
> message news:[email protected]...
>>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" <[email protected]> wrote in
>>> > message news:[email protected]...
>>> > > 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" <[email protected]> wrote
>>> > >> in
>>> > >> message news:[email protected]...
>>> > >> >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
>>> > >>
>>> > >>
>>> > >>
>>> >
>>> >
>>> >
>
>

South Bend Larry · 08-18-2005, 01:05 PM

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" <[email protected]> wrote in message
> news:%[email protected]...
> > 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" <[email protected]> wrote in
> > message news:[email protected]...
> >>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" <[email protected]> wrote in
> >>> > message news:[email protected]...
> >>> > > 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" <[email protected]> wrote
> >>> > >> in
> >>> > >> message news:[email protected]...
> >>> > >> >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
> >>> > >>
> >>> > >>
> >>> > >>
> >>> >
> >>> >
> >>> >
> >
> >
>
>
>