ExcelTips: The Macros (Table of Contents)

ExcelTips: The Macros includes 1,310 pages of valuable macro-oriented tips. The following is the Table of Contents:

Introduction

• A Few Words about Versions
• How to Use the Macros in This Book
• Need a Good Macro Tutorial?
• Are You a Visual Learner?
• Need More Tips?
• Want to Learn About VBA Programming in Word?
• Sharing this Document

Creating and Managing Macros

• Understanding Macros
• Recording a Macro
• Relative References when Recording Macros
• Writing a Macro from Scratch
• Editing Macros
• Continuing Macro Lines
• Excel 2007 VBA Differences
• Offering Options in a Macro
• Renaming a Macro
• Automating Copying Macros
• Debugging a Macro
• Friendly and Informative Error Handling
• Stepping Through a Macro with a Worksheet Visible
• Macro Runs Slowly, but Steps Quickly
• Macro, while Running, Stops Excel from Responding
• Macros Run Fine Individually, but Not Collectively
• Getting Big Macros to Run
• Working while a Macro is Running
• Assigning a Macro to a Keyboard Combination
• One Shortcut for Two Macros
• Removing a Macro from a Shortcut Key
• Relative VBA Selections
• Running Macros in the Background
• Aborting a Macro and Retaining Control
• Develop Macros in Their Own Workbook
• Macros in Template Files
• Storing Macros in Templates
• Forcing a Macro to Run when a Worksheet is Recalculated
• Deleting a Macro
• Generating a List of Macros
• Clean Up Your Macro List
• Understanding Phantom Macros
• Getting Rid of the "Enable Macros" Notice
• Removing All Macros
• Easily Deploying Customizations
• Automating the Importing of Macros
• Library Not Registered Error
• Hiding Macros
• Disabled Macros
• Macros Not Available in Later Versions of Excel
• Maximum Length Limit for a Macro
• Excluding a Specific Add-In at Startup
• Using Macros in Protected Workbooks
• Ctrl+Break Won't Work to Stop a Macro
• Recovering Macros from Corrupted Workbooks
• Trouble Recording Paste Special Formula
• Digital Signatures for Macros
• Store Common Macros in the Personal Macro Workbook
• Understanding the Personal Workbook Filename
• Moving Macros from the Personal Workbook
• Automatically Hiding the Personal Workbook
• Upgrading a Personal.xls Workbook
• Opening Personal.xlsb in Excel 2007
• Documenting Changes in VBA Code
• Measuring Efficiency of Formulas and Macros
• Out of Memory Errors when Accessing the VBA Editor
• Out of Memory Errors for Macros
• Macros in Two Workbooks Interfere with Each Other
• Automatically Changing References to VBA Libraries
• Item Not Available in Library
• Automatically Opening Macro Workbooks when Using a Shortcut Key

Functions and Subroutines

• Understanding Subroutines
• Understanding Functions
• Making Common Functions Available
• Worksheet Events
• Workbook Events

Structures Used in Macros

• Understanding the For ... Next Structure
• Exiting a For ... Next Loop Early
• Understanding the If ... End If Structure
• Understanding the While...Wend Structure
• Understanding the Select Case Structure

Working with Variables

• Understanding Variables in Macros
• Declaring Variables
• Swapping the Values in Two Variables
• Comparing Strings
• Converting Numbers to Strings
• Converting Strings to Numbers
• Quickly Dumping Array Contents

Commands and Statements

• Deriving an Absolute Value
• Determining a Random Value
• Determining an ANSI Value
• Determining the Hour of the Day
• Determining the Day of the Month
• Determining Differences between Dates
• Creating a String in a Macro
• Dissecting a String
• Trimming Spaces from Strings
• Determining the Length of a String
• Determining an Integer Value
• Error Using ATAN2 Function in Macro
• Using BIN2DEC In a Macro
• Using SUM in a Macro
• EOMONTH Function is Flakey
• Making VLOOKUP Trigger a Macro
• Making VLOOKUP Case Sensitive

Controlling Data Entry

• Requiring Input
• Don't Allow Empty Cells
• Controlling Data Entry in a Cell
• Running a Macro while in Edit Mode
• Forcing Input to Uppercase
• Controlling Entry Order on Unprotected Cells
• Accepting Only a Single Digit
• Setting a Length Limit on Cells
• Setting an Upper Threshold for a Cell
• Entering Data as Thousands
• Creating Dependent Cells
• Automatically Adding 20% to an Entry
• Creating a Shortcut for Pasting Values
• Changing the Default Paste Mode
• Making Sure Numbers Copy as Numbers
• Automatically Protecting After Input
• Locking All Non-Empty Cells
• Maintaining the Active Cell
• Unique Name Entry, Take Two
• Inserting a Radical Symbol
• Checking for Proper Entry of Array Formulas
• Positioning a Column on the Screen
• Jumping to the Start of the Next Data Entry Row
• Limiting Who Can Delete Data

