101 Ready-to-Use Excel® Macros

Table of Contents

Introduction

Topics Covered

What You Need to Know

What You Need to Have

How This Book Is Organized

Part I: Getting Started with Excel Macros

Part II: Working with Workbooks

Part III: Automating Worksheet Tasks with Macros

Part IV: Selecting and Modifying Ranges

Part V: Working with Data

Part VI: Working with PivotTables

Part VII: Manipulating Charts with Macros

Part VIII: E-Mailing from Excel

Part IX: Integrating Excel and Other Office Applications

Conventions in This Book

What the icons mean

About the Companion Website

Part I: Getting Started with Excel Macros

Chapter 1: Getting Started with Excel Macros

Becoming Familiar with Macro Recording Basics

Comparing Absolute and Relative Macro Recording

Recording macros with absolute references

Recording macros with relative references

Looking at Other Macro Recording Concepts

Macro-enabled file extensions

Macro security in Excel 2010

Trusted locations

Storing macros in your Personal Macro Workbook

Assigning a macro to a button and other form controls

Placing a macro on the Quick Access toolbar

Working in the Visual Basic Editor

Activating the VBE

Understanding VBE components

Working with the Project window

Adding a new VBA module

Removing a VBA module

Working with a Code Window

Minimizing and maximizing windows

Getting VBA code into a module

Customizing the VBA Environment

The Editor tab

The Editor Format tab

The General tab

The Docking tab

Discovering the Excel Object Model

Understanding objects

Understanding collections

Understanding properties

Understanding methods

Taking a Brief Look at Variables

About the Macros in This Book

Getting the sample files

Using the sample files

Things to keep in mind

Part II: Working with Workbooks

Macro 1: Creating a New Workbook from Scratch

How it works

How to use it

Macro 2: Saving a Workbook When a Particular Cell Is Changed

How it works

How to use it

Macro 3: Saving a Workbook Before Closing

How it works

How to use it

Macro 4: Protect a Worksheet on Workbook Close

How it works

How to use it

Macro 5: Unprotect a Worksheet on Workbook Open

How it works

How to use it

Macro 6: Open a Workbook to a Specific Tab

How it works

How to use it

Macro 7: Opening a Specific Workbook Defined by the User

How it works

How to use it

Macro 8: Determine Whether a Workbook Is Already Open

How it works

How to use it

Macro 9: Determine Whether a Workbook Exists in a Directory

How it works

How to use it

Macro 10: Refresh All Data Connections in Workbook on Open

How it works

How to use it

Macro 11: Close All Workbooks at Once

How it works

How to use it

Macro 12: Open All Workbooks in a Directory

How it works

How to use it

Macro 13: Print All Workbooks in a Directory

How it works

How to use it

Macro 14: Preventing the Workbook from Closing Until a Cell Is Populated

How it works

How to use it

Macro 15: Create a Backup of a Current Workbook with Today's Date

How it works

How to use it

Part III: Automating Worksheet Tasks with Macros

Macro 16: Add and Name a New Worksheet

How it works

How to use it

Macro 17: Delete All but the Active Worksheet

How it works

How to use it

Macro 18: Hide All but the Active Worksheet

How it works

How to use it

Macro 19: Unhide All Worksheets in a Workbook

How it works

How to use it

Macro 20: Moving Worksheets Around

How it works

How to use it

Macro 21: Sort Worksheets by Name

How it works

How to use it

Macro 22: Group Worksheets by Color

How it works

How to use it

Macro 23: Copy a Worksheet to a New Workbook

How it works

How to use it

Macro 24: Create a New Workbook for Each Worksheet

How it works

How to use it

Macro 25: Print Specified Worksheets

How it works

How to use it

Macro 26: Protect All Worksheets

How it works

How to use it

Macro 27: Unprotect All Worksheets

How it works

How to use it

Macro 28: Create a Table of Contents for Your Worksheets

How it works

How to use it

Macro 29: Zooming In and Out of a Worksheet with Double-Click

How it works

How to use it

Macro 30: Highlight the Active Row and Column

How it works

How to use it

Part IV: Selecting and Modifying Ranges

Macro 31: Selecting and Formatting a Range

How it works

How to use it

Macro 32: Creating and Selecting Named Ranges

How it works

How to use it

Macro 33: Enumerating Through a Range of Cells

How it works

How to use it

Macro 34: Select and Format All Named Ranges

How it works

How to use it

Macro 35: Inserting Blank Rows in a Range

How it works

How to use it

Macro 36: Unhide All Rows and Columns

How it works

How to use it

Macro 37: Deleting Blank Rows

How it works

How to use it

Macro 38: Deleting Blank Columns

How it works

How to use it

Macro 39: Select and Format All Formulas in a Workbook

How it works

How to use it

Macro 40: Find and Select the First Blank Row or Column

How it works

How to use it

Macro 41: Apply Alternate Color Banding

How it works

