Cover image for Excel Macros For Dummies

Excel Macros For Dummies

Author Michael Alexander
Release Date: 2015/06/01
ISBN: 9781119089346
Topic:
0%
26
Chapters
0-1
Hours read
0k
Total Words
    Start Reading Now    
       Add to Wishlist       
View table of contents

Book Description

Ready-made Excel macros that will streamline your workflow

Excel Macros For Dummies helps you save time, automate, and be more productive, even with no programming experience at all. Each chapter offers practical macros that you can implement right away, with practical exercises that extend your knowledge and help you understand the mechanics at work. You'll find the most effective Excel macros for solving common problems, and explanations of why and where to use each one, plus invaluable guidance and step-by-step instruction for applying them effectively. Learn how to customize your applications to look and work exactly the way you want them to, with simple, friendly walk-throughs that directly apply to real-world tasks. Follow it through from start to finish, or quickly look up problems as they occur; the book's clear layout and organization makes it an irreplaceable desk reference, and all macro code is available for download from the companion website.

Microsoft Excel is the world's leading spreadsheet application, and it supports VBA macros that allow you to customize the program and automate many common tasks. This book helps you take advantage of macros to get more done, and get it done better.

  • Grasp the fundamentals of VBA and macros
  • Work with workbooks, worksheets, and ranges
  • Clean data, automate reporting, and send email from Excel
  • Use tips and tricks that streamline your workflow

If you have an Excel problem, there's a macro to solve it. You don't need to be a programmer, and you don't need to spend months learning code. Excel Macros For Dummies gives you the "recipes" you need, and the knowledge to apply them effectively.

Table of Contents

  1. Cover
    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
3.22.241.60