Selecting Cells and Ranges

• Using R1C1 Formula References in a Macro
• Magnifying Only the Current Cell
• Displaying the Selected Cell's Address
• Selecting a Cell in the Current Row
• Selecting a Range of Cells Relative to the Current Cell
• Selecting the First Cell In a Row
• Selecting a Specific Cell in a Macro
• Highlighting the Rows of Selected Cells
• Choosing Direction After Enter on a Workbook Basis
• Automatically Moving from Cell to Cell when Entering Data
• Using a Macro to Select a Modified Table Body

Affecting Worksheet Data

• Inserting Worksheet Values with a Macro
• Filling a Range of Cells with Values
• Copying Named Ranges
• Copying to Very Large Ranges
• Clearing Everything Except Formulas
• Deleting Everything Except Formulas
• Converting Text Case
• Converting Cells to Proper Case
• Proper Case Conversion with Exceptions
• Modifying Proper Capitalization
• Capitalizing Just a Surname
• Converting Text to Numbers
• Converting Imported Information to Numeric Values
• Reversing Integer Values
• Converting to ASCII Text
• Getting Rid of 8-Bit ASCII Characters
• Negating a Cell Using a Macro
• Creating a Plus/Minus Button
• Finding the Lowest Numbers
• Finding the Smallest Even Value
• Keeping a Max Value in a Cell
• Finding Odd Values Greater Than 50
• Converting from Relative to Absolute
• Swapping Two Cells
• Reversing Cell Contents
• Reversing Names In Place
• Concatenating Ranges of Cells
• Concatenating Values from a Variable Number of Cells
• Finding the First Non-Digit in a Text Value
• Identifying Digit-Only Part Numbers Excluding Special Characters
• Finding the Nth Occurrence of a Character
• Extracting a Pattern from within Text
• Repeating a Pattern when Copying or Filling Cells
• Highlighting Pattern Violations
• Identifying Values that Don't Follow a Specific Pattern
• Shortcut to Merge Cells
• Identifying Merged Cells
• Removing Duplicate Cells
• Deleting Duplicate Text Values
• Using the Delete Duplicates Tool
• Using Data Filtering
• Using a Formula
• Using a Macro
• Removing Duplicates Based on a Partial Match
• Deleting Everything Up to a Character Sequence
• Extracting First and Last Words
• Removing Cells from a Selected Range
• Extracting Numbers within a Range
• Deleting Old Data from a Worksheet
• Deleting Unwanted Styles
• Conditional Page Breaks
• Moving Subtotals
• Formatting Subtotal Rows
• Determining Combinations to Make a Total
• Listing Combinations
• Putting Addresses on State-Specific Worksheets
• Selectively Importing Records
• Importing Huge Data Files
• Merging Many Workbooks
• Importing Multiple Files to a Single Workbook
• Importing Based on a Partial File Name
• Getting Rid of Extra Quote Marks in Exported Text Files
• Getting Rid of Alphabetic Characters
• Adjusting Values with Formulas
• Returning Zero When a Referenced Cell is Blank
• Counting Commas in a Selection
• Counting Consecutive Negative Numbers
• Counting Cells Containing a Formula
• Summing Only Cells Containing Formulas
• Summing Digits in a Value
• Summing Absolute Values
• Summing Based on Formatting in Adjacent Cells
• Summing Based on Part of the Information in a Cell
• Summing Only Visible Values
• Averaging without Hidden Cells
• Averaging Based on Multiple Criteria
• Displaying a Count of Zeros on the Status Bar
• Inserting Dashes between Letters and Numbers
• Removing Spaces
• Getting Rid of Spaces in Cells
• Trimming Off All Spaces
• Adding Spaces in Front of Capital Letters
• Replacing Tildes at the Beginning of a Cell
• Replacing Commas with Periods
• Replacing Letters with Numbers
• Generating Random Strings of Characters
• Generating Unique, Sequential Names
• Randomly Assigning Names to Items
• Pulling Apart Characters in a Long String
• Breaking Up Variable-Length Part Numbers
• Moving the House Number to Its Own Cell
• Deleting All Characters Up Through a Unique Character Sequence
• Splitting Text to Multiple Cells
• Splitting Sentences to Cells
• Splitting Cells by Case
• Separating Text by Color
• Separating Evens and Odds
• Adding Dashes between Letters
• Removing Dashes from ISBN Numbers
• Calculating an ISBN Check Digit
• Adding a Missing Closing Bracket
• Pulling Initial Letters from a String
• Making PROPER Skip Certain Words
• Determining "Highest Since" or "Lowest Since"
• Making Changes in a Group of Workbooks
• Three-Dimensional Transpositions
• Putting an X in a Clicked Cell
• Getting Rid of Everything Except Numbers
• Deleting Zero Values from a Data Table
• Removing Cells Containing Specific Terms
• Number of Terms in a Formula
• Finding the Number of Significant Digits
• Removing the Last Digit in a Number
• Leaving a Cell Value Unchanged If a Condition Is False
• Copying Data without Leaving the Currently Selected Cell
• Copying and Pasting Non-Contiguous Ranges of Cells
• Making Sure Cells are Filled In before Saving

