book

Excel Macros For Dummies, 3rd Edition

Name: Excel Macros For Dummies, 3rd Edition
Author: Dick Kusleika
ISBN: 9781119844433

by Dick Kusleika

March 2022

Beginner

304 pages

6h 19m

English

For Dummies

Read now

Unlock full access

Cover
Title Page
Copyright
Introduction
About This BookFoolish AssumptionsIcons Used in This BookBeyond the BookWhere to Go from Here
Part 1: Holy Macro Batman!
Chapter 1: Macro Fundamentals
Choosing to Use a MacroMacro Recording BasicsComparing Absolute and Relative Macro RecordingUnderstanding Macro SecurityStoring and Running MacrosExploring Macro Examples
Chapter 2: Getting Cozy with the Visual Basic Editor
Working in the Visual Basic EditorWorking with the Project ExplorerWorking with a Code PaneCustomizing the VBE
Chapter 3: The Anatomy of Macros
A Brief Overview of the Excel Object ModelA Brief Look at VariablesUnderstanding Event ProceduresError Handling in a Nutshell
Part 2: Making Short Work of Workbook Tasks
Chapter 4: Working with Workbooks
Installing MacrosCreating a New Workbook from ScratchSaving a Workbook when a Particular Cell Is ChangedSaving a Workbook before ClosingProtecting a Worksheet on Workbook CloseUnprotecting a WorksheetOpening a Workbook to a Specific TabOpening a Specific Workbook Defined by the UserDetermining Whether a Workbook Is Already OpenDetermining Whether a Workbook Exists in a DirectoryClosing All Workbooks at OncePrinting All Workbooks in a DirectoryPreventing the Workbook from Closing Until a Cell Is PopulatedCreating a Backup of the Current Workbook with Today’s Date
Chapter 5: Working with Worksheets
Installing MacrosAdding and Naming a New WorksheetDeleting All but the Active WorksheetHiding All but the Active WorksheetUnhiding All Worksheets in a WorkbookMoving Worksheets AroundSorting Worksheets by NameGrouping Worksheets by ColorCopying a Worksheet to a New WorkbookCreating a New Workbook for Each WorksheetPrinting Specified WorksheetsProtecting All WorksheetsUnprotecting All WorksheetsCreating a Table of Contents for Your WorksheetsZooming In and Out of a Worksheet with Double-ClickHighlighting the Active Row and Column

Part 3: One-Touch Data Manipulation
Chapter 6: Feeling at Home on the Range
Installing MacrosSelecting and Formatting a RangeCreating and Selecting Named RangesLooping Through a Range of CellsInserting Blank Rows in a RangeUnhiding All Rows and ColumnsDeleting Blank RowsDeleting Blank ColumnsLimiting Range Movement to a Particular AreaSelecting and Formatting All Formulas in a WorkbookFinding and Selecting the First Blank Row or Column
Chapter 7: Manipulating Data with Macros
Installing MacrosCopying and Pasting a RangeConverting All Formulas in a Range to ValuesText to Columns on All ColumnsConverting Trailing Minus SignsTrimming Spaces from All Cells in a RangeTruncating Zip Codes to the Left FivePadding Cells with ZerosReplacing Blanks Cells with a ValueAppending Text to the Left or Right of Your CellsCleaning Up Non-Printing CharactersHighlighting Duplicates in a Range of DataHiding All but Rows Containing Duplicate DataSelectively Hiding AutoFilter Drop-down ArrowsCopying Filtered Rows to a New WorkbookShowing Filtered Columns in the Status Bar
Part 4: Macro-Charging Reports and Emails
Chapter 8: Automating Common Reporting Tasks
Installing MacrosRefreshing All PivotTables in a WorkbookCreating a List of PivotTablesAdjusting All Pivot Data Field TitlesSetting All Data Items to SumApplying Number Formatting for All Data ItemsSorting All Fields in Alphabetical OrderApplying a Custom Sort to Data ItemsApplying PivotTable RestrictionsApplying Pivot Field RestrictionsAutomatically Deleting PivotTable Drill-Down SheetsPrinting a PivotTable for Each Report Filter ItemCreating a New Workbook for Each Report Filter ItemResizing All Charts on a WorksheetAligning a Chart to a Specific RangeCreating a Set of Disconnected ChartsPrinting All Charts on a Worksheet
Chapter 9: Sending Emails from Excel
Installing MacrosMailing the Active Workbook as an AttachmentMailing a Specific Range as an AttachmentMailing a Single Sheet as an AttachmentSending Mail with a Link to Your WorkbookMailing All Email Addresses in Your Contact ListSaving All Attachments to a FolderSaving Certain Attachments to a Folder
Chapter 10: Wrangling External Data with Macros
Working with External Data ConnectionsUsing Macros to Create Dynamic ConnectionsIterating through All Connections in a WorkbookUsing ADO and VBA to Pull External DataWorking with Text Files
Part 5: The Part of Tens
Chapter 11: Ten Handy Visual Basic Editor Tips
Applying Block CommentsCopying Multiple Lines of Code at OnceJumping between Modules and ProceduresTeleporting to Your FunctionsStaying in the Right ProcedureStepping through Your CodeStepping to a Specific Line in Your CodeStopping Your Code at a Predefined PointSeeing the Beginning and End of Variable ValuesTurning Off Auto Syntax Check
Chapter 12: Ten Places to Turn for Macro Help
Let Excel Write the Macro for YouUse the VBA Help FilesPilfer Code from the InternetLeverage User ForumsVisit Expert BlogsMine YouTube for Video TrainingAttend Live and Online Training ClassesLearn from the Microsoft Office Dev CenterDissect the Other Excel Files in your OrganizationAsk Your Local Excel Genius
Chapter 13: Ten Ways to Speed Up Your Macros
Halt CalculationsDisable Screen UpdatingTurn Off Status Bar UpdatesTell Excel to Ignore EventsHide Page BreaksSuspend PivotTable UpdatesSteer Clear of Copy and PasteUse the With StatementDon’t Explicitly Select ObjectsAvoid Excessive Trips to the Worksheet
Index
About the Author
Advertisement Page
Connect with Dummies
End User License Agreement

Content preview from Excel Macros For Dummies, 3rd Edition

Chapter 5

Working with Worksheets

IN THIS CHAPTER

Adding, naming, copying, and deleting worksheets

Hiding and unhiding, moving, sorting, and grouping worksheets

Protecting and unprotecting all worksheets

Printing and creating a table of contents for your worksheets

Zooming in and out of a worksheet

Excel analysts often need to automate tasks related to worksheets. Whether it is unhiding all sheets in a workbook, or printing all sheets at the same time, many tasks can be automated to save time and gain efficiencies. This chapter covers some of the more useful macros related to worksheets.

Installing Macros

You can install the macros in this chapter in three places depending on what kind of macro it is and how available you want the macro to be. Event macros are put into the module of the object the event is tied to. Non-event macros go into a standard module and can be in the workbook in which they are meant to run or in a workbook called the Personal Macro Workbook if you want to ...

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: 9781119844433Purchase Link

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Excel Macros For Dummies, 3rd Edition

by Dick Kusleika

Working with Worksheets

Installing Macros

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.