101 Ready-to-Use Excel® Macros -- About the Authors -- Dedication -- Authors' Acknowledgments -- Contents at a Glance -- Table of Contents -- Introduction -- Topics Covered -- What You Need to Know -- What You Need to Have -- How This Book Is Organized -- Conventions in This Book -- About the Companion Website -- PART I: Getting Started with Excel Macros -- Becoming Familiar with Macro Recording Basics -- Comparing Absolute and Relative Macro Recording -- Looking at Other Macro Recording Concepts -- Working in the Visual Basic Editor -- Working with the Project window -- Working with a Code Window -- Customizing the VBA Environment -- Discovering the Excel Object Model -- Taking a Brief Look at Variables -- About the Macros in This Book -- 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 -- Index.