Affecting Cell Formatting

• Enforcing a Desired Font
• Adding Drop Shadows to Cells
• Highlighting Values in a Cell
• Creating a Center Across Selection Button
• Adjusting Center Across Selection with a Cell Value
• Changing the Default Vertical Alignment
• Making a Cell's Contents Bold or Italic
• Determining If a Cell is Bold
• Counting Cells with Text Colors
• Official Color Names in VBA
• Showing RGB Colors in a Cell
• Determining the RGB Value of a Color
• Converting HSL to RGB
• Replacing Background Colors in Cells
• Automatically Changing a Cell's Background Color
• Specifying Font Color in a Formula
• Creating Superscript and Subscript Buttons
• Automatically Copying Formatting
• Pasting and Matching Destination Formatting
• Matching Formatting when Concatenating
• Adding Differently Formatted Text to a Cell
• Finding Positions of Formatted Characters in a Cell
• Checking All Cell Formatting in VBA
• Replacing Cell Formats
• Determining Font Formatting
• Changing Fonts in Multiple Workbooks
• Making All Occurrences Bold
• Select One Cell and Make Another Cell Bold
• Separating Cells Based on Text Color
• Shortcut Key for Format Painter
• Finding Cells Filled with a Particular Color
• Counting Colors of Cells
• Counting Empty Colored Cells
• Summing Cells Using a Particular Background Color
• Summing Filled Cells
• Using COUNTIF with Colors
• Outlining Cells Referenced in a Formula
• Adding a Tool to Clear Borders
• Flashing Cells
• Locking the Background Color
• Automatically Formatting for Decimal Places
• Using a Custom Format to Add Dashes
• Moving Custom Formats to Number Formatting Categories
• Can't Set Custom Format in VBA
• Coloring Cells with Formulas
• Setting Cell Color Based on Numeric Values
• Coloring Identical Company Names
• Colors in an IF Function
• Changing Currency Formatting for a Single Workbook
• Changing Number Display Settings for Single Workbooks
• Mimicking Small Caps in Excel
• Determining a Format's Currency Symbol

Working with Conditional Formatting

• Conditionally Highlighting Cells Containing Formulas
• Conditionally Formatting Cells Containing Dates
• Conditional Formats for Odd and Even Columns
• Conditional Formats that Distinguish Blanks and Zeroes
• Changing Font Face and Size Conditionally
• Diagonal Borders in a Conditional Format
• Protecting Your Conditional Formatting Rules
• Detecting Errors in Conditional Formatting Formulas
• Protecting Conditional Formatting
• Removing Conditional Formats, but Not the Effects
• Highlighting Greater Than Average Dry Durations

Affecting Rows and Columns

• Removing Duplicate Rows
• Deleting Blank Rows
• Selecting Blanks
• Sorting
• Macros
• Enforcing Moving Cells Up
• Conditionally Deleting Rows
• Deleting Rows Containing Struck-Through Text
• Deleting Every X Rows
• Deleting All Rows Except for the End of Month
• Inserting a Page Break Every X Rows
• Hiding Rows Based on a Cell Value
• Hiding Rows Based on Two Values
• Detecting Hidden Rows
• Skipping Hidden Rows in a Macro
• Easily Adding Blank Rows
• Inserting and Copying Rows
• Moving and Selecting Rows
• Setting Row Height in a Macro
• Adjusting to a Maximum Row Height
• Automatic Row Height for Wrapped Text
• Adjusting Row Height for a Number of Worksheets
• Splitting Information into Rows
• Shading Rows for Ease in Reading Output
• Summing Every Fourth Cell in a Row
• Counting String Occurrences in Odd Rows
• Condensing Sequential Values to a Single Row
• Hiding a Huge Number of Rows
• Getting Rid of Empty Rows after Importing
• Floating Information in a Frozen Row
• Rows in a PivotTable
• Alphabetic Column Designation
• Getting Rid of Numbered Columns
• Unhiding a Single Column
• Unhiding a Limited Number of Columns
• Determining the Number of Visible Columns
• Setting Column Width
• Widening a Column to a Particular Cell's Width
• Widening Multiple Columns Proportionally
• Changing Width and Height to Inches
• Deleting Duplicate Columns
• Deleting Blank Columns
• Combining Columns
• Combining Multiple Rows in a Column
• Changing Shading when a Column Value Changes
• Selecting to the Bottom of a Column in a Macro
• Selecting Columns in VBA when Cells are Merged
• Setting Minimums and Maximums when AutoFitting Column Widths
• Hiding Columns Based on a Cell Value
• Locating a Single-Occurrence Value in a Column

