101 Ready-To-Use Excel Macros

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.

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 ...