101 Ready-To-Use Excel Macros

101 Ready-To-Use Excel Macros

by Michael Alexander, John Walkenbach
Released June 2012
Publisher(s): For Dummies
ISBN: 9781118330685

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

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

  1. Cover
  2. Table of Contents
  3. Title Page
  4. Introduction
  5. Part I: Getting Started with Excel Macros
    1. Chapter 1: Getting Started with Excel Macros
      1. Becoming Familiar with Macro Recording Basics
      2. Comparing Absolute and Relative Macro Recording
      3. Looking at Other Macro Recording Concepts
      4. Working in the Visual Basic Editor
      5. Working with the Project window
      6. Working with a Code Window
      7. Customizing the VBA Environment
      8. Discovering the Excel Object Model
      9. Taking a Brief Look at Variables
      10. About the Macros in This Book
  6. Part II: Working with Workbooks
    1. Macro 1: Creating a New Workbook from Scratch
    2. Macro 2: Saving a Workbook When a Particular Cell Is Changed
    3. Macro 3: Saving a Workbook Before Closing
    4. Macro 4: Protect a Worksheet on Workbook Close
    5. Macro 5: Unprotect a Worksheet on Workbook Open
    6. Macro 6: Open a Workbook to a Specific Tab
    7. Macro 7: Opening a Specific Workbook Defined by the User
    8. Macro 8: Determine Whether a Workbook Is Already Open
    9. Macro 9: Determine Whether a Workbook Exists in a Directory
    10. Macro 10: Refresh All Data Connections in Workbook on Open
    11. Macro 11: Close All Workbooks at Once
    12. Macro 12: Open All Workbooks in a Directory
    13. Macro 13: Print All Workbooks in a Directory
    14. Macro 14: Preventing the Workbook from Closing Until a Cell Is Populated
    15. Macro 15: Create a Backup of a Current Workbook with Today's Date
  7. Part III: Automating Worksheet Tasks with Macros
    1. Macro 16: Add and Name a New Worksheet
    2. Macro 17: Delete All but the Active Worksheet
    3. Macro 18: Hide All but the Active Worksheet
    4. Macro 19: Unhide All Worksheets in a Workbook
    5. Macro 20: Moving Worksheets Around
    6. Macro 21: Sort Worksheets by Name
    7. Macro 22: Group Worksheets by Color
    8. Macro 23: Copy a Worksheet to a New Workbook
    9. Macro 24: Create a New Workbook for Each Worksheet
    10. Macro 25: Print Specified Worksheets
    11. Macro 26: Protect All Worksheets
    12. Macro 27: Unprotect All Worksheets
    13. Macro 28: Create a Table of Contents for Your Worksheets
    14. Macro 29: Zooming In and Out of a Worksheet with Double-Click
    15. Macro 30: Highlight the Active Row and Column
  8. Part IV: Selecting and Modifying Ranges
    1. Macro 31: Selecting and Formatting a Range
    2. Macro 32: Creating and Selecting Named Ranges
    3. Macro 33: Enumerating Through a Range of Cells
    4. Macro 34: Select and Format All Named Ranges
    5. Macro 35: Inserting Blank Rows in a Range
    6. Macro 36: Unhide All Rows and Columns
    7. Macro 37: Deleting Blank Rows
    8. Macro 38: Deleting Blank Columns
    9. Macro 39: Select and Format All Formulas in a Workbook
    10. Macro 40: Find and Select the First Blank Row or Column
    11. Macro 41: Apply Alternate Color Banding
    12. Macro 42: Sort a Range on Double-Click
    13. Macro 43: Limit Range Movement to a Particular Area
    14. Macro 44: Dynamically Set the Print Area of a Worksheet
  9. Part V: Working with Data
    1. Macro 45: Copy and Paste a Range
    2. Macro 46: Convert All Formulas in a Range to Values
    3. Macro 47: Perform the Text to Columns Command on All Columns
    4. Macro 48: Convert Trailing Minus Signs
    5. Macro 49: Trim Spaces from All Cells in a Range
    6. Macro 50: Truncate ZIP Codes to the Left Five
    7. Macro 51: Padding Cells with Zeros
    8. Macro 52: Replace Blanks Cells with a Value
    9. Macro 53: Append Text to the Left or Right of Your Cells
    10. Macro 54: Create a Super Data Cleanup Macro
    11. Macro 55: Clean Up Non-Printing Characters
    12. Macro 56: Highlight Duplicates in a Range of Data
    13. Macro 57: Hide All Rows but Those Containing Duplicate Data
    14. Macro 58: Selectively Hide AutoFilter Drop-down Arrows
    15. Macro 59: Copy Filtered Rows to a New Workbook
    16. Macro 60: Create a New Sheet for Each Item in an AutoFilter
    17. Macro 61: Show Filtered Columns in the Status Bar
  10. Part VI: Working with PivotTables
    1. Macro 62: Create a Backwards-Compatible PivotTable
    2. Macro 63: Refresh All PivotTables Workbook
    3. Macro 64: Create a PivotTable Inventory Summary
    4. Macro 65: Make All PivotTables Use the Same Pivot Cache
    5. Macro 66: Hide All Subtotals in a PivotTable
    6. Macro 67: Adjust All Pivot Data Field Titles
    7. Macro 68: Set All Data Items to Sum
    8. Macro 69: Apply Number Formatting for All Data Items
    9. Macro 70: Sort All Fields in Alphabetical Order
    10. Macro 71: Apply Custom Sort to Data Items
    11. Macro 72: Apply PivotTable Restrictions
    12. Macro 73: Apply Pivot Field Restrictions
    13. Macro 74: Automatically Delete Pivot Table Drill-Down Sheets
    14. Macro 75: Print Pivot Table for Each Report Filter Item
    15. Macro 76: Create New Workbook for Each Report Filter Item
    16. Macro 77: Transpose Entire Data Range with a PivotTable
  11. Part VII: Manipulating Charts with Macros
    1. Macro 78: Resize All Charts on a Worksheet
    2. Macro 79: Align a Chart to a Specific Range
    3. Macro 80: Create a Set of Disconnected Charts
    4. Macro 81: Print All Charts on a Worksheet
    5. Macro 82: Label First and Last Chart Points
    6. Macro 83: Color Chart Series to Match Source Cell Colors
    7. Macro 84: Color Chart Data Points to Match Source Cell Colors
  12. Part VIII: E-Mailing from Excel
    1. Macro 85: Mailing the Active Workbook as an Attachment
    2. Macro 86: Mailing a Specific Range as Attachment
    3. Macro 87: Mailing a Single Sheet as an Attachment
    4. Macro 88: Send Mail with a Link to Our Workbook
    5. Macro 89: Mailing All E-Mail Addresses in Our Contact List
    6. Macro 90: Saving All Attachments to a Folder
    7. Macro 91: Saving Certain Attachments to a Folder
  13. Part IX: Integrating Excel and Other Office Applications
    1. Macro 92: Running an Access Query from Excel
    2. Macro 93: Running an Access Macro from Excel
    3. Macro 94: Opening an Access Report from Excel
    4. Macro 95: Opening an Access Form from Excel
    5. Macro 96: Compacting an Access Database from Excel
    6. Macro 97: Sending Excel Data to a Word Document
    7. Macro 98: Simulating Mail Merge with a Word Document
    8. Macro 99: Sending Excel Data to a PowerPoint Presentation
    9. Macro 100: Sending All Excel Charts to a PowerPoint Presentation
    10. Macro 101: Convert a Workbook into a PowerPoint Presentation

Product information

  • Title: 101 Ready-To-Use Excel Macros
  • Author(s): Michael Alexander, John Walkenbach
  • Release date: June 2012
  • Publisher(s): For Dummies
  • ISBN: 9781118330685