Filtering Data and Working with Filtered Data

• Toggling AutoFilter
• Quickly Identifying Applied AutoFilters
• Enabling Filters by Default
• Numbering Filtered Rows
• Filtering for Comments
• Displaying Multiple Filtered Colors
• Clearing Only Filtering Settings
• Images Pile Up when Filtering
• Changing AutoFilter Drop-Down Arrow Colors
• Column Formatting Based On a Filter
• Easy Filtering Specifications for a PivotTable
• Removing Filters and Unhiding Rows and Columns on Multiple Worksheets
• Macro Fails after Filter

Working with Dates and Times

• Modifying Default Year for Dates
• Changing How Excel Determines which Year to Use
• Entering Dates without Separators
• Putting the Last Saved Date in a Cell
• Parsing Non-Standard Date Formats
• Controlling Automatic Formatting of Dates
• Converting an Unsupported Date Format
• Deciphering a Coded Date
• Using a Two-Character Day of the Week in a Date Format
• Using a Single Digit for a Year
• Limiting Entry of Prior Dates
• Adjusting Date Values by Keypress
• Preventing Automatic Date Formatting Changes
• Pushing Dates Into Last Month
• Deleting Dates within Text Strings
• Converting Dates to Text
• Hiding Columns Not within a Date Range
• Determining If a Year is a Leap Year
• Getting Excel Dates into Outlook's Calendar
• Adding Ordinal Notation to Dates
• Tombstone Date Math
• Determining a Zodiac Sign from a Birthdate
• Finding the Date Associated with a Negative Value
• Automatically Advancing by a Month
• Weekdays in a Month
• Counting Month Ends
• Calculating the First Tuesday
• Pulling All Fridays
• The Last Business Day
• Years in Which a Date Occurred on a Particular Day
• Deleting Rows before a Cutoff Date
• Expiration Date for Excel Programs
• Installing the Date Picker
• Inserting the Current Time with Seconds
• Recording a Data Entry Time
• Automatically Entering a Data Entry Time
• Entering Large Time Values
• Checking for Time Input
• Limiting Input by Time of Day
• Entering or Importing Times without Colons
• Shortcut to Enter GMT
• Automatically Converting to GMT
• Using Excel for Timing
• Calculating TV Time
• Converting Numeric Values to Times

Working with Worksheets

• Creating Worksheets
• Creating and Naming a Worksheet Using a Macro
• Jumping to a Specific Worksheet
• Shortcut to Move between Two Worksheets
• Displaying the First Worksheet in a Macro
• Displaying Worksheets in a Slideshow Fashion
• Opening a Workbook to a Specific Worksheet
• Telling which Worksheets are Selected
• Determining a Worksheet's Number
• Deriving the Worksheet Name
• Getting the Name of the Worksheet Into a Cell
• Retrieving Worksheet Names
• Automatically Renaming Worksheets
• Dynamic Worksheet Tab Names
• Dynamically Changing Worksheet Tab Color
• Conditionally Setting the Color of Worksheet Tabs
• Alphabetizing Worksheet Tabs
• Ordering Worksheets Based on a Cell Value
• Creating Worksheets from a List of Names
• Renaming Worksheets Based On a List
• Resetting Default Names for New Worksheets
• Copying Worksheets in a Macro
• Making Multiple Worksheet Copies
• Creating a Copy without Formulas
• Relative Worksheet References
• Sheets for Days
• Referencing Worksheet Tabs
• Referencing a Worksheet Name
• Locking Worksheet Names
• Freezing Worksheet Tabs
• Running Macros on Hidden Worksheets
• Spell-Checking in a Protected Worksheet
• Stopping a Worksheet from being Moved or Copied
• Preventing Someone from Recreating a Protected Worksheet
• Fixing Macro Button Behavior in Protected Worksheets
• Buttons Don't Stay Put
• Visually Showing a Protection Status
• Forcing a Worksheet to be Protected Again
• Stopping Worksheets from being Reprotected
• Protecting Many Worksheets
• Finding a Worksheet to Unhide among Many Hidden Sheets
• Unhiding Multiple Worksheets
• Properties for Worksheets
• Detecting Types of Sheets in VBA
• Selecting All Visible Worksheets in a Macro
• Deleting Worksheets in a Macro
• Condensing Multiple Worksheets Into One
• Combining Worksheets from Many Workbooks
• Comparing Formulas on Two Worksheets
• Finding a Worksheet with a Specific Value in a Specific Cell
• Copying a Single Cell to Multiple Worksheets
• Copying Rows between Worksheets Based on a Text Value
• Copying a Set Range from Multiple Worksheets to a New Worksheet
• Preventing Changes to Multiple Selected Worksheets
• Counting the Times a Worksheet is Used
• Limiting How Many Times a Worksheet Can Be Calculated
• Generating Unique Numbers for Worksheets
• Disabling Moving between Worksheets
• Random Width and Height Changes
• Identifying the Last Cell Changed in a Worksheet
• Copying Data between Worksheets Using a Macro
• Viewing Same Cells on Different Worksheets
• Testing for an Empty Worksheet
• Turning Off Display of Zeros for All Worksheets
• Copying Worksheet Code Automatically
• Deleting VBA Code in a Copied Worksheet
• Finding the Size of Individual Worksheets
• Creating Workbooks for Individual Worksheets
• Creating Individual PDFs by Worksheet
• Different Cell Movement in a Single Worksheet

