Adding and Naming Worksheets

Mar 14th, 2002, 01:38 PM

I have a VBA routine where I need to add and rename worksheets. I have a list of names and some date for each name, and new names and data are added each week. When a new name is encountered, the code creates a new worksheet for that person. The new worksheets are automatically named "Sheet 1", "Sheet 2", "Sheet 3" etc.
The problem occurs when I try to rename the new worksheets. My code works fine for the first one but times out on the second and subsequent ones (I never know how many there are going to be). What I need is a way to change the name of the new worksheet when I don't know its current name.

Al Chara · Mar 14th, 2002, 01:44 PM

Try using its index number:
Sheets(1).Name = "new sheet"

I hope this helps.
Kind regards, Al.

Mudface · Mar 14th, 2002, 01:45 PM

Would the following work OK for you?

With Worksheets.Add
.Move after:=Worksheets(Worksheets.Count)
.Name = YourName
End With

[ This Message was edited by: Mudface on 2002-03-14 12:47 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 14th, 2002, 01:38 PM	#1
Guest Posts: n/a	I have a VBA routine where I need to add and rename worksheets. I have a list of names and some date for each name, and new names and data are added each week. When a new name is encountered, the code creates a new worksheet for that person. The new worksheets are automatically named "Sheet 1", "Sheet 2", "Sheet 3" etc. The problem occurs when I try to rename the new worksheets. My code works fine for the first one but times out on the second and subsequent ones (I never know how many there are going to be). What I need is a way to change the name of the new worksheet when I don't know its current name.

Mar 14th, 2002, 01:44 PM	#2
Al Chara MrExcel MVP Join Date: Feb 2002 Location: Monterrey, Mexico Posts: 1,433	Try using its index number: Sheets(1).Name = "new sheet" I hope this helps. Kind regards, Al.

Mar 14th, 2002, 01:45 PM	#3
Mudface MrExcel MVP Join Date: Feb 2002 Location: Sunny, spring-like Hull Posts: 3,339	Would the following work OK for you? With Worksheets.Add .Move after:=Worksheets(Worksheets.Count) .Name = YourName End With [ This Message was edited by: Mudface on 2002-03-14 12:47 ]