Dim fName as String
Dim fInitName as String
Dim Sh as Worksheet
set sh = ActiveSheet
fInitName = Activesheet.Parent.Worksheet("Sheet2") _
.Range("B9").Value
' copy creates a new workbook with sheet1 and sheet3
worksheets(Array("Sheet1","Sheet3")).Copy
fName = Application.GetSaveAsFilename( _
"InitialFilename:=fInitName, _
FileFilter:="Excel Files (*.xls),*.xls")
if not fName = "False" then
Activeworkbook.SaveAs filename:=fName
End if

This doesn't force the user to use the suggested filename.

If you want to just let the user pick a folder and force a name, see John
Walkenbach's site:

http://j-walk.com/ss/excel/tips/tip29.htm
Selecting a Directory

--
Regards,
Tom Ogilvy


Greg Liber wrote in message
...
OK...this works great!

NOW... sort of on the same topic... can anybody help me create a new
workbook to copy 2 of 4 existing worksheets in the original workbook...
THEN
name the new workbook and use information from the original workbook to
rename the new workbook? In the perfect world it would also allow the
user
to enter in the target folder for the new folder or if none chosen then
save
the new file into My documents (in windows 95,98 or XP)... not asking a
lot
am I?

Seems that I have hit a wall... since I had a way to do this before using
"sheets array" but since I started filtering information and copying and
pasting it when the macro gets to this spot it stalls... so I'm looking
for
another way to do the above...

Any and all suggestions would be greatly appreciated!

Greg

"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message
...
Dim ws As Worksheet
Set ws = Worksheets.Add
ws.Name = "NewName"

For alphabetical order, look at Chip Pearson's site:

http://www.cpearson.com/excel/sortws.htm

--

Vasant



"csus_tony " wrote in message
...
hello

how do i create a new worksheet in a workbook using code?

can i also rename the worksheet and put it in alphibetic order?

tony


---
Message posted from http://www.ExcelForum.com/