Interacting with the Excel Environment

• Switching Windows in a Macro
• Setting Program Window Size in a Macro
• Calculating the Distance between the Top of the Window and Row 1
• Dividing the Screen Unevenly between Two Workbooks
• Remembering Workbook Position and Size
• Controlling Window Size when Opening Additional Workbooks
• Inadvertantly Getting Rid of Frozen Panes
• Making Pane Settings Persist
• Turning Off Screen Updating
• Changing the Outline Color of the Selected Cell
• Disabling Shift Key Use when Opening a Workbook
• Opening a Workbook but Disabling Macros
• Forcing Editing to Be Done in a Cell
• Synchronous Scrolling with More than Two Windows
• Using Go To to Jump to a Chart Sheet
• Displaying a Set Column Range
• Selecting Visible Cells in a Macro
• Stepping Through a Non-Contiguous Range of Cells
• Hiding Excel in VBA
• Preserving the Undo List
• Clearing the Undo Stack
• Calculating Only the Active Workbook
• Setting the Calculation Default
• Counting All Characters
• Unhiding or Listing All Objects
• Using Named Ranges in a Macro
• Checking the Scope of a Defined Name
• Getting Rid of Unused Range Names
• Deleting All Names but a Few
• Setting a Default for Shifting when Inserting
• Default Cell Movement when Deleting
• Saving Movement on Enter with a Workbook
• Forcing Manual Calculation for a Workbook
• Pulling Formulas from a Worksheet
• Saving Non-Existent Changes
• Seeing the Difference on the Status Bar
• Counting Precedents and Dependents
• Accessing Dependent and Precedent Information
• Discovering Dependent Workbooks
• Determining If a Workbook is Referenced by Another Workbook
• Leaving Trace Precedents Turned On
• Turning Off AutoFill for a Workbook
• Noting the Workbook Creation Date
• Displaying the "Last Modified" Date
• Noting When a Workbook was Changed
• Date Last Edited
• Finding the Path to the Desktop
• Grabbing a User's Name from Excel
• Inserting the User's Name in a Cell
• Adjusting a Path Based on System and User
• Grabbing the MRU List
• Seeing All Open Workbook Names
• Testing if a Workbook is Open
• Getting Rid of "Copy of"
• Pulling Cell Names into VBA
• Simultaneous Scrolling
• Looking Up Names when Key Values are Identical
• Determining if Caps Lock is On
• Disabling Page Layout View
• Disabling a Function Key
• Disabling the F1 Key
• Disabling All Function Keys Except One
• Disabling Excel's Help System
• Retrieving Drive Statistics
• Full Path Names in Excel
• Automatically Closing a Workbook
• Finding Unknown Links
• Getting Rid of Workbook Links
• Replacing Links with Values
• Generating Automatic Links to Audio Files
• Protecting Formulas in Control-Linked Cells
• Task Pane Doesn't Appear Properly
• Finding the Last-Used Cell in a Macro
• Jumping to the Real Last Cell
• Determining if Calculation is Necessary
• Getting Notification a Recalculation is Necessary
• Notification when Recalculation is Done
• Permanently Turning On Set Precision As Displayed
• Iterating Circular References
• Enabling Circular References by Default
• Modifying Error Alerts Received
• Tracking Down Invalid References
• Determining How Many Windows are Open
• Saving Common Formulas
• Turning Off Track Changes without Unsharing
• Checking if a Workbook is Already Open
• Saving All Open Workbooks
• Saving a Workbook in a Macro
• Finding the Size of a Workbook
• Finding Workbooks Containing Macros
• Automatically Enabling Macros for Specific Workbooks
• Limiting Where a Workbook is Used
• Saving Changes when Closing
• Saving an Unsavable Workbook
• Closing a Read-Only Workbook
• Forcing a Workbook to Close after Inactivity
• Using a Single Password for Multiple Workbooks
• Finding Other Instances of Excel in a Macro
• Who Has the File Open?
• Macros Run Slower in Newer Excel?
• Delaying in a Macro
• Creating an Animated Count Up
• Executing a Macro Every 15 Minutes
• Making Modal Dialog Boxes Appear in Front of Workbooks

