An Excel Macro to Add and Name a New Worksheet
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:
Activate Visual Basic Editor by pressing Alt+F11.
Right-click the project/workbook name in the project window.
Type or paste the code in the newly created module.