O'Reilly logo

101 Ready-To-Use Excel Macros by John Walkenbach, Michael Alexander

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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.

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

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required