The Ribbon, Menus, and Toolbars

• Displaying Excel's Developer Tab
• Tools on Developer Tab are Unavailable
• Adding a Macro to a Toolbar
• Adding a Macro to the Quick Access Toolbar
• Controlling Display of Toolbar Buttons
• Adding Items to a Context Menu
• Removing Items from a Context Menu
• Problem with Missing Context Menu Option
• Copying from the Task Bar

Working with Comments

• Copying Comments to Cells
• Inserting Workbook Comments Into a Cell
• Pasting Pictures into a Comment
• Moving Comment Background Pictures to Cells
• Changing the Comment Font
• Setting the Default Font Size for Comment Balloons
• Copying Comments when Filtering
• Adding Comments to Protected Worksheets
• Adding a Comment to Multiple Cells
• Linking Comments to Multiple Cells
• Editing a Comment Close to Its Cell
• Finding and Replacing Text in Comments
• Automatically Placing Text in a Comment
• Placing Formula Results in a Comment
• Viewing the Contents of a Very Wide Cell on Demand
• Counting Comments in a Worksheet
• Changing the Comment Indicator Color

Working with Headers and Footers

• Dynamic Headers and Footers
• Full Path Names in Headers or Footers
• Using a Formula in a Footer
• Adding a File Path and Filename
• Extracting File Names from a Path
• First and Last Names in a Page Header
• Adding Last-Row Data to a Page Footer
• Multiple Line Headers and Footers
• Putting a Different Date in a Header
• Inserting the Saved Date in a Header or Footer
• Last Saved Date in a Footer
• Specifying Date Formats in Headers
• Using a Custom Date Format in a Header or Footer
• Selective Headers and Footers
• Switching Headers in a Frozen Row
• Putting Cell Contents in Footers
• Copying Headers and Footers
• Header and Footer Background Color
• Using a Different Footer on Secondary Pages
• Leading Zeros in Page Numbers
• Changing Page Number Format
• Specifying the Y Value in X of Y Page Numbering
• Custom Page Numbers on Printouts
• Roman Numerals for Page Numbers
• Omitting Page Numbers on Some Pages
• Changing Section Headers
• Find and Replace in Headers

Working with Graphics and Charts

• Assigning Macros to Graphics
• Pasting a Graphic to Multiple Worksheets
• Cropping Graphics in a Macro
• Positioning a Graphic
• Copying Pictures with a Macro
• Pop-Up Comments for Graphics
• ScreenTip for an Image
• Automatically Displaying Thumbnails of a Graphic File
• Sizing Text Boxes and Cells the Same
• Resizing a Text Box in a Macro
• Finding Text in Text Boxes
• Placing Textbox Text into a Worksheet
• Changing Text in Text Boxes on a Chart
• Adding AutoShapes
• Adding and Formatting a Shape via Macro
• Shifting Objects Off a Sheet
• Removing Pictures for a Worksheet
• Snapshots of Excel Worksheets for PowerPoint
• Creating Charts
• Automatically Creating Charts for Individual Rows in a Data Table
• Unlocking Charts
• Positive and Negative Colors in a Chart
• Labeling X-Y Scatter Plots
• Exporting Black and White Charts
• Creating a JPG File from a Chart
• Specifying the Size of Chart Objects
• Specifying Chart Sizes
• Changing Elements in Lots of Charts at One Time
• Converting Charts to GIF Files
• Creating a Photo Catalog from a Folder of Photos
• Hyperlinks to Charts
• Determining Mouse Cursor Coordinates On a Graphic

Working with the Outside World

• Getting User Input in a Dialog Box
• Using InputBox to Get Data
• Hiding Entries in an InputBox
• Pausing Macros for User Input
• Using Message Boxes
• Conditionally Displaying a Message Box
• Specifying Location for a Message Box
• Progression Indicator in a Macro
• Hiding an Excel 2007 Progress Indicator
• Using the Status Bar
• Disappearing Status Bar
• Getting a File Name
• Determining If a File Exists
• Checking for the Existence of a File
• Determining the Length of a Text File
• Getting Input from a Text File
• Saving Information in a Text File
• Appending to a Text File
• Using Seek
• Renaming a File
• Deleting a File
• Faster Text File Conversions
• Importing a Subset of Records
• Changing the Default Text Import Delimiter
• Changing Your Regional Settings
• Changing the Text to Column Settings
• Writing Your Own Import Routine
• Using Power Query
• Aligning Cells when Importing from CSV
• Specifying a Delimiter when Saving a CSV File
• Creating a Directory
• Changing Directories
• Determining the Current Directory
• Removing a Directory
• Changing the Default Drive
• Easily Changing the Default Drive and Directory
• Word Documents from Excel Macros
• Using Old Lotus Macros
• Linking to a Specific Page in a PDF File
• Updating Automatically when Opening Under Macro Control
• Displaying Messages when Automatic Data Changes
• Preparing Data for Import into Access
• Getting Contact Information from Outlook
• Creating a Dated Backup File

