101 Ready-To-Use Excel Macros

Book Description

Save time and be more productive with this helpful guide to Excel macros!

While most books about Excel macros offer only minor examples, usually aimed at illustrating a particular topic, this invaluable resource provides you with the tools needed to efficiently and effectively program Excel macros immediately. Step-by-step instructions show you how to create VBA macros and explain how to customize your applications to look and work exactly as you want them to. By the end of the book, you will understand how each featured macro works, be able to reuse the macros included in the book and online, and modify the macro for personal use.

• Shows you how to solve common problems with the featured macros, even if you lack extensive programming knowledge
• Outlines a problem that needs to be solved and provides the actual Excel macro, as well as the downloadable code, to solve the problem
• Provides an explanation of how each macro works and where to use the macro

With 101 Ready-to-Use Excel Macros, Microsoft MVP Michael Alexander helps you save time, automate tasks, and ultimately be more productive.

Table of contents

PART I: Getting Started with Excel Macros

PART II: Working with Workbooks
Macro 1: Creating a New Workbook from Scratch
Macro 2: Saving a Workbook When a Particular Cell Is Changed
Macro 3: Saving a Workbook Before Closing
Macro 4: Protect a Worksheet on Workbook Close
Macro 5: Unprotect a Worksheet on Workbook Open
Macro 6: Open a Workbook to a Specific Tab
Macro 7: Opening a Specific Workbook Defined by the User
Macro 8: Determine Whether a Workbook Is Already Open
Macro 9: Determine Whether a Workbook Exists in a Directory
Macro 10: Refresh All Data Connections in Workbook on Open
Macro 11: Close All Workbooks at Once
Macro 12: Open All Workbooks in a Directory
Macro 13: Print All Workbooks in a Directory
Macro 14: Preventing the Workbook from Closing Until a Cell Is Populated
Macro 15: Create a Backup of a Current Workbook with Today’s Date

PART III: Automating Worksheet Tasks with Macros
Macro 16: Add and Name a New Worksheet
Macro 17: Delete All but the Active Worksheet
Macro 18: Hide All but the Active Worksheet
Macro 19: Unhide All Worksheets in a Workbook
Macro 20: Moving Worksheets Around
Macro 21: Sort Worksheets by Name
Macro 22: Group Worksheets by Color
Macro 23: Copy a Worksheet to a New Workbook
Macro 24: Create a New Workbook for Each Worksheet
Macro 25: Print Specified Worksheets
Macro 26: Protect All Worksheets
Macro 27: Unprotect All Worksheets
Macro 28: Create a Table of Contents for Your Worksheets
Macro 29: Zooming In and Out of a Worksheet with Double-Click
Macro 30: Highlight the Active Row and Column

PART IV: Selecting and Modifying Ranges
Macro 31: Selecting and Formatting a Range
Macro 32: Creating and Selecting Named Ranges
Macro 33: Enumerating Through a Range of Cells
Macro 34: Select and Format All Named Ranges
Macro 35: Inserting Blank Rows in a Range
Macro 36: Unhide All Rows and Columns
Macro 37: Deleting Blank Rows
Macro 38: Deleting Blank Columns
Macro 39: Select and Format All Formulas in a Workbook
Macro 40: Find and Select the First Blank Row or Column
Macro 41: Apply Alternate Color Banding
Macro 42: Sort a Range on Double-Click
Macro 43: Limit Range Movement to a Particular Area
Macro 44: Dynamically Set the Print Area of a Worksheet

PART V: Working with Data
Macro 45: Copy and Paste a Range
Macro 46: Convert All Formulas in a Range to Values
Macro 47: Perform the Text to Columns Command on All Columns
Macro 48: Convert Trailing Minus Signs
Macro 49: Trim Spaces from All Cells in a Range
Macro 50: Truncate ZIP Codes to the Left Five
Macro 51: Padding Cells with Zeros
Macro 52: Replace Blanks Cells with a Value
Macro 53: Append Text to the Left or Right of Your Cells
Macro 54: Create a Super Data Cleanup Macro
Macro 55: Clean Up Non-Printing Characters
Macro 56: Highlight Duplicates in a Range of Data
Macro 57: Hide All Rows but Those Containing Duplicate Data
Macro 58: Selectively Hide AutoFilter Drop-down Arrows
Macro 59: Copy Filtered Rows to a New Workbook
Macro 60: Create a New Sheet for Each Item in an AutoFilter
Macro 61: Show Filtered Columns in the Status Bar

PART VI: Working with PivotTables
Macro 62: Create a Backwards-Compatible PivotTable
Macro 63: Refresh All PivotTables Workbook
Macro 64: Create a Pivot Table Inventory Summary
Macro 65: Make All PivotTables Use the Same Pivot Cache
Macro 66: Hide All Subtotals in a PivotTable
Macro 67: Adjust All Pivot Data Field Titles
Macro 68: Set All Data Items to Sum
Macro 69: Apply Number Formatting for All Data Items
Macro 70: Sort All Fields in Alphabetical Order
Macro 71: Apply Custom Sort to Data Items
Macro 72: Apply PivotTable Restrictions
Macro 73: Apply Pivot Field Restrictions
Macro 74: Automatically Delete Pivot Table Drill-Down Sheets
Macro 75: Print Pivot Table for Each Report Filter Item
Macro 76: Create New Workbook for Each Report Filter Item
Macro 77: Transpose Entire Data Range with a PivotTable

PART VII: Manipulating Charts with Macros
Macro 78: Resize All Charts on a Worksheet
Macro 79: Align a Chart to a Specific Range
Macro 80: Create a Set of Disconnected Charts
Macro 81: Print All Charts on a Worksheet
Macro 82: Label First and Last Chart Points
Macro 83: Color Chart Series to Match Source Cell Colors
Macro 84: Color Chart Data Points to Match Source Cell Colors

PART VIII: E-Mailing from Excel
Macro 85: Mailing the Active Workbook as an Attachment
Macro 86: Mailing a Specific Range as Attachment
Macro 87: Mailing a Single Sheet as an Attachment
Macro 88: Send Mail with a Link to Our Workbook
Macro 89: Mailing All E-Mail Addresses in Our Contact List
Macro 90: Saving All Attachments to a Folder
Macro 91: Saving Certain Attachments to a Folder

PART IX: Integrating Excel and Other Office Applications
Macro 92: Running an Access Query from Excel
Macro 93: Running an Access Macro from Excel
Macro 94: Opening an Access Report from Excel
Macro 95: Opening an Access Form from Excel
Macro 96: Compacting an Access Database from Excel
Macro 97: Sending Excel Data to a Word Document
Macro 98: Simulating Mail Merge with a Word Document
Macro 99: Sending Excel Data to a PowerPoint Presentation
Macro 100: Sending All Excel Charts to a PowerPoint Presentation
Macro 101: Convert a Workbook into a PowerPoint Presentation

About The Author

John Walkenbach

John Walkenbach, a.k.a. Mr. Spreadsheet, is arguably the world's foremost authority on Microsoft Excel. His fifty-plus Excel books include Excel 2013 Power Programming with VBA, Excel 2013 Formulas, and the bestselling Excel 2013 Bible, all published by Wiley. He has also written hundreds of articles and software reviews, and created the award-winning Power Utility Pak add-in for Excel.

John lives in Tucson, Arizona. He also plays the banjo - but don't let that prevent you from buying his books.

Michael Alexander

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 16 years’ experience consulting and developing Office solutions. Mike has been named a Microsoft MVP for his ongoing contributions to the Excel community. You can visit Mike at www.datapigtechnologies.com, where he regularly shares Excel and Access tips and techniques.