Loading
Excel.Tips.Net ExcelTips (Menu Interface)

Creating and Naming a Worksheet Using a Macro

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.

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.

Related Tips:

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

 

Leave your own comment:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*What is 5+3 (To prevent automated submissions and spam.)
 
 
           Commenting Terms

Comments for this tip:

Mo    11 Jul 2016, 10:29
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
Gav    13 Apr 2016, 09:49
@Willy Vanhaelen. Thank you, that solved it.

Gav.
Willy Vanhaelen    13 Apr 2016, 07:56
@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)
Theflash    12 Apr 2016, 22:12
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
Willy Vanhaelen    12 Apr 2016, 11:21
@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
Gav    12 Apr 2016, 04:52
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.
Willy Vanhaelen    31 Jan 2016, 11:53
@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
Shtela    30 Jan 2016, 11:05
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.
Dev    08 Jul 2015, 05:21
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
Doug Glancy    13 Jun 2015, 14:45
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
Lionel Dyck    13 Apr 2015, 09:17
Thank you very much - great advice for this novice.
Barry    12 Apr 2015, 07:14
@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


Lionel    11 Apr 2015, 12:52
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
Barry    07 Apr 2015, 05:26
@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
Gilbert Avila    06 Apr 2015, 14:29
How would you put the new worksheet at the beginning (all the way to the left)?
Barry    02 Jul 2014, 05:30
@ 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

Trev    01 Jul 2014, 09:37
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.
 
 

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us

 

Advertise with Us

Our Privacy Policy

Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives

 

Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2016 Sharon Parq Associates, Inc.