101 Ready-To-Use Excel Macros

A workbook is not just an Excel file; it's also an object in Excel's Object model (a programming hierarchy that exposes parts of Excel to VBA). This means that you can reference workbooks through VBA to do cool things like automatically create new workbooks, prevent users from closing workbooks, automatically back up workbooks, and much more. We start our list of 101 macros here with a list of the most useful workbook macros.

If you're brand-new to Excel VBA, we highly recommend that you first take a quick look at Part I. There, you will find the basic foundation you'll need to understand many of the concepts found in the macros in this Part.

The code for this Part can be found on this book's companion website. See this book's Introduction for more on the companion website.

Macro 1: Creating a New Workbook from Scratch

You may sometimes want or need to create a new workbook in an automated way. For instance, you may need to copy data from a table and paste it into a newly created workbook. The following macro copies a range of cells from the active sheet and pastes the data into a new workbook.

How it works

This macro is relatively intuitive as you read through the lines of the code.

Sub Macro1()

‘Step 1 Copy the data

Sheets(“Example 1”).Range(“B4:C15”).Copy

‘Step 2 Create ...