book

101 Ready-To-Use Excel Macros

Name: 101 Ready-To-Use Excel Macros
ISBN: 9781118330685

by Michael Alexander, John Walkenbach

June 2012

Beginner

336 pages

6h 6m

English

For Dummies

Read now

Unlock full access

Cover
Table of Contents
Title Page
Introduction
Part I: Getting Started with Excel Macros
Chapter 1: Getting Started with Excel Macros
Becoming Familiar with Macro Recording BasicsComparing Absolute and Relative Macro RecordingLooking at Other Macro Recording ConceptsWorking in the Visual Basic EditorWorking with the Project windowWorking with a Code WindowCustomizing the VBA EnvironmentDiscovering the Excel Object ModelTaking a Brief Look at VariablesAbout the Macros in This Book
Part II: Working with Workbooks
Macro 1: Creating a New Workbook from Scratch
Macro 2: Saving a Workbook When a Particular Cell Is ChangedMacro 3: Saving a Workbook Before ClosingMacro 4: Protect a Worksheet on Workbook CloseMacro 5: Unprotect a Worksheet on Workbook OpenMacro 6: Open a Workbook to a Specific TabMacro 7: Opening a Specific Workbook Defined by the UserMacro 8: Determine Whether a Workbook Is Already OpenMacro 9: Determine Whether a Workbook Exists in a DirectoryMacro 10: Refresh All Data Connections in Workbook on OpenMacro 11: Close All Workbooks at OnceMacro 12: Open All Workbooks in a Directory Macro 13: Print All Workbooks in a DirectoryMacro 14: Preventing the Workbook from Closing Until a Cell Is PopulatedMacro 15: Create a Backup of a Current Workbook with Today's Date
Part III: Automating Worksheet Tasks with Macros
Macro 16: Add and Name a New Worksheet
Macro 17: Delete All but the Active WorksheetMacro 18: Hide All but the Active WorksheetMacro 19: Unhide All Worksheets in a WorkbookMacro 20: Moving Worksheets AroundMacro 21: Sort Worksheets by NameMacro 22: Group Worksheets by ColorMacro 23: Copy a Worksheet to a New WorkbookMacro 24: Create a New Workbook for Each WorksheetMacro 25: Print Specified WorksheetsMacro 26: Protect All WorksheetsMacro 27: Unprotect All WorksheetsMacro 28: Create a Table of Contents for Your WorksheetsMacro 29: Zooming In and Out of a Worksheet with Double-ClickMacro 30: Highlight the Active Row and Column

