Excel analysts often need to automate tasks related to worksheets. Whether it is un-hiding all sheets in a workbook, or printing all sheets at the same time, many tasks can be automated to save time and gain efficiencies. In this Part, we cover some of the more useful macros related to worksheets.
If you're brand-new to Excel VBA, we highly recommend you take a quick look at Part 1. There we provide the basic foundation you'll need to understand many of the concepts found in the macros in this part.
You can find the code for this Part on this book's companion website. See this book's Introduction for more on the companion website.
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.
‘Step 1: Tell Excel what to do if Error
On Error GoTo MyError
‘Step 2: Add a sheet and name it
ActiveSheet.Name = _
WorksheetFunction.Text(Now(), “m-d-yyyy h_mm_ss am/pm”)
‘Step 3: If here, an error happened; tell the user
MsgBox “There is already a sheet called that.”
Here's how this macro works:
1. You ...