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

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.

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

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

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