Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Creating and Naming a Worksheet Using a Macro.

Creating and Naming a Worksheet Using a Macro

by Allen Wyatt
(last updated February 17, 2015)

19

Jeff would like to create a copy of his "master" worksheet, prompt for a name of the new worksheet, and move it to the end of the worksheet tabs, all from within a macro. He tried to record a macro to do this, but it didn't work.

The fact that the recorded macro didn't work isn't terribly surprising. When you record a macro, you tell Excel to record the steps you take. Those steps (in this instance) included the naming of the worksheet, so that name was recorded in the macro. Try to run the macro a second time, and you will get an error because the worksheet you are trying to create on the second pass was already created on the first.

In this case you have to write a macro manually. You can start with recording the process, and you will get a code like the following:

Sub Macro1()
    Sheets("Master").Select
    Sheets("Master").Copy After:=Sheets(3)
    Sheets("Master (2)").Select
    Sheets("Master (2)").Name = "NewMaster"
End Sub

Note that the code places the worksheet (after the third sheet) and then always names it the same thing. There's a lot to change here. What you want to do is change it to something like the following:

Sub CopyRename()
    Dim sName As String
    Dim wks As Worksheet
    Worksheets("Master").Copy after:=Sheets(Worksheets.Count)
    Set wks = ActiveSheet
    Do While sName <> wks.Name
        sName = Application.InputBox _
          (Prompt:="Enter new worksheet name")
        On Error Resume Next
        wks.Name = sName
        On Error GoTo 0
    Loop
    Set wks = Nothing
End Sub

This macro will copy the worksheet named "Master" to the end of sheet list (no matter how many sheets you have in the workbook) and continue to prompt for a new worksheet name until a valid name is entered.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3898) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Creating and Naming a Worksheet Using a Macro.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Finding Formatted Bulleted Paragraphs

Want to find the bulleted paragraphs within a large document? Word doesn't have a built-in way to search for this formatting. ...

Discover More

Incomplete and Corrupt Sorting

Using the sorting tools, on the toolbar, may result in some unwanted results, such as jumbled data. If this happens to you, ...

Discover More

Entering Large Time Values

If you need to input humongous times into a worksheet, you may run into a problem if you need to enter times greater than ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

MORE EXCELTIPS (MENU)

Replacing Some Formulas with the Formula Results

Macros are often used to process the data stored in a worksheet. Some of these processing needs can be pretty specific to ...

Discover More

Deriving the Worksheet Name

Excel doesn't provide an easy way to grab the worksheet name for use within a worksheet. Here are some ideas on ways you can ...

Discover More

Quickly Dumping Array Contents

Variable arrays are used quite often in macros. If you use an array once in your macro and then need to reuse it for ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments for this tip:

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is three minus 1?

2017-04-05 15:02:24

Doug W

Great tip! This works well. I added a small revision so that I could have a constant text entry followed by a number:
sName = "Request " & Application.InputBox _

This allows me to just type or enter the number needed and then the worksheet name will be "Request 'NumberEntered'"

Thanks again....this site is a must for Excel users trying to get past repetitive keystrokes!


2017-02-02 12:41:57

Ravi

Hi , I want to create an macro and requirements are as below .

Based my report macro has to create the new sheet and has to change the name as per the report. Example- in an file we have 10 business area and for those need different sheet and each sheet name has to change as per business area.

Any suggestions??


2016-07-11 10:29:28

Mo

Hi and thanks so much for this, very helpful.

Quick question, how do I edit this so that it only copied the values and not the formula from the sheet?

The idea is to create a static copy!

Thanks in advance


2016-04-13 09:49:52

Gav

@Willy Vanhaelen. Thank you, that solved it.

Gav.


2016-04-13 07:56:41

Willy Vanhaelen

@Theflash

That's quite easy. In the macro I posted 12 April replace

Worksheets("Master").Copy after:=Sheets(Worksheets.Count)

with

ActiveSheet.Copy after:=Sheets(Worksheets.Count)


2016-04-12 22:12:02

Theflash

Quick question on the code below would it be possible to have the user input the sheet to copy then prompt to name the copy? For example I have sheet 1,2,3,4,.... and I prompt to say I want to copy sheet 2 and name it "something".



Sub CopyRename()
Dim sName As String
Dim wks As Worksheet
Worksheets("Master").Copy after:=Sheets(Worksheets.Count)
Set wks = ActiveSheet
Do While sName <> wks.Name
sName = Application.InputBox _
(Prompt:="Enter new worksheet name")
On Error Resume Next
wks.Name = sName
On Error GoTo 0
Loop
Set wks = Nothing
End Sub


