Hjem

Introduction 1 About This Book 2 Foolish Assumptions 2 Icons Used in This Book 3 Beyond the Book 3 Where to Go from Here 4
Part 1: Holy Macro Batman! 7
Chapter 1: Macro Fundamentals 9 Why Use a Macro? 9 Macro Recording Basics 10 Examining the macro 12 Testing the macro 13 Editing the macro 13 Comparing Absolute and Relative Macro Recording 14 Recording macros with absolute references 14 Recording macros with relative references 17 Other Macro Recording Concepts 19 Macro-enabled file extensions 19 Macro security in Excel 2010 19 Trusted locations 20 Storing macros in your Personal Macro Workbook 21 Assigning a macro to a button and other form controls 22 Placing a macro on the Quick Access Toolbar 23 Examples of Macros in Action 24 Building navigation buttons 24 Dynamically rearranging PivotTable data 25 Offering one-touch reporting options 27
Chapter 2: Getting Cozy with the Visual Basic Editor 29 Working in the Visual Basic Editor 29 The VBE menu bar 30 The VBE toolbar 31 The Project window 31 The Code window 31 The Immediate window 31 Working with the Project Window 32 Adding a new VBA module 32 Removing a VBA module 33 Working with a Code Window 34 Minimizing and maximizing windows 34 Getting VBA code into a module 35 Customizing the VBA Environment 37 The Editor tab 38 The Auto Syntax Check option 38 The Require Variable Declaration option 38 The Editor Format tab 40 The General tab 41 The Docking tab 42
Chapter 3: The Anatomy of Macros 43 A Brief Overview of the Excel Object Model 43 Understanding objects 44 Understanding collections 45 Understanding properties 45 Understanding methods 46 A Brief Look at Variables 46 The common variable types 47 Understanding Event Procedures 49 Worksheet events 49 Workbook events 51 Error Handling in a Nutshell 53 On Error GoTo SomeLabel 53 On Error Resume Next 54 On Error GoTo 0 55
Part 2: Making Short Work of Workbook Tasks 57
Chapter 4: Working with Workbooks 59 Creating a New Workbook from Scratch 60 How it works 60 How to use it 61 Saving a Workbook when a Particular Cell Is Changed 61 How it works 62 How to use it 63 Saving a Workbook before Closing 64 How it works 64 How to use it 65 Protecting a Worksheet on Workbook Close 66 How it works 66 How to use it 67 Unprotecting a Worksheet on Workbook Open 68 How it works 68 How to use it 68 Opening a Workbook to a Specific Tab 69 How it works 69 How to use it 70 Opening a Specific Workbook Defined by the User 70 How it works 71 How to use it 72 Determining Whether a Workbook Is Already Open 72 How it works 73 How to use it 75 Determining Whether a Workbook Exists in a Directory 75 How it works 76 How to use it 77 Closing All Workbooks at Once 77 How it works 77 How to use it 78 Printing All Workbooks in a Directory 78 How it works 79 How to use it 80 Preventing the Workbook from Closing Until a Cell Is Populated 80 How it works 80 How to use it 81 Creating a Backup of the Current Workbook with Today''s Date 82 How it works 82 How to use it 83
Chapter 5: Working with Worksheets 85 Adding and Naming a New Worksheet 85 How it works 85 How to use it 86 Deleting All but the Active Worksheet 87 How it works 87 How to use it 88 Hiding All but the Active Worksheet 88 How it works 89 How to use it 90 Unhiding All Worksheets in a Workbook 90 How it works 90 How to use it 91 Moving Worksheets Around 92 How it works 92 How to use it 93 Sorting Worksheets by Name 93 How it works 93 How to use it 95 Grouping Worksheets by Color 95 How it works 96 How to use it 97 Copying a Worksheet to a New Workbook 98 How it works 98 How to use it 98 Creating a New Workbook for Each Worksheet 99 How it works 99 How to use it 100 Printing Specified Worksheets 101 How it works 101 How to use it 101 Protecting All Worksheets 102 How it works 102 How to use it 103 Unprotecting All Worksheets 104 How it works 104 How to use it 105 Creating a Table of Contents for Your Worksheets 106 How it works 106 How to use it 109 Zooming In and Out of a Worksheet with Double-Click 109 How it works 109 How to use it 110 Highlighting the Active Row and Column 110 How it works 111 How to use it 112
Part 3: One-Touch Data Manipulation 113
Chapter 6: Feeling at Home on the Range 115 Selecting and Formatting a Range 116 How it works 116 How to use it 117 Creating and Selecting Named Ranges 118 How it works 120 How to use it 120 Enumerating Through a Range of Cells 121 How it works 121 How to use it 122 Inserting Blank Rows in a Range 122 How it works 122 How to use it 124 Unhiding All Rows and Columns 124 How it works 124 How to use it 124 Deleting Blank Rows 125 How it works 125 How to use it 126 Deleting Blank Columns 127 How it works 127 How to use it 129 Limiting Range Movement to a Particular Area 129 How it works 129 How to use it 130 Selecting and Formatting All Formulas in a Workbook 131 How it works 131 How to use it 133 Finding and Selecting the First Blank Row or Column 133 How it works 134 How to use it 136
Chapter 7: Manipulating Data with Macros 137 Copying and Pasting a Range 138 How it works 138 How to use it 139 Converting All Formulas in a Range to Values 139 How it works 139 How to use it 141 Text to Columns on All Columns 141 How it works 142 How to use it 144 Converting Trailing Minus Signs 144 How it works 144 How to use it 146 Trimming Spaces from All Cells in a Range 147 How it works 147 How to use it 148 Truncating ZIP Codes to the Left Five 149 How it works 149 How to use it 151 Padding Cells with Zeros 151 How it works 152 How to use it 154 Replacing Blanks Cells with a Value 154 How it works 154 How to use it 156 Appending Text to the Left or Right of Your Cells 156 How it works 156 How to use it 158 Cleaning Up Non-Printing Characters 158 How it works 159 How to use it 160 Highlighting Duplicates in a Range of Data 160 How it works 161 How to use it 162 Hiding All but Rows Containing Duplicate Data 162 How it works 163 How to use it 165 Selectively Hiding AutoFilter Drop-down Arrows 165 How it works 165 How to use it 167 Copying Filtered Rows to a New Workbook 167 How it works 167 How to use it 168 Showing Filtered Columns in the Status Bar 168 How it works 169 How to use it 171
Part 4: Macro-Charging Reports and Emails 173
Chapter 8: Automating Common Reporting Tasks 175 Refreshing All PivotTables in a Workbook 176 How it works 176 How to use it 177 Creating a PivotTable Inventory Summary 177 How it works 178 How to use it 180 Adjusting All Pivot Data Field Titles 181 How it works 181 How to use it 183 Setting All Data Items to Sum 183 How it works 183 How to use it 185 Applying Number Formatting for All Data Items 185 How it works 186 How to use it 189 Sorting All Fields in Alphabetical Order 189 How it works 189 How to use it 190 Applying a Custom Sort to Data Items 191 How it works 191 How to use it 192 Applying PivotTable Restrictions 192 How it works 192 How to use it 194 Applying Pivot Field Restrictions 194 How it works 194 How to use it 196 Automatically Deleting PivotTable Drill-Down Sheets 196 How it works 196 How to use it 198 Printing a PivotTable for Each Report Filter Item 200 How it works 200 How to use it 202 Creating a New Workbook for Each Report Filter Item 202 How it works 203 How to use it 205 Resizing All Charts on a Worksheet 205 How it works 205 How to use it 207 Aligning a Chart to a Specific Range 207 How it works 207 How to use it 209 Creating a Set of Disconnected Charts 209 How it works 209 How to use it 211 Printing All Charts on a Worksheet 211 How it works 211 How to use it 212
Chapter 9: Sending Emails from Excel 213 Mailing the Active Workbook as an Attachment 214 How it works 214 How to use it 215 Mailing a Specific Range as an Attachment 216 How it works 216 How to use it 218 Mailing a Single Sheet as an Attachment 218 How it works 218 How to use it 220