Macro 16: Add and Name a New Worksheet
We start off this chapter with one of the simplest worksheet-related automations you can apply with
a macro — adding and naming a new worksheet.
How it works
If you read through the lines of the code, you’ll see this macro is relatively intuitive.
Sub Macro16()
‘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
Here’s how this macro works:
1. You must anticipate that if you give the new sheet a name that already exists, an error
occurs. 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.
2. 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. We give the sheet a new name by changing
the Name property of the ActiveSheet object. In this case, we are naming the worksheet
with the current date and time.
As with workbooks, each time you add a new sheet via VBA, it automatically becomes the
active sheet. Finally, in Step 2, notice that the macro exits the procedure. It has to do this
so that it doesn’t accidentally go into Step 3 (which should come into play only if an error
occurs).
3. Step 3 notifies the user that the sheet name already exists. Again, this step should only be
activated if an error occurs.
How to use it
To implement this macro, you can copy and paste it into a standard module:
1. Activate the 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.
