An Excel Macro to Add and Name a New Worksheet

By Michael Alexander

One of the simplest worksheet-related automations you can apply with a macro in Excel is adding and naming a new worksheet. Here’s how that macro works and how to use it.

How the macro works

When you read through the lines of the code, you’ll see that this macro is relatively intuitive:

Sub Macro1()
'Step 1: Tell Excel what to do if error
    On Error GoTo MyError
'Step 2:  Add a sheet and name it
    Sheets.Add
    ActiveSheet.Name = _
    WorksheetFunction.Text(Now(), "m-d-yyyy h_mm_ss 
am/pm") Exit Sub 'Step 3: If here, an error happened; tell the user MyError: MsgBox "There is already a sheet called that." End Sub

You must anticipate that if you give the new sheet a name that already exists, an error would occur. So in Step 1, the macro tells Excel to immediately skip to the line that says MyError (in Step 3) if there is an error.

Step 2 uses the Add method to add a new sheet. By default, the sheet is called Sheetxx, where xx represents the number of the sheet. You give the sheet a new name by changing the Name property of the ActiveSheet object. In this case, you’re naming the worksheet with the current date and time.

As with workbooks, each time you use VBA to add a new sheet, the newly added sheet automatically becomes the active sheet. Finally, in Step 2, note that the macro exits the procedure. It has to do this so that it doesn’t accidentally go into Step 3 (which comes into play only if an error occurs).

Step 3 notifies the user that the sheet name already exists. Again, this step should be activated only if an error occurs.

How to use the macro

To implement this macro, you can copy and paste it into a standard module:

  1. Activate Visual Basic Editor by pressing Alt+F11.

  2. Right-click the project/workbook name in the project window.

  3. Choose Insert→Module.

  4. Type or paste the code in the newly created module.