Excel Macros For Dummies

Table of contents

1. 1. Cover
   2. Introduction
      1. About This Book
      2. Foolish Assumptions
      3. Icons Used In This Book
      4. Beyond the Book
      5. Where to Go from Here
   3. Part I: Holy Macro Batman!
      1. Chapter 1: Macro Fundamentals
         1. Why Use a Macro?
         2. Macro Recording Basics
         3. Comparing Absolute and Relative Macro Recording
         4. Other Macro Recording Concepts
         5. Examples of Macros in Action
      2. Chapter 2: Getting Cozy with Visual Basic Editor
         1. Working in Visual Basic Editor
         2. Working with the Project Window
         3. Working with a Code Window
         4. Customizing the VBA Environment
      3. Chapter 3: The Anatomy of Macros
         1. An Overview of the Excel Object Model
         2. A Brief Look at Variables
         3. Understanding Event Procedures
         4. Error Handling in a Nutshell
   4. Part II: Making Short Work of Workbook Tasks
      1. Chapter 4: Working with Workbooks
         1. Creating a Workbook from Scratch
         2. Saving a Workbook when a Particular Cell Is Changed
         3. Saving a Workbook before Closing
         4. Protecting a Worksheet on Workbook Close
         5. Unprotecting a Worksheet on Workbook Open
         6. Opening a Workbook to a Specific Tab
         7. Opening a Specific Workbook Defined by the User
         8. Determining Whether a Workbook Is Already Open
         9. Determining Whether a Workbook Exists in a Directory
         10. Closing All Workbooks at Once
         11. Printing All Workbooks in a Directory
         12. Preventing the Workbook from Closing Until a Cell Is Populated
         13. Creating a Backup of a Current Workbook with Today’s Date
      2. Chapter 5: Working with Worksheets
         1. Adding and Naming a New Worksheet
         2. Deleting All but the Active Worksheet
         3. Hiding All but the Active Worksheet
         4. How the macro works
         5. Unhiding All Worksheets in a Workbook
         6. Moving Worksheets Around
         7. Sorting Worksheets by Name
         8. Grouping Worksheets by Color
         9. Copying a Worksheet to a New Workbook
         10. Creating a Workbook for Each Worksheet
         11. Printing Specified Worksheets
         12. Protecting All Worksheets
         13. Unprotecting All Worksheets
         14. Creating a Table of Contents for Your Worksheets
         15. Zooming In and Out of a Worksheet with Double-Click
         16. Highlighting the Active Row and Column
   5. Part III: One-Touch Data Manipulation
      1. Chapter 6: Feeling at Home on the Range
         1. Selecting and Formatting a Range
         2. Creating and Selecting Named Ranges
         3. Enumerating through a Range of Cells
         4. Inserting Blank Rows in a Range
         5. Unhiding All Rows and Columns
         6. Deleting Blank Rows
         7. Deleting Blank Columns
         8. Limiting Range Movement to a Particular Area
         9. Selecting and Formatting All Formulas in a Workbook
         10. Finding and Selecting the First Blank Row or Column
      2. Chapter 7: Manipulating Data with Macros
         1. Copying and Pasting a Range
         2. Converting All Formulas in a Range to Values
         3. Performing the Text to Columns Command on All Columns
         4. Converting Trailing Minus Signs
         5. Trimming Spaces from All Cells in a Range
         6. Truncating Zip Codes to the Left Five Digits
         7. Padding Cells with Zeros
         8. Replacing Blanks Cells with a Value
         9. Adding Text to the Left or Right of Your Cells
         10. Cleaning Up Nonprinting Characters
         11. Highlighting Duplicates in a Range of Data
         12. Hiding All Rows Except Rows Containing Duplicate Data
         13. Selectively Hiding AutoFilter Drop-Down Arrows
         14. Copying Filtered Rows to a New Workbook
         15. Displaying Filtered Columns in the Status Bar
   6. Part IV: Macro-Charging Reports and Emails
      1. Chapter 8: Automating Common Reporting Tasks
         1. Refreshing All Pivot Tables in a Workbook
         2. Creating a Pivot Table Inventory Summary
         3. Adjusting All Pivot Data Field Titles
         4. Setting All Data Items to Sum
         5. Applying Number Formatting to All Data Items
         6. Sorting All Fields in Alphabetical Order
         7. Applying Custom Sort to Data Items
         8. Applying Pivot Table Restrictions
         9. Applying Pivot Field Restrictions
         10. Automatically Deleting Pivot Table Drill-Down Sheets
         11. Printing a Pivot Table for Each Report Filter Item
         12. Creating a Workbook for Each Report Filter Item
         13. Resizing All Charts on a Worksheet
         14. Aligning a Chart to a Specific Range
         15. Creating a Set of Disconnected Charts
         16. Printing All Charts on a Worksheet
      2. Chapter 9: Sending Emails from Excel
         1. Mailing the Active Workbook as an Attachment
         2. Mailing a Specific Range as an Attachment
         3. Mailing a Single Sheet as an Attachment
         4. Sending Mail with a Link to Your Workbook
         5. Mailing All Email Addresses in Your Contact List
         6. Saving All Attachments to a Folder
         7. Saving Certain Attachments to a Folder
   7. Part V: The Part of Tens
      1. Chapter 10: Ten Handy Visual Basic Editor Tips
         1. Applying Block Comments
         2. Copying Multiple Lines of Code
         3. Jumping between Modules and Procedures
         4. Teleporting to Your Functions
         5. Staying in the Right Procedure
         6. Stepping through Your Code
         7. Stepping to a Specific Code Line
         8. Stopping Code at a Predefined Point
         9. Seeing Beginning and Ending Variable Values
         10. Turning Off Auto Syntax Check
      2. Chapter 11: Ten Places to Turn for Macro Help
         1. Letting Excel Write the Macro for You
         2. Using the VBA Help Files
         3. Pilfering Code from the Internet
         4. Leveraging User Forums
         5. Visiting Expert Blogs
         6. Mining YouTube for Video Training
         7. Attending Live and Online Training Classes
         8. Learning from Microsoft Office Dev Center
         9. Dissecting Other Excel Files in Your Organization
         10. Asking Your Local Excel Genius
      3. Chapter 12: Ten Ways to Speed Up Your Macros
         1. Halting Sheet Calculations
         2. Disabling Sheet Screen Updating
         3. Turning Off Status Bar Updates
         4. Telling Excel to Ignore Events
         5. Hiding Page Breaks
         6. Suspending Pivot Table Updates
         7. Steering Clear of Copy and Paste
         8. Using the With Statement
         9. Avoiding the Select Method
         10. Limiting Trips to the Worksheet
   8. About the Author
   9. Cheat Sheet
   10. Advertisement Page
   11. Connect with Dummies
   12. End User License Agreement