Web and Online

• Opening Sites in a Browser
• Opening an HTML Page in a Macro
• Specifying a Browser in a Hyperlink
• Converting a Range of URLs to Hyperlinks
• Pulling Apart a URL
• Specifying Default Hyperlink Text
• Showing Visited Hyperlinks
• Getting Rid of All Hyperlinks
• Changing Portions of Many Hyperlinks
• Changing Huge Numbers of Hyperlinks
• Converting to Hyperlinks in a Shared Workbook
• Extracting E-mail Addresses from Hyperlinks
• Extracting URLs from Hyperlinks
• Extracting URLs from Hyperlinked Images
• Extracting Hyperlink Information
• Get Rid of Web Stuff
• Sending Single Worksheets via E-mail
• Enabling Macros for an E-mailed Workbook
• Sending an E-mail when a Due Date is Reached
• E-mailing PDF Reports Results in Consistent Crash
• Suppressing the Reviewing Toolbar on E-mailed Workbooks
• Updating a Web Query Based on a Schedule
• Retrieving Web Query Data without Interruption
• Hiding a Hyperlink on a Printout

Working with the Printer

• Printing Gridlines by Default
• Using a Macro to Set a Print Range
• Locking the Print Area
• Showing Print Preview for the Current Page
• Page Numbers in VBA
• Changing Paper Size for a Complete Workbook
• Displaying the Print Dialog Box in a Macro
• Specifying Print Quantity in a Cell
• Controlling the Printer in a Macro
• Working with Multiple Printers
• Using Multiple Print Settings
• Can Only Print to Default Printer
• Printing Selected Worksheets
• Printing Only Non-Blank Worksheets
• Printing Limited Pages from a Range of Worksheets
• Forcing Worksheets to Print on a New Sheet
• Printing Odd or Even Pages
• Increasing Row Height for Printing
• Printing an Entire Workbook by Default
• Automatic Selection of Portrait or Landscape
• Printing a Single Column in Multiple Columns
• Printing Multiple Worksheets on a Single Page
• Printing a Worksheet List
• Printing Workbook Properties
• Adjusting Comment Printouts
• Disabling the Print Option
• Automatically Printing a Range
• Setting Print Ranges for Multiple Worksheets
• Printing All or Nothing
• Conditional Printing
• Printing in Black and White and Color
• Protecting Print Settings
• Incrementing Copy Numbers for Printouts
• Printing Individual Worksheets for Vendors
• Limiting Printing to a Workbook from a Set Location
• Showing Filter Criteria on a Printout
• Repeating Rows on a Printout Except on the Last Page
• Printing Selected Cells by Default
• Printing Based on Cell Contents
• Changing Cell Values while Printing
• Easily Printing to PDF
• Printing a Week of Planner Sheets
• Executing a Macro After Printing is Done

Special-Purpose Macros

• Running a Macro when a Workbook is Opened
• Calling a Macro from the Workbook_Open Event
• Running a Macro when a Workbook is Closed
• Bypassing the BeforeClose Event
• Running a Macro when a Worksheet is Activated
• Running a Macro when a Worksheet is Deactivated
• Triggering an Event when a Worksheet is Deactivated
• Opening a Workbook and Suppressing Automatic Macros
• Always Opening a Workbook that is Editable
• Running a Macro in a Number of Workbooks
• Calling a Subroutine from a UDF
• Mouse Click Event in VBA
• Saving in Two Locations
• Saving in Multiple Locations
• Opening Two Workbooks at Once
• Using File Explorer
• Configuring Windows for Automatic Starting
• Using a Batch File
• Using the Open Dialog Box
• Using the XLSTART Folder
• Configuring Excel to Open the Workbooks
• Using a Macro
• Using a Workspace
• Understanding Add-Ins
• Creating Add-Ins
• Using Custom Add-Ins
• Automatically Loading Add-ins

Searching and Sorting

