Excel Macros For Dummies (For Dummies (Computer/Tech))

Save time and become an Excel wizard with the world's leading Excel macro guide Do you love Excel and all the things you can do with it, but wish you could just work…faster? Excel macros—automated workflows that save you time and energy—might be just what you need. In Excel Macros For Dummies, you'll learn over 70 of the most productive, time-saving macros in less time than it takes to back up the files on your computer! Every chapter in the book gives you practical info and exercises you can put to work immediately, alongside step-by-step instructions and guidance on how to customize Excel to fit your every need. Inside, you'll find: Automations that take your Excel productivity to the next level, and beyond Fully updated macros compatible with the newest version of Excel included in Microsoft 365 Careful explanations of the basics as well as tips for the advanced user With something for everyone, Excel Macros For Dummies is the productivity supercharger you've been waiting for. Grab a copy today! Title Page 5 Copyright Page 6 Table of Contents 9 Introduction 15 About This Book 16 Foolish Assumptions 17 Icons Used in This Book 17 Beyond the Book 17 Where to Go from Here 18 Part 1 Holy Macro Batman! 21 Chapter 1 Macro Fundamentals 23 Choosing to Use a Macro 23 Macro Recording Basics 24 Examining the macro 26 Editing the macro 27 Testing the macro 28 Comparing Absolute and Relative Macro Recording 28 Recording macros with absolute references 28 Recording macros with relative references 31 Understanding Macro Security 33 Macro-enabled file extensions 34 Trusted documents 34 Trusted locations 34 Storing and Running Macros 35 Storing macros in your Personal Macro Workbook 36 Assigning a macro to a button and other form controls 36 Placing a macro on the Quick Access Toolbar 38 Exploring Macro Examples 39 Building navigation buttons 39 Dynamically rearranging PivotTable data 40 Offering one-touch reporting options 41 Chapter 2 Getting Cozy with the Visual Basic Editor 43 Working in the Visual Basic Editor 43 The VBE menu bar 44 The VBE toolbars 45 The Project Explorer 45 The Code pane 45 The Immediate window 45 Working with the Project Explorer 46 Adding a new VBA module 46 Removing a VBA module 48 Working with a Code Pane 48 Minimizing and maximizing windows 48 Getting VBA code into a module 49 Customizing the VBE 52 The Editor tab 52 The Editor Format tab 54 The General tab 55 The Docking tab 56 Chapter 3 The Anatomy of Macros 57 A Brief Overview of the Excel Object Model 57 Understanding objects 58 Understanding collections 59 Understanding properties 59 Understanding methods 60 A Brief Look at Variables 60 The common variable types 61 Understanding Event Procedures 63 Worksheet events 63 Workbook events 65 Error Handling in a Nutshell 66 On Error GoTo SomeLabel 67 On Error Resume Next 68 On Error GoTo 0 69 Part 2 Making Short Work of Workbook Tasks 71 Chapter 4 Working with Workbooks 73 Installing Macros 74 Event macros 74 Personal Macro Workbook 74 Standard macros 75 Creating a New Workbook from Scratch 75 Saving a Workbook when a Particular Cell Is Changed 77 Saving a Workbook before Closing 79 Protecting a Worksheet on Workbook Close 81 Unprotecting a Worksheet 82 Opening a Workbook to a Specific Tab 83 Opening a Specific Workbook Defined by the User 84 Determining Whether a Workbook Is Already Open 86 Determining Whether a Workbook Exists in a Directory 88 Closing All Workbooks at Once 90 Printing All Workbooks in a Directory 91 Preventing the Workbook from Closing Until a Cell Is Populated 92 Creating a Backup of the Current Workbook with Today’s Date 94 Chapter 5 Working with Worksheets 95 Installing Macros 95 Event macros 96 Personal Macro Workbook 96 Standard macros 97 Adding and Naming a New Worksheet 97 Deleting All but the Active Worksheet 98 Hiding All but the Active Worksheet 100 Unhiding All Worksheets in a Workbook 101 Moving Worksheets Around 102 Sorting Worksheets by Name 104 Grouping Worksheets by Color 106 Copying a Worksheet to a New Workbook 108 Creating a New Workbook for Each Worksheet 108 Printing Specified Worksheets 110 Protecting All Worksheets 111 Unprotecting All Worksheets 112 Creating a Table of Contents for Your Worksheets 114 Zooming In and Out of a Worksheet with Double-Click 117 Highlighting the Active Row and Column 118 Part 3 One-Touch Data Manipulation 121 Chapter 6 Feeling at Home on the Range 123 Installing Macros 124 Event macros 124 Personal Macro Workbook 124 Standard macros 125 Selecting and Formatting a Range 125 Creating and Selecting Named Ranges 127 Looping Through a Range of Cells 130 Inserting Blank Rows in a Range 131 Unhiding All Rows and Columns 133 Deleting Blank Rows 133 Deleting Blank Columns 135 Limiting Range Movement to a Particular Area 136 Selecting and Formatting All Formulas in a Workbook 137 Finding and Selecting the First Blank Row or Column 140 Chapter 7 Manipulating Data with Macros 143 Installing Macros 144 Event macros 144 Personal Macro Workbook 145 Standard macros 145 Copying and Pasting a Range 145 Converting All Formulas in a Range to Values 146 Text to Columns on All Columns 148 Converting Trailing Minus Signs 151 Trimming Spaces from All Cells in a Range 153 Truncating Zip Codes to the Left Five 155 Padding Cells with Zeros 157 Replacing Blanks Cells with a Value 159 Appending Text to the Left or Right of Your Cells 161 Cleaning Up Non-Printing Characters 163 Highlighting Duplicates in a Range of Data 165 Hiding All but Rows Containing Duplicate Data 166 Selectively Hiding AutoFilter Drop-down Arrows 169 Copying Filtered Rows to a New Workbook 171 Showing Filtered Columns in the Status Bar 172 Part 4 Macro-Charging Reports and Emails 177 Chapter 8 Automating Common Reporting Tasks 179 Installing Macros 180 Event macros 180 Personal Macro Workbook 180 Standard macros 181 Refreshing All PivotTables in a Workbook 181 Creating a List of PivotTables 183 Adjusting All Pivot Data Field Titles 186 Setting All Data Items to Sum 187 Applying Number Formatting for All Data Items 189 Sorting All Fields in Alphabetical Order 192 Applying a Custom Sort to Data Items 193 Applying PivotTable Restrictions 194 Applying Pivot Field Restrictions 196 Automatically Deleting PivotTable Drill-Down Sheets 198 Printing a PivotTable for Each Report Filter Item 201 Creating a New Workbook for Each Report Filter Item 203 Resizing All Charts on a Worksheet 206 Aligning a Chart to a Specific Range 207 Creating a Set of Disconnected Charts 209 Printing All Charts on a Worksheet 210 Chapter 9 Sending Emails from Excel 213 Installing Macros 214 Event macros 214 Personal Macro Workbook 214 Standard macros 215 Mailing the Active Workbook as an Attachment 215 Mailing a Specific Range as an Attachment 217 Mailing a Single Sheet as an Attachment 219 Sending Mail with a Link to Your Workbook 221 Mailing All Email Addresses in Your Contact List 223 Saving All Attachments to a Folder 225 Saving Certain Attachments to a Folder 227 Chapter 10 Wrangling External Data with Macros 231 Working with External Data Connections 231 Manually creating a connection 232 Manually editing data connections 235 Using Macros to Create Dynamic Connections 236 Iterating through All Connections in a Workbook 238 Using ADO and VBA to Pull External Data 240 Understanding ADO syntax 240 Using ADO in a macro 242 Working with Text Files 245 Opening a text file 245 Reading the opened text file 246 A practical example: Logging workbook usage in a text file 246 A practical example: Importing a text file to a range 247 Part 5 The Part of Tens 249 Chapter 11 Ten Handy Visual Basic Editor Tips 251 Applying Block Comments 252 Copying Multiple Lines of Code at Once 253 Jumping between Modules and Procedures 254 Teleporting to Your Functions 254 Staying in the Right Procedure 255 Stepping through Your Code 255 Stepping to a Specific Line in Your Code 256 Stopping Your Code at a Predefined Point 257 Seeing the Beginning and End of Variable Values 258 Turning Off Auto Syntax Check 259 Chapter 12 Ten Places to Turn for Macro Help 261 Let Excel Write the Macro for You 262 Use the VBA Help Files 262 Pilfer Code from the Internet 262 Leverage User Forums 263 Visit Expert Blogs 264 Mine YouTube for Video Training 265 Attend Live and Online Training Classes 265 Learn from the Microsoft Office Dev Center 265 Dissect the Other Excel Files in your Organization 266 Ask Your Local Excel Genius 266 Chapter 13 Ten Ways to Speed Up Your Macros 267 Halt Calculations 267 Disable Screen Updating 268 Turn Off Status Bar Updates 269 Tell Excel to Ignore Events 270 Hide Page Breaks 271 Suspend PivotTable Updates 271 Steer Clear of Copy and Paste 272 Use the With Statement 273 Don’t Explicitly Select Objects 274 Avoid Excessive Trips to the Worksheet 275 Index 277 EULA 307 Save time and become an Excel wizard with the world's leading Excel macro guide Do you love Excel and all the things you can do with it, but wish you could just work...faster? Excel macros—automated workflows that save you time and energy—might be just what you need. In Excel Macros For Dummies , you'll learn over 70 of the most productive, time-saving macros in less time than it takes to back up the files on your computer! Every chapter in the book gives you practical info and exercises you can put to work immediately, alongside step-by-step instructions and guidance on how to customize Excel to fit your every need. Inside, you'll find: Automations that take your Excel productivity to the next level, and beyond Fully updated macros compatible with the newest version of Excel included in Microsoft 365 Careful explanations of the basics as well as tips for the advanced user With something for everyone, Excel Macros For Dummies is the productivity supercharger you've been waiting for. Grab a copy today!