How to use it

Macro 42: Sort a Range on Double-Click

How it works

How to use it

Macro 43: Limit Range Movement to a Particular Area

How it works

How to use it

Macro 44: Dynamically Set the Print Area of a Worksheet

How it works

How to use it

Part V: Working with Data

Macro 45: Copy and Paste a Range

How it works

How to use it

Macro 46: Convert All Formulas in a Range to Values

How it works

How to use it

Macro 47: Perform the Text to Columns Command on All Columns

How it works

How to use it

Macro 48: Convert Trailing Minus Signs

How it works

How to use it

Macro 49: Trim Spaces from All Cells in a Range

How it works

How to use it

Macro 50: Truncate ZIP Codes to the Left Five

How it works

How to use it

Macro 51: Padding Cells with Zeros

How it works

How to use it

Macro 52: Replace Blanks Cells with a Value

How it works

How to use it

Macro 53: Append Text to the Left or Right of Your Cells

How it works

How to use it

Macro 54: Create a Super Data Cleanup Macro

How it works

How to use it

Macro 55: Clean Up Non-Printing Characters

How it works

How to use it

Macro 56: Highlight Duplicates in a Range of Data

How it works

How to use it

Macro 57: Hide All Rows but Those Containing Duplicate Data

How it works

How to use it

Macro 58: Selectively Hide AutoFilter Drop-down Arrows

How it works

How to use it

Macro 59: Copy Filtered Rows to a New Workbook

How it works

How to use it

Macro 60: Create a New Sheet for Each Item in an AutoFilter

How it works

How to use it

Macro 61: Show Filtered Columns in the Status Bar

How it works

How to use it

Part VI: Working with PivotTables

Macro 62: Create a Backwards-Compatible PivotTable

How it works

How to use it

Macro 63: Refresh All PivotTables Workbook

How it works

How to use it

Macro 64: Create a PivotTable Inventory Summary

How it works

How to use it

Macro 65: Make All PivotTables Use the Same Pivot Cache

How it works

How to use it

Macro 66: Hide All Subtotals in a PivotTable

How it works

How to use it

Macro 67: Adjust All Pivot Data Field Titles

How it works

How to use it

Macro 68: Set All Data Items to Sum

How it works

How to use it

Macro 69: Apply Number Formatting for All Data Items

How it works

How to use it

Macro 70: Sort All Fields in Alphabetical Order

How it works

How to use it

Macro 71: Apply Custom Sort to Data Items

How it works

How to use it

Macro 72: Apply PivotTable Restrictions

How it works

How to use it

Macro 73: Apply Pivot Field Restrictions

How it works

How to use it

Macro 74: Automatically Delete Pivot Table Drill-Down Sheets

How it works

How to use it

Macro 75: Print Pivot Table for Each Report Filter Item

How it works

How to use it

Macro 76: Create New Workbook for Each Report Filter Item

How it works

How to use it

Macro 77: Transpose Entire Data Range with a PivotTable

How it works

How to use it

Part VII: Manipulating Charts with Macros

Macro 78: Resize All Charts on a Worksheet

How it works

How to use it

Macro 79: Align a Chart to a Specific Range

How it works

How to use it

Macro 80: Create a Set of Disconnected Charts

How it works

How to use it

Macro 81: Print All Charts on a Worksheet

How it works

How to use it

Macro 82: Label First and Last Chart Points

How it works

How to use it

Macro 83: Color Chart Series to Match Source Cell Colors

How it works

How to use it

Macro 84: Color Chart Data Points to Match Source Cell Colors

How it works

How to use it

Part VIII: E-Mailing from Excel

Macro 85: Mailing the Active Workbook as an Attachment

How it works

How to use it

Macro 86: Mailing a Specific Range as Attachment

How it works

How to use it

Macro 87: Mailing a Single Sheet as an Attachment

How it works

How to use it

Macro 88: Send Mail with a Link to Our Workbook

How it works

How to use it

Macro 89: Mailing All E-Mail Addresses in Our Contact List

How it works

How to use it

Macro 90: Saving All Attachments to a Folder

How it works

How to use it

Macro 91: Saving Certain Attachments to a Folder

How it works

How to use it

Part IX: Integrating Excel and Other Office Applications

Macro 92: Running an Access Query from Excel

How it works

How to use it

Macro 93: Running an Access Macro from Excel

How it works

How to use it

Macro 94: Opening an Access Report from Excel

How it works

How to use it

Macro 95: Opening an Access Form from Excel

How it works

How to use it

Macro 96: Compacting an Access Database from Excel

How it works

How to use it

Macro 97: Sending Excel Data to a Word Document

How it works

How to use it

Macro 98: Simulating Mail Merge with a Word Document

How it works

How to use it

Macro 99: Sending Excel Data to a PowerPoint Presentation

How it works

How to use it

Macro 100: Sending All Excel Charts to a PowerPoint Presentation

