Naming worksheets using marco?

dandan6

New Member
Joined
Oct 1, 2006
Messages
3
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.... :cry:

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 "
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
How are you creating the worksheets?
 

larrydunn

Board Regular
Joined
Jun 1, 2003
Messages
130
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

New Member
Joined
Oct 1, 2006
Messages
3
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

Board Regular
Joined
Jun 1, 2003
Messages
130
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
 

Forum statistics

Threads
1,136,430
Messages
5,675,794
Members
419,586
Latest member
RoteichA

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top