book

Excel Macros For Dummies

Name: Excel Macros For Dummies
Author: Michael Alexander
ISBN: 9781119089346

by Michael Alexander

June 2015

Beginner

288 pages

5h 27m

English

For Dummies

Read now

Unlock full access

Cover
Introduction
About This BookFoolish AssumptionsIcons Used In This BookBeyond the BookWhere to Go from Here
Part I: Holy Macro Batman!
Chapter 1: Macro Fundamentals
Why Use a Macro?Macro Recording BasicsComparing Absolute and Relative Macro RecordingOther Macro Recording ConceptsExamples of Macros in Action
Chapter 2: Getting Cozy with Visual Basic Editor
Working in Visual Basic EditorWorking with the Project WindowWorking with a Code WindowCustomizing the VBA Environment
Chapter 3: The Anatomy of Macros
An Overview of the Excel Object ModelA Brief Look at VariablesUnderstanding Event ProceduresError Handling in a Nutshell
Part II: Making Short Work of Workbook Tasks
Chapter 4: Working with Workbooks
Creating a Workbook from ScratchSaving a Workbook when a Particular Cell Is ChangedSaving a Workbook before ClosingProtecting a Worksheet on Workbook CloseUnprotecting a Worksheet on Workbook OpenOpening 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 a Current Workbook with Today’s Date
Chapter 5: Working with Worksheets
Adding and Naming a New WorksheetDeleting All but the Active WorksheetHiding All but the Active WorksheetHow the macro worksUnhiding All Worksheets in a WorkbookMoving Worksheets AroundSorting Worksheets by NameGrouping Worksheets by ColorCopying a Worksheet to a New WorkbookCreating a 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 III: One-Touch Data Manipulation

Chapter 6: Feeling at Home on the Range
Selecting and Formatting a RangeCreating and Selecting Named RangesEnumerating 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
Copying and Pasting a RangeConverting All Formulas in a Range to ValuesPerforming the Text to Columns Command on All ColumnsConverting Trailing Minus SignsTrimming Spaces from All Cells in a RangeTruncating Zip Codes to the Left Five DigitsPadding Cells with ZerosReplacing Blanks Cells with a ValueAdding Text to the Left or Right of Your CellsCleaning Up Nonprinting CharactersHighlighting Duplicates in a Range of DataHiding All Rows Except Rows Containing Duplicate DataSelectively Hiding AutoFilter Drop-Down ArrowsCopying Filtered Rows to a New WorkbookDisplaying Filtered Columns in the Status Bar
Part IV: Macro-Charging Reports and Emails
Chapter 8: Automating Common Reporting Tasks
Refreshing All Pivot Tables in a WorkbookCreating a Pivot Table Inventory SummaryAdjusting All Pivot Data Field TitlesSetting All Data Items to SumApplying Number Formatting to All Data ItemsSorting All Fields in Alphabetical OrderApplying Custom Sort to Data ItemsApplying Pivot Table RestrictionsApplying Pivot Field RestrictionsAutomatically Deleting Pivot Table Drill-Down SheetsPrinting a Pivot Table for Each Report Filter ItemCreating a 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
Mailing 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
Part V: The Part of Tens
Chapter 10: Ten Handy Visual Basic Editor Tips
Applying Block CommentsCopying Multiple Lines of CodeJumping between Modules and ProceduresTeleporting to Your FunctionsStaying in the Right ProcedureStepping through Your CodeStepping to a Specific Code LineStopping Code at a Predefined PointSeeing Beginning and Ending Variable ValuesTurning Off Auto Syntax Check
Chapter 11: Ten Places to Turn for Macro Help
Letting Excel Write the Macro for YouUsing the VBA Help FilesPilfering Code from the InternetLeveraging User ForumsVisiting Expert BlogsMining YouTube for Video TrainingAttending Live and Online Training ClassesLearning from Microsoft Office Dev CenterDissecting Other Excel Files in Your OrganizationAsking Your Local Excel Genius
Chapter 12: Ten Ways to Speed Up Your Macros
Halting Sheet CalculationsDisabling Sheet Screen UpdatingTurning Off Status Bar UpdatesTelling Excel to Ignore EventsHiding Page BreaksSuspending Pivot Table UpdatesSteering Clear of Copy and PasteUsing the With StatementAvoiding the Select MethodLimiting Trips to the Worksheet
About the Author
Cheat Sheet
Advertisement Page
Connect with Dummies
End User License Agreement

Content preview from Excel Macros For Dummies

Chapter 5

Working with Worksheets

In This Chapter

Adding and naming worksheets

Protecting and unprotecting worksheets

Creating a table of contents

Highlighting active rows and columns

Hiding and unhiding worksheets

Moving and sorting Worksheets

Copying worksheets to new Workbooks

Excel analysts can save time and gain efficiencies by using macros to automate tasks related to worksheets. Two common example tasks are unhiding all sheets in a workbook and printing all sheets at the same time. In this chapter, I cover some of the more useful macros related to worksheets.

Adding and Naming a New Worksheet

The chapter starts with one of the simplest worksheet-related automations you can apply with a macro: adding and naming a new worksheet.

How the macro works

When you read through the ...

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: 9781119089346

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Excel Macros For Dummies

by Michael Alexander

Working with Worksheets