Naming Newly Created Sheet

cw_lynn · Mar 4th 2004

This is probably an easy answer... I have a template sheet that performs a calculation dependent upon a unit number. I want to loop through all unit numbers and create copy of the template sheet for each unit. I believe I can make everything work except for naming the newly created sheet. I don't know what generic name Excel will give it, so I need to name the active sheet. Activesheets.Name didn't work. The name will be the unit name listed in cell B2.

Thanks for any help.

Chris

Code

Sub Sheet_Create()


For i = 2 To 3
Range("A" & i).Select
Selection.Copy
Sheets("Temp").Select
Sheets("Temp").Copy After:=Sheets("Amenity")
Sheets.Name = "Test"  <--- Problem line
Range("A2").Select
ActiveSheet.Paste
Sheets("Name").Select
Application.CutCopyMode = False
Next
End Sub

Display More

X-man · Mar 4th 2004

May I suggest creating a Pivot Table and then use the "show pages" Option of it

Woody93 · Mar 4th 2004

Hi Chris,

Excel gives the new sheet the generic name, The old sheets name + a number
E.g. Sheets(“Temp”).Copy After:=Sheets("Amenity") gives a sheet with the name “Temp (2)” and copy again results in “Temp (3)”
So if you want to make a new sheet for each unit number in the column B and name them after the cell values in the column, you could write

Code

Sub Sheet_Create()


For i = 2 To 3 ' where n is the number of rows with a unit number
Sheets("Temp").Copy After:=Sheets("Amenity")
ActiveSheet.Name = Sheets("Temp").Range("B" & i).Value ' no s at the end of ActiveSheet
Next i


End Sub


Or if the only values in column B are the unit numbers You could do like this


Sub Sheet_Create() 


i = 2
do while Sheets("Temp").Cells(i,2).value<>""
Sheets("Temp").Copy After:=Sheets("Amenity") 
ActiveSheet.Name = Sheets("Temp").Range("B" & i).value 
i=i+1
Loop


End Sub

Display More

Have a nice day

Woody

Iridium · Mar 4th 2004

The newly added sheet will then be active so perhaps try something like this:

Code

Sub Test()
'
Worksheets.Add ' Add a worksheet
ActiveSheet.Name = "test" ' Name a worksheet.


End Sub

HTH

cw_lynn · Mar 4th 2004

Thanks guys!

The method I used was to change the sheet Temp (2) to a the new file name in each loop. Then, the next sheet created would be Temp (2) also.

As for the pivot table suggestion, I needed all of these to be in seperate sheets for distribution purposes.

Thanks again for the responses.

This really is a terrific forum!

Chris

Naming Newly Created Sheet

Participate now!

Share