Save just a worksheet from a workbook

[Guest]

Is it possible to save just a worksheet out of a workbook that is quite large?

Example: I have a user form that extracts data from a worksheet labeled
'data', and with a command button inputs the data into another worksheet
labeled 'form' in the same workbook. The userform also allows the user to
change the information on the user form if it is incorrect, and then with the
command button populate the appropriate fields on the form (worksheet
formatted to look like a commonly used form).

Since the workbook's size is upwards of 1 meg, and each applicant would have
to be saved, I thought the best way would be to save just the form with the
data put into it. I have tried several different ways via this website and
others, but I am a novice vba user.

Please help! Thankyou.

 

[Guest]

Maybe when the user clicks a command button to save (or in the
Workbook_BeforeClose event) you can delete all the other sheets and save the
workbook with a new name.

Dim Sheet As Worksheet

For Each Sheet In ActiveWorkbook
If Sheet.Name <> "Form" Then Sheet.Delete
Next Sheet

ActiveWorkbook.SaveAs "C:\NewWorkbook.xls"

 

[Tom Ogilvy]

activesheet.copy ' creates a new one page workbook
' with a copy of the activesheet in it
' this becomes the activesheet/book
Activeworkbook.SaveAS "C:\MyFolder\" & activesheet.name & ".xls"
Activeworkbook.Close SaveChanges:=False

 

[Guest]

Thanks so much for your input!

It definitely helped. I do have to admit that after trying your suggestion,
I tried Tom's. Tom's was more of what I was looking for. Nonetheless, your
help is greatly appreciated. Thankyou!

 

[Guest]

Tom,

Thankyou very much for your suggestion. It worked perfectly and then some!

If it wasn't for Excel Newsgroups and the people, like yourself that provide
help, I don't know what I'd do.

Thankyou again!

 

[Guest]

Hi Tom. Ran across this thread today and your code was helpful to me as well.
I have a question. Using similar code, could I do the following:

I have a workbook with a seperate sheet for each employee. Our admin updates
their sales leads every day with entries on their sheets. I would like to use
a command button with your code. She could finish the entry on the master,
click to use your code to save their sheet as a seperate workbook in the same
folder. Then the salesperson could update their own sheet and use a command
button to then update their sheet in the master.

Problem is that the info would have to append and not overwrite so this
maybe impossible. Thank you for any help you can provide.