Excel Macros For Dummies, 3rd Edition

Table of contents

1. Cover
2. Title Page
3. Copyright
4. Introduction
   1. About This Book
   2. Foolish Assumptions
   3. Icons Used in This Book
   4. Beyond the Book
   5. Where to Go from Here
5. Part 1: Holy Macro Batman!
   1. Chapter 1: Macro Fundamentals
      1. Choosing to Use a Macro
      2. Macro Recording Basics
      3. Comparing Absolute and Relative Macro Recording
      4. Understanding Macro Security
      5. Storing and Running Macros
      6. Exploring Macro Examples
   2. Chapter 2: Getting Cozy with the Visual Basic Editor
      1. Working in the Visual Basic Editor
      2. Working with the Project Explorer
      3. Working with a Code Pane
      4. Customizing the VBE
   3. Chapter 3: The Anatomy of Macros
      1. A Brief Overview of the Excel Object Model
      2. A Brief Look at Variables
      3. Understanding Event Procedures
      4. Error Handling in a Nutshell
6. Part 2: Making Short Work of Workbook Tasks
   1. Chapter 4: Working with Workbooks
      1. Installing Macros
      2. Creating a New Workbook from Scratch
      3. Saving a Workbook when a Particular Cell Is Changed
      4. Saving a Workbook before Closing
      5. Protecting a Worksheet on Workbook Close
      6. Unprotecting a Worksheet
      7. Opening a Workbook to a Specific Tab
      8. Opening a Specific Workbook Defined by the User
      9. Determining Whether a Workbook Is Already Open
      10. Determining Whether a Workbook Exists in a Directory
      11. Closing All Workbooks at Once
      12. Printing All Workbooks in a Directory
      13. Preventing the Workbook from Closing Until a Cell Is Populated
      14. Creating a Backup of the Current Workbook with Today’s Date
   2. Chapter 5: Working with Worksheets
      1. Installing Macros
      2. Adding and Naming a New Worksheet
      3. Deleting All but the Active Worksheet
      4. Hiding All but the Active Worksheet
      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 New 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
7. Part 3: One-Touch Data Manipulation
   1. Chapter 6: Feeling at Home on the Range
      1. Installing Macros
      2. Selecting and Formatting a Range
      3. Creating and Selecting Named Ranges
      4. Looping Through a Range of Cells
      5. Inserting Blank Rows in a Range
      6. Unhiding All Rows and Columns
      7. Deleting Blank Rows
      8. Deleting Blank Columns
      9. Limiting Range Movement to a Particular Area
      10. Selecting and Formatting All Formulas in a Workbook
      11. Finding and Selecting the First Blank Row or Column
   2. Chapter 7: Manipulating Data with Macros
      1. Installing Macros
      2. Copying and Pasting a Range
      3. Converting All Formulas in a Range to Values
      4. Text to Columns on All Columns
      5. Converting Trailing Minus Signs
      6. Trimming Spaces from All Cells in a Range
      7. Truncating Zip Codes to the Left Five
      8. Padding Cells with Zeros
      9. Replacing Blanks Cells with a Value
      10. Appending Text to the Left or Right of Your Cells
      11. Cleaning Up Non-Printing Characters
      12. Highlighting Duplicates in a Range of Data
      13. Hiding All but Rows Containing Duplicate Data
      14. Selectively Hiding AutoFilter Drop-down Arrows
      15. Copying Filtered Rows to a New Workbook
      16. Showing Filtered Columns in the Status Bar
8. Part 4: Macro-Charging Reports and Emails
   1. Chapter 8: Automating Common Reporting Tasks
      1. Installing Macros
      2. Refreshing All PivotTables in a Workbook
      3. Creating a List of PivotTables
      4. Adjusting All Pivot Data Field Titles
      5. Setting All Data Items to Sum
      6. Applying Number Formatting for All Data Items
      7. Sorting All Fields in Alphabetical Order
      8. Applying a Custom Sort to Data Items
      9. Applying PivotTable Restrictions
      10. Applying Pivot Field Restrictions
      11. Automatically Deleting PivotTable Drill-Down Sheets
      12. Printing a PivotTable for Each Report Filter Item
      13. Creating a New Workbook for Each Report Filter Item
      14. Resizing All Charts on a Worksheet
      15. Aligning a Chart to a Specific Range
      16. Creating a Set of Disconnected Charts
      17. Printing All Charts on a Worksheet
   2. Chapter 9: Sending Emails from Excel
      1. Installing Macros
      2. Mailing the Active Workbook as an Attachment
      3. Mailing a Specific Range as an Attachment
      4. Mailing a Single Sheet as an Attachment
      5. Sending Mail with a Link to Your Workbook
      6. Mailing All Email Addresses in Your Contact List
      7. Saving All Attachments to a Folder
      8. Saving Certain Attachments to a Folder
   3. Chapter 10: Wrangling External Data with Macros
      1. Working with External Data Connections
      2. Using Macros to Create Dynamic Connections
      3. Iterating through All Connections in a Workbook
      4. Using ADO and VBA to Pull External Data
      5. Working with Text Files
9. Part 5: The Part of Tens
   1. Chapter 11: Ten Handy Visual Basic Editor Tips
      1. Applying Block Comments
      2. Copying Multiple Lines of Code at Once
      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 Line in Your Code
      8. Stopping Your Code at a Predefined Point
      9. Seeing the Beginning and End of Variable Values
      10. Turning Off Auto Syntax Check
   2. Chapter 12: Ten Places to Turn for Macro Help
      1. Let Excel Write the Macro for You
      2. Use the VBA Help Files
      3. Pilfer Code from the Internet
      4. Leverage User Forums
      5. Visit Expert Blogs
      6. Mine YouTube for Video Training
      7. Attend Live and Online Training Classes
      8. Learn from the Microsoft Office Dev Center
      9. Dissect the Other Excel Files in your Organization
      10. Ask Your Local Excel Genius
   3. Chapter 13: Ten Ways to Speed Up Your Macros
      1. Halt Calculations
      2. Disable Screen Updating
      3. Turn Off Status Bar Updates
      4. Tell Excel to Ignore Events
      5. Hide Page Breaks
      6. Suspend PivotTable Updates
      7. Steer Clear of Copy and Paste
      8. Use the With Statement
      9. Don’t Explicitly Select Objects
      10. Avoid Excessive Trips to the Worksheet
10. Index
11. About the Author
12. Advertisement Page
13. Connect with Dummies
14. End User License Agreement