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)

27

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

Jumping to a Section

One way you can navigate through a document is to jump from section to section. Here's the traditional way to quickly get to ...

Discover More

A Fast Find-Next

Tired of the Find and Replace dialog box blocking the view of your worksheet when you are searching for information? Do what ...

Discover More

Updating Automatically when Opening Under Macro Control

If your workbook contains links, you are normally given the opportunity to update those links when you open the workbook. ...

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

More ExcelTips (menu)

Controlling the Behavior of the Mouse Wheel

The mouse wheel, by default, controls scrolling vertically through your worksheet. If you don't want the wheel to control ...

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

Dissecting a String

VBA is a versatile programming language. It is especially good at working with string data. Here are the different VBA ...

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

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. Maximum image size is 6Mpixels. 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 9 - 2?

2017-06-06 02:57:32

Alan Elston

Just passing interest: Apparently there are 5 documented and at least one undocumented type of Sheet. The problem with my file is that I had a Excel 5 / 95 module “disguised” as a normal module. A Excel 5 / 95 module is apparently kept for some sort of backward compatibility. You can add them with
Modules.Add
and delete them with
Module.Delete
They appear in the Sheets count and not the Worksheets count.
But there ar others. More detail in the Thread I referenced.
It seems that one needs to be a bit careful when using Sheets in code. When doing anything with Worksheets I would still be inclined to use Worksheets in codes rather than Sheets.
Alan


2017-06-03 07:48:39

Alan Elston

Hi Wiily
Thanks. I had thought of things like that , but was nervous about doing it in case it caused any strange problems. Also I often find these strange folders hard to find.
So initially I did a Save As and copied and stored the path shown in the Save As Dialogue Box:
SaveAs_XLSRART.jpg http://imgur.com/QbJZ6af
C:\Users\Elston\AppData\Roaming\Microsoft\Excel\XLSTART
Then I deleted it.
Finally I saved a File at in that start folder that I use a lot just now to get it to open
All is well :-)
Thanks again.
Alan
P.s. I dropped a question in about my File with a weird extra sheet here:
https://www.excelforum.com/excel-programming-vba-macros/1187815-extra-sheet-not-a-chart-or-old-macro-module-or-worksheet-appears-to-be-normal-module.html#post4668803


2017-06-02 11:22:03

Willy Vanhaelen

@Alan
If you don't want your Personal workbook to be loaded each time Excel starts, simply delete it or move it out of the XLSTART folder.

Willy

P.S. I also have no idea what this other sheet can be.


2017-06-01 03:07:57

Alan Elston

P.s. Just out of passing interest, Here is that Personal Macro Workbook. ( I have hardly ever used it, - I wish the damm thing would not open every time I start Excel, I guess there is a way to stop that.. A quick google was not any help. It is in XL2007. I also have XL2003 on the same computer and that tries to open the Personal Macro Workbook and makes a mess. I think I only ever got the Personal Macro Workbook when I did a macro recording and selected that to put the macro in. Never mind :-) )
https://app.box.com/s/crxb4v6oshuk7p6yt948xj3a7p6ab2wb
Alan


2017-06-01 02:55:55

Alan Elston

@ Willy
Hi Wiily
Thanks. I thought it was something like that. I have never done anything with Charts, but I had heard that they are included in Sheets count but not in Worksheets count. Sheets can also include some old Macro sheet thing that is still kept for backward compatibility, ( Hit Ctrl+F11 to get one of those ).

I noticed that in my Personal Macro Workbook that I have three Worksheets, and have never added any sheet of any sort, as far as I know. But never the less I get Worksheets count of 3 and Sheets count of 4 ?? - When I run this code in my Personal Macro Workbook , all three worksheets are selected as expected, but in the Sheets section it selects something else first, but I cannot see what. Strange ?

Sub SheetsCheck()
Dim Cnt
For Cnt = 1 To Worksheets.Count
Worksheets.Item(Cnt).Select
Next Cnt
For Cnt = 1 To Sheets.Count
Sheets.Item(Cnt).Select
Next Cnt
End Sub

So I’ve got in the habit of always using Worksheets to be on the safe side. It seems to be that most people have got in the habit of always using Sheets

Alan


2017-05-31 14:14:57

Willy Vanhaelen

@Chris
Try the macro I posted 12 April 2016. I think it's foolproof concerning your problem. Just replace "Master" by "Template".

@Alan
You are right about wks=nothing. Because it's the last line in the code the macro ends after that and the variable wks is then destroyed and his memory freed anyhow.
WorkSheets(Worksheets.Count)
Sheets(Worksheets.Count)
Sheets(Sheets.Count)
are all valid but the result can be slightly different. WorkSheets counts only worksheets :-) while Sheets includes chart sheets. It all depends on where you want the new sheet to be. If you have only worksheets then all three variants give the same result.


2017-05-31 03:48:33

Alan Elston

@Chris
Hi
I don’t understand that error.
If you had a worksheet already named ‘Item’, then, of course it would error. But I think the error is then different to the one you mentioned
But I am slightly confused at why Allen is using a Loop. Also in VBA, I think there is no need to set wks = Nothing
This code, I think copies a new worksheet after the last current worksheet, ( and note I use Worksheets(Worksheets.Count) and not Sheets(Worksheets.Count). The Latter might sometimes give the wrong results).

Sub CopyRename()
Dim sName As String
Dim wks As Worksheet
Worksheets("Template").Copy after:=Worksheets(Worksheets.Count)
Set wks = ActiveSheet
sName = Application.InputBox(Prompt:="Enter new worksheet name")
wks.Name = sName
End Sub

Alan


2017-05-30 14:59:27

Chris W

Quick question, when I use the Macro below I get an error message that says,
"The name 'Item' already exists. Click Yes to use that version of the name, or click No to rename the version of 'Item' you're moving or copying.

How do I get around this error?

I thought that this would just copy the "Template" and just ask for a new name.

Sub CopyRename()
Dim sName As String
Dim wks As Worksheet
Worksheets("Template").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


Any ideas or help is appreciated.


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.