Naming worksheets using marco?

[dandan6]

I always need to name the many worksheets that I have created. I tried to use macro to auto name the worksheets from a column list that I have created earlier but it did not work. It shows the previous names that I have created not the new ones. I think I'm missing the paste statement.

Please Help! I have been figuring this out for the whole week already....

Range("T8").Select
Selection.Copy
ActiveCell.FormulaR1C1 = "projectDrill"
Sheets("sheet1").Select
Sheets("sheet").Name = " projectDrill "
Range("T9").Select
Selection.Copy
ActiveCell.FormulaR1C1 = "projectFlow"
Sheets("sheet2").Select
Sheets("sheet").Name = " projectFlow "

 

[Norie]

How are you creating the worksheets?

 

[larrydunn]

You don't need copy or paste.

If you change your statement to ActiveSheet.Name = "Project", or whatever name you choose, it will work fine.

Simply select a sheet and give it a name.

You may have other problems, if these are newly created sheets. For example, you may be assuming that the first sheet will be Sheet1, and that may not be true, if other sheets have been created in the current Excel session.

 

[dandan6]

Can't I make "project1" or "project2" or "project3" as variables? For example to look up for the name at "A1" or "A2" or "A3"

Sheets("Sheet1").Select
Sheets("Sheet1").Name = "project1"
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "project2"
Sheets("Sheet3").Select
Sheets("Sheet3").Name = "project3"

Thanks u for your reply!

 

[larrydunn]

This may be what you're looking for.

Put in column A
Sheet1
Sheet2
Sheet3

Put in column B
Project1
Project2
Project3

The macro below will read column A starting at A1, and rename to name in column B. It stops when it reaches a blank cell.

Sub Renaming()
'Rename sheets in column A to names in column B
' Macro recorded 10/1/2006 by Larry Dunn
Dim sStartName As String
Dim sEndName As String

ActiveSheet.Range("A1").Select 'First sheet name goes here

NextRename:
sStartName = ActiveCell.Value
sEndName = ActiveCell.Next.Value 'Column B
If sStartName = "" Then GoTo EndOfSubroutine 'End of list
Sheets(sStartName).Name = sEndName
'ActiveSheet.Name = sEndName 'Rename here

ActiveCell.Offset(1, 0).Range("A1").Select 'Move down
GoTo NextRename

EndOfSubroutine:
End Sub

 

[dandan6]

Wow! I'm impressed...Thanks Larrydunn!