How do i create a new worksheet

  • Thread starter Thread starter csus_tony
  • Start date Start date
C

csus_tony

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?

ton
 
Hi Bob:

You probably meant ActiveSheet rather than ActiveWorksheet :-).

Regards,

Vasant.

(PS Myrna nailed me for the same mistake a couple of years ago.)
 
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 said:
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
Click to expand...
 
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 said:
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
Click to expand...
 
Hi again!
I'm trying to make this work but it keeps getting hung up on the "Excel
part... any ideas? I'm using Office 2000 if that helps...

Thanks again for your help!
Greg
 
Ok I figured out the excel thing... but I'm now getting a
"Object doesn't support this property or method"
on line
finitname=Activesheet.parent.worksheet("sheet2")

any ideas?
Thanks in advance for your patience and help!

Greg
 
Greg,
What do you mean by the "Excel part" ?

Not sure what this line is achieving:
fInitName = Activesheet.Parent.Worksheet("Sheet2").Range("B9").Value
when you have a reference to sh which is not used.
try to make it clearer which WorkBook is active and which is being copied
to/from.

NickHK
 
Greg,

Dim fInitName as String <---- string


fInitName = Activesheet.Parent.Worksheet("Sheet2") <----worksheet

NickHK
 
Back
Top