How do i create a new worksheet

[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

 

[Bob Phillips]

hello

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

ActiveWorkbook.Worksheets.Add

can i also rename the worksheet

ActiveWorkbook.ActiveWorksheet.Name = "Tony"

and put it in alphibetic order?

See Chip Pearson's site at http://www.cpearson.com/excel/sortws.htm

 

[Vasant Nanavati]

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 Nanavati]

Hi Bob:

You probably meant ActiveSheet rather than ActiveWorksheet .

Regards,

Vasant.

(PS Myrna nailed me for the same mistake a couple of years ago.)

 

[Bob Phillips]

Hi Vasant,

Caught out by copy and paste again.

Bob

 

[Greg Liber]

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

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

 

[Tom Ogilvy]

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

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

 

[Greg Liber]

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

 

[Greg Liber]

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

 

[Nick Cranham]

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

 

[Nick Cranham]

Greg,

Dim fInitName as String <---- string


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

NickHK