• Universal Searching
• Searching for All
• Searching through Many Workbooks
• Searching a Workbook by Default
• Searching by Columns, by Default
• Changing Default Search Settings
• Searching for Leading Apostrophes
• Searching for Line Breaks
• Searching for a Value Using a Function
• Where Is that Text?
• Searching Very Large Strings in a Macro
• Allowing for Prefixes and Suffixes in Find and Replace
• Using Find and Replace to Pre-Pend Characters
• Replacing Only Whole Words in Excel
• Replacing Characters at the End of a Cell
• Finding and Replacing with Subscripts
• Finding and Replacing Error Values
• Finding and Replacing in Text Boxes
• Using Find and Replace to Find Conditionally Formatted Cells
• Superscripts in Find and Replace
• Wildcards in 'Replace With' Text
• Replacing in Worksheets and Comments At the Same Time
• Getting a List of Matching Cells
• Finding Columns of a Certain Width
• Finding the Widest Cell Contents
• Sorting Worksheets
• Sorting Worksheets According to Region
• Non-standard Sorting
• Forcing Excel to Sort Cells as Text
• Sorting Data on Protected Worksheets
• Sorting by Colors
• Sorting by Fill Color
• Taking Bold Text into Account in a Sort
• Using a Color
• Using a Helper Column
• Using a Macro
• Other Possible Approaches
• Sorting IP Addresses
• Sorting Data Containing Merged Cells
• Determining Sorting Criteria
• Storing Sorting Criteria
• Automatically Sorting as You Enter Information

Macro Cookbook

• AutoFilling with the Alphabet
• Converting Numbers Into Words
• Converting Phone Numbers
• Adding Leading Zeroes to ZIP Codes
• Shortening ZIP Codes
• Working with Imperial Linear Distances
• Calculating the Distance between Points
• Selecting Random Names
• Generating Random Testing Data
• Zooming with the Keyboard
• Always Open at 100% Zoom
• Determining If a Number is Odd or Even
• Playing with a Full Deck
• Counting Wins and Losses
• Making Squares
• Counting Words
• Counting Occurrences of Words
• Default Worksheet when Opening
• Protecting Individual Worksheets, by User
• Unprotecting Groups of Worksheets
• Showing Text when a Cell is Empty
• Creating Individual Workbooks
• Disappearing Toolbar Buttons for Macros
• Sheets for Months
• Macro for Month Name
• Naming Tabs for Weeks
• Jumping to Alphabetic Worksheets
• Flipping Data
• Counting Shaded Cells
• Counting Unique Values
• Counting Cells According to Case
• Generating a Keyword Occurrence List
• Only Showing the Maximum of Multiple Iterations
• Finding Differences between Lists
• Comparing Workbooks for Differences
• Highlighting Cells Containing both Letters and Numbers
• Quickly Changing Windows
• Using an Exact Number of Digits
• Engineering Calculations
• Counting Atoms in a Chemical Formula
• Solving a Quadratic Equation
• Maintaining Accuracy of Significant Digits
• Counting Groupings Below a Threshold
• Referencing External Cell Colors
• Automatically Editing Formulas
• Developing Reciprocal Conversion Formulas
• Spreading Out Worksheet Rows
• DOS from Macros
• Generating a List of Macros
• Deleting Macros from within a Macro
• Self-Deleting Macros
• Deleting Worksheet Code
• Stopping Excel from Deleting Macros from a Workbook
• Changing Macro Cell References Based on Edits
• Using the Camera in VBA
• Conditional Page Breaks
• Calculating the Interval between Occurrences
• Self-Aware Macros
• Pasting without Updating References
• Jumping To a Specific Page
• Checking for Either of Two Text Values
• Updating Multiple PivotTables at Once
• Removing Subtotals from Many PivotTable Fields
• Reducing File Sizes for Workbooks with PivotTables
• Using Classic PivotTable Layout as the Default
• Displaying a PivotTable's Name in the PivotTable
• Resizing Checkboxes
• Stopping a Checked Box from being Unchecked
• Creating a Floating Macro Button
• Making a Macro Button Stay Put
• Extracting Street Numbers from an Address
• Extracting a State and a ZIP Code
• Combinations for Members in Meetings
• Generating Random Door Access Codes
• Generating Random Strings
• Creating a Function Inventory for a Workbook
• Replacing Some Formulas with the Formula Results
• Finding the Address of the Lowest Value in a Range
• Extracting Proper Words
• Clearing the Clipboard in a Macro
• Clearing Large Clipboard Entries
• Pulling Apart Cells
• Pulling First Letters from Parenthetical Text
• Reorganizing Data
• Finding Unused Names
• Changing References in a Lot of Defined Names
• Getting Rid of Non-Printing Characters Intelligently
• Changing Months in a Workbook
• Triggering a Macro for Drop-Down List Changes
• Delimited Text-to-Columns in a Macro
• Making Worksheet Copies for Daily Shifts
• Replacing and Converting in a Macro
• Pulling Filenames into a Worksheet
• Protecting an Entire Folder of Workbooks
• Storing a User's Location before Running a Macro
• Limiting Scroll Area
• Conditionally Making a Sound
• Conditionally Playing an Audio File

Description	Download	CD
ExcelTips: The Macros	ISBN 978-1-61359-509-1 (7.8 MB) $37.99	ISBN 978-1-61359-510-7 (1,310 pages) $47.99

You can also order ExcelTips: The Macros by calling our order line: 307-200-0450.