How it works

How to use it

Macro 101: Convert a Workbook into a PowerPoint Presentation

How it works

How to use it

101 Ready-to-Use Excel® Macros

by Michael Alexander and John Walkenbach

101 Ready-to-Use Excel® Macros

Published by
John Wiley & Sons, Inc.
111 River Street
Hoboken, NJ 07030-5774
www.wiley.com

Copyright © 2012 by John Wiley & Sons, Inc., Hoboken, New Jersey

Published by John Wiley & Sons, Inc., Hoboken, New Jersey

Published simultaneously in Canada

No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA 01923, (978) 750-8400, fax (978) 646-8600. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, (201) 748-6011, fax (201) 748-6008, or online at http://www.wiley.com/go/permissions.

Trademarks: Wiley and the Wiley logo are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates in the United States and other countries, and may not be used without written permission. Excel is a registered trademark of Microsoft Corporation. All other trademarks are the property of their respective owners. John Wiley & Sons, Inc. is not associated with any product or vendor mentioned in this book.

Limit of Liability/Disclaimer of Warranty: The publisher and the author make no representations or warranties with respect to the accuracy or completeness of the contents of this work and specifically disclaim all warranties, including without limitation warranties of fitness for a particular purpose. No warranty may be created or extended by sales or promotional materials. The advice and strategies contained herein may not be suitable for every situation. This work is sold with the understanding that the publisher is not engaged in rendering legal, accounting, or other professional services. If professional assistance is required, the services of a competent professional person should be sought. Neither the publisher nor the author shall be liable for damages arising herefrom. The fact that an organization or Website is referred to in this work as a citation and/or a potential source of further information does not mean that the author or the publisher endorses the information the organization or Website may provide or recommendations it may make. Further, readers should be aware that Internet Websites listed in this work may have changed or disappeared between when this work was written and when it is read.

For general information on our other products and services, please contact our Customer Care Department within the U.S. at 877-762-2974, outside the U.S. at 317-572-3993, or fax 317-572-4002.

For technical support, please visit www.wiley.com/techsupport.

Wiley publishes in a variety of print and electronic formats and by print-on-demand. Some material included with standard print versions of this book may not be included in e-books or in print-on-demand. If this book refers to media such as a CD or DVD that is not included in the version you purchased, you may download this material at http://booksupport.wiley.com. For more information about Wiley products, visit www.wiley.com.

Library of Congress Control Number is available from the Publisher.

ISBN 978-1-118-28121-5 (pbk); 978-1-118-33068-5 (ebk); 978-1-118-33353-2 (ebk); 978-1-118-33466-9 (ebk)

Manufactured in the United States of America

10 9 8 7 6 5 4 3 2 1

About the Authors

Mike Alexander is a Microsoft Certified Application Developer (MCAD) and author of several books on advanced business analysis with Microsoft Access and Excel. He has more than 15 years of experience consulting and developing Office solutions. Michael has been named a Microsoft MVP for his ongoing contributions to the Excel community. In his spare time, he runs a free tutorial site, www.datapigtechnologies.com, where he shares Excel and Access tips.

John Walkenbach is author of more than 50 spreadsheet books and lives in southern Arizona. Visit his website at http://spreadsheetpage.com.

Dedication

To Mary

— Mike Alexander

Authors' Acknowledgments

Our deepest thanks to the brilliant team of professionals who helped bring this book to fruition.

Publisher's Acknowledgments

We're proud of this book; please send us your comments at http://dummies.custhelp.com. For other comments, please contact our Customer Care Department within the U.S. at 877-762-2974, outside the U.S. at 317-572-3993, or fax 317-572-4002.

Some of the people who helped bring this book to market include the following:

Acquisitions and Editorial

Project Editor: Linda Morris

Senior Acquisitions Editor: Katie Feltman

Copy Editor: Linda Morris

Technical Editor: John Walkenbach

Editorial Manager: Jodi Jensen

Editorial Assistant: Leslie Saxman

Sr. Editorial Assistant: Cherie Case

Composition Services

Project Coordinator: Sheree Montgomery

Layout and Graphics: Carrie A. Cesavice, Andrea Hornberger

Indexer: BIM Indexing & Proofreading Services

Publishing and Editorial for Technology Dummies

Richard Swadley, Vice President and Executive Group Publisher

Andy Cummings, Vice President and Publisher

Mary Bednarek, Executive Acquisitions Director

Mary C. Corder, Editorial Director

Publishing for Consumer Dummies

Kathleen Nebenhaus, Vice President and Executive Publisher

Composition Services

Debbie Stailey, Director of Composition Services

Introduction

In its broadest sense, an Excel macro is a sequence of instructions that automates some aspect of Excel so that you can work more efficiently and with fewer errors. You may create a macro, for example, to format and print your month-end sales report. After the macro is developed, you can then execute the macro to perform many time-consuming procedures automatically.

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!