2016-04-12 11:21:41

Willy Vanhaelen

@Gav
This macro will do the job. It will ask to enter a name for the new worksheet until you enter a valid one unless you press cancel, it then will delete the just created sheet and end.

Sub Macro3()
Dim sName As String
Sheets("Master").Copy After:=Sheets(Sheets.Count)
On Error Resume Next
Do
sName = InputBox("Enter new valid worksheet name")
If sName = "" Then
Application.DisplayAlerts = False
ActiveSheet.Delete
Exit Sub
End If
ActiveSheet.Name = sName
If ActiveSheet.Name = sName Then Exit Do
Beep
Loop
End Sub


2016-04-12 04:52:24

Gav

Hi Allen. Tried the code in my spreadsheet and it works great with just one issue. If you click on cancel on the input box it still creates the copied worksheet but names it "false". How would you code it so that if the user clicks "cancel" it does not create the copied sheet?

Cheers

Gav.


2016-01-31 11:53:27

Willy Vanhaelen

@Shtela

Supposing the name for the new sheet is in cell "A1", this macro will do the job. It still will ask for a worksheet name if the name in A1 lready exists or isinvalid.

Sub Macro2()
Dim sName As String
Sheets("Master").Copy After:=Sheets(Sheets.Count)
sName = Sheets("Master").Range("A1")
On Error Resume Next
Do
ActiveSheet.Name = sName
If ActiveSheet.Name = sName Then Exit Do
sName = InputBox("Enter new valid worksheet name")
If sName = "" Then Exit Do
Loop
End Sub


2016-01-30 11:05:10

Shtela

Hi Allen,

First, thank you for these hints, they are very useful.

I was wondering if I am dedicating the first work sheet as "input data" sheet. In this sheet I have a cell for the name of the new worksheet which I would like to create from the master sheet. So, how can I create a macro that can create a copy of the master sheet to new worksheet named as the name in the cell.

Please help.

Thank you.


2015-07-08 05:21:14

Dev

I tried writing a macro to use the name of the worksheet to populate a column in several documents but it only works in the one I recorded the macro for but not others.

I know I can use ActiveWorksheet.Name to return the active worksheet. However, i am at a lost in coding to get it to past the name into current column on other documents


Sub Fill_in_FieldCol_withActive_Sheet_Name()
'
' Fill_in_FieldCol_withActive_Sheet_Name Macro
' Inserts a Column at specific location in Active Worksheet. Populate that Column with Active Worksheet Name.
'
' Keyboard Shortcut: Ctrl+Shift+P
'
Columns("C:C").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("C1").Select
ActiveCell.FormulaR1C1 = "Location"
Range("C2").Select
Sheets("Builders in Barrow-in-furness").Select
Sheets("Builders in Barrow-in-furness").Name = "Builders in Barrow-in-furness"
Range("C2").Select
ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
DisplayAsIcon:=False, NoHTMLFormatting:=True
Selection.AutoFill Destination:=Range("C2:C42"), Type:=xlFillDefault
Range("C2:C42").Select
ActiveWorkbook.Save
End Sub

Also, is there a way to get this code to open all files in a particular folder and run Macro on all without me having to open them individually and executing?

Thanks
Dev


2015-06-13 14:45:41

Doug Glancy

I recently discovered a nice way to validate the new worksheet name and posted about it at http://yoursumbuddy.com/prompt-to-name-new-sheet/

Here's the code:

Sub PromptForNewSheetWithName()
Dim DefaultSheetName As String

ActiveWorkbook.Worksheets.Add
DefaultSheetName = ActiveSheet.Name
Application.Dialogs(xlDialogWorkbookName).Show
If ActiveSheet.Name = DefaultSheetName Then
MsgBox "You didn't name the new sheet." & vbCrLf & _
"Processing cancelled", vbExclamation
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True
End If
End Sub


2015-04-13 09:17:34

Lionel Dyck

Thank you very much - great advice for this novice.


2015-04-12 07:14:54

Barry

@Lionel,

The basic problem is that the macro is trying to name the worksheet using a name containing prohibited characters namely "/". Worksheet names are not allowed to contain the following characters: /?*[], and the maximum length is 31 characters (including any spaces).

