Hi Jim,

I'm assuming you understand that the best you can do is save a workbook
that contains only a single worksheet. There's no concept of saving a
worksheet object outside a workbook in modern versions of Excel. Given that,
the easiest way to get what you want is to copy the sheet that you want to
save out to a new workbook, then save that workbook. It can be as simple as
the following three lines of code depending on your situation:

Sheet1.Copy
ActiveWorkbook.SaveAs "E:\MySheet.xls"
ActiveWorkbook.Close False

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Jim Carlock" wrote in message
...
I'm looking for a way to save one worksheet out of a workbook
of two sheets... I don't want the VB modules saved, nor any VB
code, forms, etc.

The Worksheet.SaveAs command doesn't seem to work even
though the help file indicates that it's to save a worksheet (and
I'm strictly keeping a worksheet defined as 1 and only 1
worksheet - rather than the whole work book).

I've thought about creating the worksheet as a seperate file but
before I continue in that direction, I'll ask here.

I've either missed something completely, and saving one and only
one worksheet out of a workbook is a little more complicated
than I've realized.

I'm leaning into seperating the code sheet from the data sheet
right at the moment.

Any advice is appreciated. Thank you.

--
Jim Carlock
http://www.microcosmotalk.com
Feel free to post back to the newsgroup!

Hi Rob,

Well I was thinking about some of the options I'd have...

I currently have a workbook with two sheets in it. One sheet
contains the code that holds/displays the combo boxes and
presents the buttons to commit the information to the other
sheet.

The combo boxes are filled with information that is read
from an Access database. Once a vendor is selected,
information is placed into some variables awaiting to be
moved to a destination once the Commit button is pressed.

The spreadsheet that is filled in is used as a form for
creating purchase orders.

The end result is that the form is saved as a Purchase Order
and printed out. They want the actual files saved and
sometimes 10 or 15 Purchase Orders get stacked into
one workbook (file). They're rebuilding factories.

So I'm thinking that I'd use one template per se, to start
each PO off.

The sheet immediately gets either a new file name or gets
placed into an existing workbook of sheets.

Thus I was trying to save the sheet, as I don't want the
code for the form to be saved. I just want the information
for the particular sheet saved.

You've displayed Sheet1.Copy... I'll check that out as it
sounds like a clipboard operation and I can create a new
sheet in an existing file or create a new sheet in a new file.

But that brings up another question...

I want to put an AutoSave into the worksheet, but I don't
want to use the old outdated Timer... somewhere I spotted
something on Microsoft's site today that might help me with
using an API to time things for this. I was originally thinking
about using a Timer1 control from VB but they don't have
VB installed on their machines... I think I need one of VB's
runtime files to get and use the timer control. But I did find
some things on Microsoft's site to use some API calls to
start a timer and raise events.

Word has an Autosave but Excel doesn't seem to have one.

Thanks for your help.

Jim Carlock
http://www.microcosmotalk.com
Feel free to post back to the newsgroup!


"Rob Bovey" wrote in message
...
Hi Jim,

I'm assuming you understand that the best you can do is save
a workbook that contains only a single worksheet. There's no
concept of saving a worksheet object outside a workbook
in modern versions of Excel. Given that, the easiest way to
get what you want is to copy the sheet that you want to save
out to a new workbook, then save that workbook. It can be
as simple as the following three lines of code depending on
your situation:

Sheet1.Copy
ActiveWorkbook.SaveAs "E:\MySheet.xls"
ActiveWorkbook.Close False

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Jim Carlock" wrote in message
...
I'm looking for a way to save one worksheet out of a workbook
of two sheets... I don't want the VB modules saved, nor any VB
code, forms, etc.

The Worksheet.SaveAs command doesn't seem to work even
though the help file indicates that it's to save a worksheet (and
I'm strictly keeping a worksheet defined as 1 and only 1
worksheet - rather than the whole work book).

I've thought about creating the worksheet as a seperate file but
before I continue in that direction, I'll ask here.

I've either missed something completely, and saving one and only
one worksheet out of a workbook is a little more complicated
than I've realized.

I'm leaning into seperating the code sheet from the data sheet
right at the moment.

Any advice is appreciated. Thank you.

--
Jim Carlock
http://www.microcosmotalk.com
Feel free to post back to the newsgroup!

Hi Jim,

I've implemented quite a few solutions that use the Application.OnTime
method to make scheduled procedure calls that perform various operations,
including automatic saves. Have a look at that.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Jim Carlock" wrote in message
...
Hi Rob,

Well I was thinking about some of the options I'd have...

I currently have a workbook with two sheets in it. One sheet
contains the code that holds/displays the combo boxes and
presents the buttons to commit the information to the other
sheet.

The combo boxes are filled with information that is read
from an Access database. Once a vendor is selected,
information is placed into some variables awaiting to be
moved to a destination once the Commit button is pressed.

The spreadsheet that is filled in is used as a form for
creating purchase orders.

The end result is that the form is saved as a Purchase Order
and printed out. They want the actual files saved and
sometimes 10 or 15 Purchase Orders get stacked into
one workbook (file). They're rebuilding factories.

So I'm thinking that I'd use one template per se, to start
each PO off.

The sheet immediately gets either a new file name or gets
placed into an existing workbook of sheets.

Thus I was trying to save the sheet, as I don't want the
code for the form to be saved. I just want the information
for the particular sheet saved.

You've displayed Sheet1.Copy... I'll check that out as it
sounds like a clipboard operation and I can create a new
sheet in an existing file or create a new sheet in a new file.

But that brings up another question...

I want to put an AutoSave into the worksheet, but I don't
want to use the old outdated Timer... somewhere I spotted
something on Microsoft's site today that might help me with
using an API to time things for this. I was originally thinking
about using a Timer1 control from VB but they don't have
VB installed on their machines... I think I need one of VB's
runtime files to get and use the timer control. But I did find
some things on Microsoft's site to use some API calls to
start a timer and raise events.

Word has an Autosave but Excel doesn't seem to have one.

Thanks for your help.

Jim Carlock
http://www.microcosmotalk.com
Feel free to post back to the newsgroup!


"Rob Bovey" wrote in message
...
Hi Jim,

I'm assuming you understand that the best you can do is save
a workbook that contains only a single worksheet. There's no
concept of saving a worksheet object outside a workbook
in modern versions of Excel. Given that, the easiest way to
get what you want is to copy the sheet that you want to save
out to a new workbook, then save that workbook. It can be
as simple as the following three lines of code depending on
your situation:

Sheet1.Copy
ActiveWorkbook.SaveAs "E:\MySheet.xls"
ActiveWorkbook.Close False

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Jim Carlock" wrote in message
...
I'm looking for a way to save one worksheet out of a workbook
of two sheets... I don't want the VB modules saved, nor any VB
code, forms, etc.

The Worksheet.SaveAs command doesn't seem to work even
though the help file indicates that it's to save a worksheet (and
I'm strictly keeping a worksheet defined as 1 and only 1
worksheet - rather than the whole work book).

I've thought about creating the worksheet as a seperate file but
before I continue in that direction, I'll ask here.

I've either missed something completely, and saving one and only
one worksheet out of a workbook is a little more complicated
than I've realized.

I'm leaning into seperating the code sheet from the data sheet
right at the moment.

Any advice is appreciated. Thank you.

--
Jim Carlock
http://www.microcosmotalk.com
Feel free to post back to the newsgroup!