Automatic SaveAs Worksheet Name
 

This is a small part of a larger project that extracts data from a lis
to populate a form. Then it copies the form to a new sheet in a ne
workbook. I then want it to save the new workbook as the worksheet nam
in a directory that I have already choosen. It then goes back an
clears the form for the next use. And I then wish it to email the ne
workbook to three people with the subject being the file name.

Soooo if you can help with some of this let me know. I have most of i
working but I don't know how to get the save as sheet name to work.

Lin

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

Automatic SaveAs Worksheet Name
 

Assuming:

sName is the name of the worksheet
sDir is the full path of the directory where you want the workbook to go,
for example,
"C:\Documents and Settings\shockley\Desktop\"
(and be sure to put the backslash on the end)

Then the command would simply be:

ActiveWorkbook.SaveAs (sDir & sName & ".xls")

Or, if you have assigned the workbook to an object variable "wbk"

wbk.SaveAs (sDir & sName & ".xls")

You may also want to preceed the save command with a command that insures
the current drive is the correct one. This is what I use:

ChDrive Left(sDir,1)

And then, if desired, once the workbook is saved, change back to the
previous drive.

HTH,
Shockley


"Linc" wrote in message
...

This is a small part of a larger project that extracts data from a list
to populate a form. Then it copies the form to a new sheet in a new
workbook. I then want it to save the new workbook as the worksheet name
in a directory that I have already choosen. It then goes back and
clears the form for the next use. And I then wish it to email the new
workbook to three people with the subject being the file name.

Soooo if you can help with some of this let me know. I have most of it
working but I don't know how to get the save as sheet name to work.

Linc


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

Automatic SaveAs Worksheet Name
 

Thanks for the response. I ultimately figured out that the string I
needed was

Activeworkbook.SaveAs "NameofFile_" & Cells(1, 12)

This added the counter number to the name of the file and saved it. So
the file Incidnet.xls became Incident_1002.xls with 1002 coming from
the cell in row one column 12.

Thanks for your help too.

Linc


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

Automatic SaveAs Worksheet Name
 

Glad you got it to work!

Shockley


"Linc" wrote in message
...

Thanks for the response. I ultimately figured out that the string I
needed was

Activeworkbook.SaveAs "NameofFile_" & Cells(1, 12)

This added the counter number to the name of the file and saved it. So
the file Incidnet.xls became Incident_1002.xls with 1002 coming from
the cell in row one column 12.

Thanks for your help too.

Linc


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/