You could change you macro code to format the date to something similar e.g.:
wks.Name = Format(Settlement_Date, "mmm-d-yyyy") or
wks.Name = Format(Settlement_Date, "mmm, d yyyy")

I noticed also you macro code crashed if a valid date was not entered into the Inputboxes - it would be good coding practice to check this before assigning it to the variable which only accepts dates, and requesting the User enters a valid date otherwise. This then also leaves a default name copy of your template. I would only create the copy of the Worksheet once both Inputs have been accepted/validated, as in the following code:

Sub New_Week()
'
' New_Week Macro
' Copy Weekly Template into new sheet after the Menu tab
'
Dim Settlement_Date As Variant
Dim Check_Date As Variant
Dim temp As Variant
Dim wks As Worksheet

Do
Settlement_Date = InputBox("Enter the Settlement Date (mm/ddd/yyyy):", "SETTLEMENT DATE")
If IsDate(Settlement_Date) = True Then Exit Do
temp = MsgBox("Invalid Date re-enter = OK, Cancel = Abort", vbOKCancel + vbQuestion, "ERROR!")
If temp = vbCancel Then Exit Sub
Loop

Do
Check_Date = InputBox("Enter the Check Date (mm/ddd/yyyy):", "CHECK DATE")
If IsDate(Check_Date) = True Then Exit Do
temp = MsgBox("Invalid Date re-enter = OK, Cancel = Abort", vbOKCancel + vbQuestion, "ERROR!")
If temp = vbCancel Then Exit Sub
Loop

Sheets("Weekly Template").Copy Before:=Sheets(2)
Set wks = ActiveSheet

wks.Range("B2").Value = Settlement_Date
wks.Range("D2").Value = Check_Date

wks.Name = Format(Settlement_Date, "mmm,d yyyy")

End Sub



2015-04-11 12:52:03

Lionel

I am trying to write a macro to copy a worksheet to a new worksheet, then prompt for some info, and then rename the copied worksheet based on the prompt. It is failing on the rename.

Here is my code:

Sub New_Week()
'
' New_Week Macro
' Copy Weekly Template into new sheet after the Menu tab
'
Sheets("Weekly Template").Select
Sheets("Weekly Template").Copy Before:=Sheets(2)

Dim Settlement_Date As Date
Dim Check_Date As Date

Settlement_Date = InputBox("Enter the Settlement Date (mm/ddd/yyyy):")

Range("B2").Value = Settlement_Date

Check_Date = InputBox("Enter the Check Date (mm/ddd/yyyy):")

Range("D2").Value = Check_Date

Dim wks As Worksheet
Set wks = ActiveSheet
wks.Name = Settlement_Date

End Sub


2015-04-07 05:26:30

Barry

@Gilbert

Change the line that makes the copy to:

Worksheets("Master").Copy Before:= Worksheets(1)

This works because the "index" number of a worksheet is numbered of the tab from left to right at the bottom of the workbook window (assuming Sheet Tabs is enabled); this is true even if you change the order of the worksheets. So the leftmost worksheet always has index = 1.

Note: if there are "Hidden" or "Very Hidden" worksheets they too will be indexed even though not visible


2015-04-06 14:29:17

Gilbert Avila

How would you put the new worksheet at the beginning (all the way to the left)?


2014-07-02 05:30:35

Barry

@ Trev Change to code to this. I don't know if ActiveCell is on your "Log" worksheet or not. Please note I haven't tested this.

Sub CopyRename()
Dim sName As String
Dim wks As Worksheet
Dim wksOrig as Worksheet

Set wksOrig = ActiveSheet 'remember the active sheet from where the macro was invoked

Worksheets("Master").Copy after:=Sheets(Worksheets.Count)
Set wks = ActiveSheet
Do While sName <> wks.Name
sName = Application.InputBox _
(Prompt:="Enter new worksheet name")
On Error Resume Next
wks.Name = sName
On Error GoTo 0
Loop
wksOrig.Activate 'return to sheet where the macro was invoked from
ActiveCell = sName 'write the name of the new worksheet to the active cell
Worksheets("Log").Activate 'go to the worksheet named "Log"
Set wks = Nothing
Set wksOrig = Nothing
End Sub


2014-07-01 09:37:10

Trev

This Macro Would work great for me if I could rename the copied sheet to ActiveCell.Text and return back to a specific sheet called "Log". When I try to change the input box code to just activecell.text I freeze.. any thoughts? Im a VB noob by the way.


This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.

Links and Sharing