Part IV: Selecting and Modifying Ranges
Macro 31: Selecting and Formatting a Range
Macro 32: Creating and Selecting Named RangesMacro 33: Enumerating Through a Range of CellsMacro 34: Select and Format All Named RangesMacro 35: Inserting Blank Rows in a RangeMacro 36: Unhide All Rows and ColumnsMacro 37: Deleting Blank RowsMacro 38: Deleting Blank ColumnsMacro 39: Select and Format All Formulas in a WorkbookMacro 40: Find and Select the First Blank Row or ColumnMacro 41: Apply Alternate Color BandingMacro 42: Sort a Range on Double-ClickMacro 43: Limit Range Movement to a Particular AreaMacro 44: Dynamically Set the Print Area of a Worksheet
Part V: Working with Data
Macro 45: Copy and Paste a Range
Macro 46: Convert All Formulas in a Range to ValuesMacro 47: Perform the Text to Columns Command on All ColumnsMacro 48: Convert Trailing Minus SignsMacro 49: Trim Spaces from All Cells in a RangeMacro 50: Truncate ZIP Codes to the Left FiveMacro 51: Padding Cells with ZerosMacro 52: Replace Blanks Cells with a ValueMacro 53: Append Text to the Left or Right of Your CellsMacro 54: Create a Super Data Cleanup MacroMacro 55: Clean Up Non-Printing CharactersMacro 56: Highlight Duplicates in a Range of DataMacro 57: Hide All Rows but Those Containing Duplicate DataMacro 58: Selectively Hide AutoFilter Drop-down ArrowsMacro 59: Copy Filtered Rows to a New WorkbookMacro 60: Create a New Sheet for Each Item in an AutoFilterMacro 61: Show Filtered Columns in the Status Bar
Part VI: Working with PivotTables
Macro 62: Create a Backwards-Compatible PivotTable
Macro 63: Refresh All PivotTables WorkbookMacro 64: Create a PivotTable Inventory SummaryMacro 65: Make All PivotTables Use the Same Pivot CacheMacro 66: Hide All Subtotals in a PivotTableMacro 67: Adjust All Pivot Data Field TitlesMacro 68: Set All Data Items to SumMacro 69: Apply Number Formatting for All Data ItemsMacro 70: Sort All Fields in Alphabetical OrderMacro 71: Apply Custom Sort to Data ItemsMacro 72: Apply PivotTable RestrictionsMacro 73: Apply Pivot Field RestrictionsMacro 74: Automatically Delete Pivot Table Drill-Down SheetsMacro 75: Print Pivot Table for Each Report Filter ItemMacro 76: Create New Workbook for Each Report Filter ItemMacro 77: Transpose Entire Data Range with a PivotTable
Part VII: Manipulating Charts with Macros
Macro 78: Resize All Charts on a Worksheet
Macro 79: Align a Chart to a Specific RangeMacro 80: Create a Set of Disconnected ChartsMacro 81: Print All Charts on a WorksheetMacro 82: Label First and Last Chart PointsMacro 83: Color Chart Series to Match Source Cell ColorsMacro 84: Color Chart Data Points to Match Source Cell Colors
Part VIII: E-Mailing from Excel
Macro 85: Mailing the Active Workbook as an Attachment
Macro 86: Mailing a Specific Range as AttachmentMacro 87: Mailing a Single Sheet as an AttachmentMacro 88: Send Mail with a Link to Our WorkbookMacro 89: Mailing All E-Mail Addresses in Our Contact ListMacro 90: Saving All Attachments to a FolderMacro 91: Saving Certain Attachments to a Folder
Part IX: Integrating Excel and Other Office Applications
Macro 92: Running an Access Query from Excel
Macro 93: Running an Access Macro from Excel Macro 94: Opening an Access Report from ExcelMacro 95: Opening an Access Form from ExcelMacro 96: Compacting an Access Database from ExcelMacro 97: Sending Excel Data to a Word DocumentMacro 98: Simulating Mail Merge with a Word DocumentMacro 99: Sending Excel Data to a PowerPoint PresentationMacro 100: Sending All Excel Charts to a PowerPoint PresentationMacro 101: Convert a Workbook into a PowerPoint Presentation

Content preview from 101 Ready-To-Use Excel Macros

Every data-oriented process has an application flow — a succession of applications that take the data from creation to end-user. Sometimes a dataset is touched by only one application, such as when you're creating a report and presenting it in Excel. In many cases, however, data is moved from a database such as Microsoft Access, analyzed and aggregated in Excel, and then distributed via a Word document, PowerPoint presentation, or even e-mail. In this Part, we look at some of the useful macros you can implement to have Excel integrate with other Office applications.

The code for this Part can be found on this book's companion website. See this book's Introduction for more on the companion website.

Macro 92: Running an Access Query from Excel

Here's a nifty macro for those of you who often copy and paste the results of your Microsoft Access queries to Excel. In this macro, you use DAO (Data Access Object) to open and run an Access query in the background and output the results into Excel.

How it works

In this macro, you point Excel to an Access database and pull data from an existing Access query. You then store that query in a Recordset object, which you can use to populate your Excel spreadsheet.

Because you are automating Access, you need to set a reference to the Microsoft Access Object Library. To do so, open the VBE in Excel and select Tools⇒References. The Reference dialog ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 9781118330685Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

101 Ready-To-Use Excel Macros

by Michael Alexander, John Walkenbach

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.