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 lines of the code, you'll see that this macro is relatively intuitive:
Sub Macro1()
'Step 1: Tell Excel what to do if error
On Error GoTo MyError
'Step 2: Add a sheet and name it
Sheets.Add
ActiveSheet.Name = _
WorksheetFunction.Text(Now(), "m-d-yyyy h_mm_ss am/pm")
Exit Sub
'Step 3: If here, an error happened; tell the user
MyError:
MsgBox "There is already a sheet called that."
End Sub
You must anticipate that if you give the new sheet a name that already exists, an error would occur. So in Step 1, the macro tells Excel to immediately skip to the line that says MyError (in Step 3) if there is an error.
Step 2 uses the Add method to add a new sheet. By default, the sheet is called Sheetxx, where xx represents the number of the sheet. You give the sheet a new name by changing the Name property of the ActiveSheet object. In this case, you're naming the worksheet with the current date and time.
As with workbooks, each time you use VBA to add a new sheet, the newly added sheet automatically becomes the active sheet. Finally, in Step 2, note that the macro exits the procedure. It has to do this so that it doesn’t accidentally go into Step 3 (which comes into play only if an error occurs).
Step 3 notifies the user that the sheet name already exists. Again, this step should be activated only if an error occurs.
How to use the macro
To implement this macro, you can copy and paste it into a standard module:
- Activate Visual Basic Editor by pressing Alt+F11.
- Right-click the project/workbook name in the project window.
- Choose Insert ⇒ Module.
- Type or paste the code in the newly created module.
Deleting All but the Active Worksheet
At times, you may want to delete all but the active worksheet. In these situations, you can use this next macro.
How the macro works
The macro in this section loops through the worksheets, matching each worksheet name to the active sheet’s name. Each time the macro loops, it deletes any unmatched worksheet. Note the use of the DisplayAlerts property in Step 4. This effectively turns off Excel’s warnings so you don’t have to confirm each delete.
Sub Macro1()
'Step 1: Declare your variables
Dim ws As Worksheet
'Step 2: Start looping through all worksheets
For Each ws In ThisWorkbook.Worksheets
'Step 3: Check each worksheet name
If ws.Name <> ThisWorkbook.ActiveSheet.Name Then
'Step 4: Turn off warnings and delete
Application.DisplayAlerts = False
ws.Delete
Application.DisplayAlerts = True
End If
'Step 5: Loop to next worksheet
Next ws
End Sub
The macro first declares an object called ws. This step creates a memory container for each worksheet it loops through.
In Step 2, the macro begins to loop, telling Excel it will evaluate all worksheets in this workbook. There is a difference between ThisWorkbook and ActiveWorkbook. The ThisWorkBook object refers to the workbook that contains the code. The ActiveWorkBook object refers to the currently active workbook. They often return the same object, but if the workbook running the code is not the active workbook, they return different objects. In this case, you don’t want to risk deleting sheets in other workbooks, so you use ThisWorkBook.
In Step 3, the macro simply compares the active sheet name to the sheet that is currently being looped.
In Step 4, if the sheet names are different, the macro deletes the sheet. As mentioned, you use DisplayAlerts to suppress any confirmation checks from Excel. If you want to be warned before deleting the sheets, you can omit Application. DisplayAlerts = False. Omitting the DisplayAlerts statement will ensure that you get the message in Figure 5-1, allowing you to back out of the decision to delete worksheets.
Figure 5-1: Omit the DisplayAlerts statement to see warning messages.
In Step 5, the macro loops back to get the next sheet. After all the sheets are evaluated, the macro ends.
How to use the macro
To implement this macro, you can copy and paste it into a standard module:
- Activate Visual Basic Editor by pressing Alt+F11.
- Right-click the project/workbook name in the project window.
- Choose Insert ⇒ Module.
- Type or paste the code in the newly created module.
When you use ThisWorkbook instead of ActiveWorkbook in a macro, you can’t run the macro from the personal macro workbook. Why? Because ThisWorkbook would refer to the personal macro workbook, not to the workbook to which the macro should apply.
Hiding All but the Active Worksheet
You may not want to delete all but the active sheet as you did in the preceding macro. Instead, a more gentle option is to simply hide the sheets. Excel doesn't let you hide all sheets in a workbook; at least one has to be displayed. However, you can hide all but the active sheet.
How the macro works
The macro in this section loops through the worksheets and matches each worksheet name to the active sheet’s name. Each time the macro loops, it hides any unmatched worksheet.
Sub Macro1()
'Step 1: Declare your variables
Dim ws As Worksheet
'Step 2: Start looping through all worksheets
For Each ws In ThisWorkbook.Worksheets
'Step 3: Check each worksheet name
If ws.Name <> ThisWorkbook.ActiveSheet.Name Then
'Step 4: Hide the sheet
ws.Visible = xlSheetHidden
End If
'Step 5: Loop to next worksheet
Next ws
End Sub
Step 1 declares an object called ws. This step creates a memory container for each worksheet that the macro loops through.
Step 2 begins the looping, telling Excel to evaluate all worksheets in this workbook. Note the difference between ThisWorkbook and ActiveWorkbook. The ThisWorkBook object refers to the workbook that contains the code. The ActiveWorkBook object refers to the currently active workbook. They often return the same object, but if the workbook running the code is not the active workbook, they return different objects. In this case, you don’t want to risk hiding sheets in other workbooks, so you use ThisWorkBook.
In Step 3, the macro simply compares the active sheet name to the sheet that is currently being looped.
If the sheet names are different, the macro hides the sheet in Step 4.
In Step 5, you loop back to get the next sheet. After all sheets are evaluated, the macro ends.
Note that you use xlsheetHidden in your macro. This property applies the default hide state you would normally get when you right-click a sheet and select Hide. In this default hide state, a user can right-click any tab and choose Unhide, which displays all hidden sheets. But another hide state is more clandestine than the default. If you use xlSheetVeryHidden to hide your sheets, users will not be able to see them at all — even if they right-click a tab and choose Unhide. The only way to unhide a sheet hidden in this manner is to use VBA.
How to use the macro
To implement this macro, you can copy and paste it into a standard module:
- Activate Visual Basic Editor by pressing Alt+F11.
- Right-click the project/workbook name in the project window.
- Choose Insert ⇒ Module.
- Type or paste the code in the newly created module.
Unhiding All Worksheets in a Workbook
If you’ve ever had to unhide multiple sheets in Excel, you know what a pain it is. You are forced to use the Unhide dialog box shown in Figure 5-2 to unhide one sheet at a time.
Figure 5-2: Without a macro, you’re stuck using the Unhide dialog box to unhide one worksheet at a time.
Although that may not sound like a big deal, it gets to be a pain fast when you have to unhide 10 or more sheets. The macro in this section makes easy work of the task.
How the macro works
This macro loops through the worksheets and changes their visible state.
Sub Macro1()
'Step 1: Declare your variables
Dim ws As Worksheet
'Step 2: Start looping through all worksheets
For Each ws In ActiveWorkbook.Worksheets
'Step 3: Loop to next worksheet
ws.Visible = xlSheetVisible
Next ws
End Sub
Step 1 declares an object called ws. This step creates a memory container for each worksheet that the macro loops through.
In Step 2, the macro starts the looping, telling Excel to enumerate through all worksheets in this workbook.
Step 3 changes the visible state to xlSheetVisible. Then it loops back to get the next worksheet.
How to use the macro
The best place to store this macro is in your personal macro workbook. That way, the macro is always available to you. The personal macro workbook is loaded whenever you start Excel. In the VBE project window, it is named personal.xlsb.
- Activate Visual Basic Editor by pressing Alt+F11.
- Right-click personal.xlb in the project window.
- Choose Insert ⇒ Module.
- Type or paste the code in the newly created module.
If you don’t see personal.xlb in your project window, the file doesn't exist yet. You’ll have to record a macro using personal macro workbook as the destination.
To record the macro in your personal macro workbook, display the Record Macro dialog box before you start recording. Then click the Store Macro In drop-down box and select the Personal Macro Workbook option. Simply record a few cell clicks and then stop recording. You can discard the recorded macro and replace it with this one.
Moving Worksheets Around
We’ve all had to rearrange a spreadsheet so that some sheets come before or after other sheets. If you find that you have to do this often, the macro in this section can help.
How the macro works
When you want to rearrange sheets, you use the Move method of either the Sheets object or the ActiveSheet object. When using the Move method, you specify where to move the sheet to by using the After argument, the Before argument, or both.
Sub Macro1()
'Move the active sheet to the end
ActiveSheet.Move After:=Worksheets(Worksheets.Count)
'Move the active sheet to the beginning
ActiveSheet.Move Before:=Worksheets(1)
'Move Sheet 1 before Sheet 12
Sheets("Sheet1").Move Before:=Sheets("Sheet12")
End Sub
This macro demonstrates how to move the active worksheet to three locations:
- Move the active sheet to the end: When you need to move a worksheet to the end of the workbook, you essentially want to tell Excel to move the sheet after the last sheet. But there's no code in VBA that lets you point to the last sheet. However, you can find the maximum count of worksheets, and use that number as an index for the Worksheets object. For example, you could enter Worksheets(1) to point to the first sheet in a workbook, and enter Worksheet(3) to point to the third sheet in the workbook. To point to the last sheet in the workbook, you could replace the index number with the Worksheets.Count property. Worksheets.Count will give you the total number of worksheets, which will always be the same number as the index for the last sheet. Thus Worksheet(Worksheets.Count) will point to the last sheet.
- Move the active sheet to the beginning: Moving a sheet to the beginning of the workbook is simple. Use Worksheets(1) to point to the first sheet in the workbook, and then move the active sheet before that one.
- Move Sheet 1 before Sheet X: You can also move a sheet before or after another sheet simply by calling that other sheet out by name. In the example demonstrated in the preceding macro, you are moving Sheet1 before Sheet12.
How to use the macro
The best place to store this kind of a macro is in your personal macro workbook so that the macro is always available to you. The personal macro workbook is loaded whenever you start Excel. In the VBE project window, it is named personal.xlsb.
- Activate Visual Basic Editor by pressing Alt+F11.
- Right-click personal.xlb in the project window.
- Choose Insert ⇒ Module.
- Type or paste the code in the newly created module.
If you don’t see personal.xlb in your project window, the file doesn't exist yet. You’ll have to record a macro, using personal macro workbook as the destination.
To record the macro in your personal macro workbook, display the Record Macro dialog box before you start recording. Then click the Store Macro In drop-down box and select the Personal Macro Workbook option. Simply record a few cell clicks and then stop recording. You can discard the recorded macro and replace it with this one.
Sorting Worksheets by Name
You may often need to sort worksheets alphabetically by name (see Figure 5-3). You would think Excel would have a native function to do this, but alas, it does not. If you don’t want to manually sort your spreadsheets, use this macro to do it for you.
Figure 5-3: Sort your worksheets in alphabetical order.
How the macro works
The macro in this section looks more complicated than it is. The macro simply iterates through the sheets in the workbook, comparing the current sheet to the previous one. If the name of previous sheet is greater than the current sheet (alphabetically), the macro moves the current sheet before it. By the time all the iterations are completed, you have a sorted workbook!
Sub Macro1()
'Step 1: Declare your variables
Dim CurrentSheetIndex As Integer
Dim PrevSheetIndex As Integer
'Step 2: Set the starting counts and start looping
For CurrentSheetIndex = 1 To Sheets.Count
For PrevSheetIndex = 1 To CurrentSheetIndex - 1
'Step 3: Check current sheet against previous sheet
If UCase(Sheets(PrevSheetIndex).Name) > _
UCase(Sheets(CurrentSheetIndex).Name) Then
'Step 4: Move if current sheet comes before previous sheet
Sheets(CurrentSheetIndex).Move _
Before:=Sheets(PrevSheetIndex)
End If
'Step 5 Loop back to iterate again
Next PrevSheetIndex
Next CurrentSheetIndex
End Sub
Note that this technique performs text-based sorting, so you may not get the results you were expecting when working with number-based sheet names. For instance, Sheet10 will come before Sheet2 because textually, 1 comes before 2. Excel can't do number-based sorting (in which 2 would come before 10).
Step 1 declares two integer variables. The CurrentSheetIndex variable holds the index number for the current sheet iteration, and the PrevSheetIndex variable holds the index number for the previous sheet iteration.
In Step 2, the macro starts iteration counts for both variables. Note that the count for PrevSheetIndex is one number behind CurrentSheetIndex. After the counts are set, you start looping.
In Step 3, you check to see whether the name of the previous sheet is greater than that of the current sheet. Note the UCase function, which you use to get both names in the same uppercase state. This function prevents sorting errors due to different case states.
Step 4 is reached only if the previous sheet name is greater than the current sheet name. In this step, you use the Move method to move the current sheet before the previous sheet.
In Step 5, you go back around to the start of the loop. Every iteration of the loop increments both variables up one number until the last worksheet is touched. After all iterations have completed, the macro ends.
How to use the macro
The best place to store this macro is in your personal macro workbook so that the macro is always available to you. The personal macro workbook is loaded whenever you start Excel. In the VBE project window, it is named personal.xlsb.
- Activate Visual Basic Editor by pressing Alt+F11.
- Right-click personal.xlb in the project window.
- Choose Insert ⇒ Module.
- Type or paste the code in the newly created module.
If you don’t see personal.xlb in your project window, the file doesn't exist yet. You’ll have to record a macro, using personal macro workbook as the destination.
To record the macro in your personal macro workbook, display the Record Macro dialog box before you start recording. Then click the Store Macro In drop-down box and select the Personal Macro Workbook option. Simply record a few cell clicks and then stop recording. You can discard the recorded macro and replace it with this one.
Grouping Worksheets by Color
Many of us assign colors to our worksheet tabs. You can right-click any tab and select the Tab Color option (shown in Figure 5-4) to choose a color for your tab.
Figure 5-4: Choose a tab color for the sheet.
This technique allows for the visual confirmation that data in one tab is related to data in another tab because both tabs are the same color. When you have many colored sheets, it’s often useful to group tabs with the same color for ease of navigation.
The macro in this section groups worksheets based on their tab colors.
How the macro works
You may think it's impossible to sort or group by color, but Excel offers a way. Excel assigns an index number to every color. A light yellow color may have an index number of 36, whereas a maroon color has the index number 42.
This macro iterates through the sheets in the workbook, comparing the tab color index of the current sheet to that of the previous one. If the previous sheet has the same color index number as the current sheet, the macro moves the current sheet before it. By the time all the iterations are completed, all sheets are grouped based on their tab color.
Sub Macro1()
'Step 1: Declare your variables
Dim CurrentSheetIndex As Integer
Dim PrevSheetIndex As Integer
'Step 2: Set the starting counts and start looping
For CurrentSheetIndex = 1 To Sheets.Count
For PrevSheetIndex = 1 To CurrentSheetIndex - 1
'Step 3: Check current sheet against previous sheet
If Sheets(PrevSheetIndex).Tab.ColorIndex = _
Sheets(CurrentSheetIndex).Tab.ColorIndex Then
'Step 4: Move if current and previous color indexes match Sheets(PrevSheetIndex).Move _
Before:=Sheets(CurrentSheetIndex)
End If
'Step 5 Loop back to iterate again
Next PrevSheetIndex
Next CurrentSheetIndex
End Sub
Step 1 declares two integer variables. The CurrentSheetIndex variable holds the index number for the current sheet iteration, and the PrevSheetIndex variable holds the index number for the previous sheet iteration.
Step 2 starts iteration counts for both variables. Note that the count for PrevSheetIndex is one number behind CurrentSheetIndex. After the counts are set, the macro starts looping.
In Step 3, the macro checks to see whether the color index of the previous sheet is the same as that of the current sheet. Note the use of the Tab.ColorIndex property.
Step 4 is reached only if the color index of the previous sheet is equal to the color index of the current sheet. In this step, the macro uses the Move method to move the current sheet before the previous sheet.
In Step 5, the macro goes back to the start of the loop. Every iteration of the loop increments both variables up one number until the last worksheet is touched. After all of the iterations have run, the macro ends.
How to use the macro
The best place to store this macro is in your personal macro workbook so that the macro is always available to you. The personal macro workbook is loaded whenever you start Excel. In the VBE project window, it is named personal.xlsb.
- Activate Visual Basic Editor by pressing Alt+F11.
- Right-click personal.xlb in the project window.
- Choose Insert ⇒ Module.
- Type or paste the code in the newly created module.
If you don’t see personal.xlb in your project window, the file doesn't exist yet. You’ll have to record a macro, using personal macro workbook as the destination.
To record the macro in your personal macro workbook, display the Record Macro dialog box before you start recording. Then click the Store Macro In drop-down box and select the Personal Macro Workbook option. Simply record a few cell clicks and then stop recording. You can discard the recorded macro and replace it with this one.
Copying a Worksheet to a New Workbook
In Excel, you can manually copy an entire sheet to a new workbook by right-clicking the target sheet and selecting the Move or Copy option. Unfortunately, if you try to record a macro while you do this, the macro recorder fails to accurately write the code to reflect the task. When you need to programmatically copy an entire sheet to a new workbook, the macro in this section delivers.
How the macro works
In this macro, the active sheet is first copied. Then you use the Before parameter to send the copy to a new workbook that is created on the fly. The copied sheet is positioned as the first sheet in the new workbook.
The use of the ThisWorkbook object is important here. It ensures that the active sheet that is being copied is from the workbook that contains the code, not from the newly created workbook.
Sub Macro1()
'Copy sheet, and send to new workbook
ThisWorkbook.ActiveSheet.Copy _
Before:=Workbooks.Add.Worksheets(1)
End Sub
How to use the macro
To implement this macro, you can copy and paste it into a standard module:
- Activate Visual Basic Editor by pressing Alt+F11.
- Right-click the project/workbook name in the project window.
- Choose Insert ⇒ Module.
- Type or paste the code in the newly created module.
Creating a Workbook for Each Worksheet
Many Excel analysts need to parse their workbooks into separate books per worksheet tab. In other words, they need to create a new workbook for each worksheet in their existing workbook. You can imagine what an ordeal this task would be if you had to do it manually. The following macro helps automate this task.
How the macro works
In this macro, you are looping through the worksheets, copying each sheet, and then sending the copy to a new workbook that is created on the fly. The thing to note here is that the newly created workbooks are saved in the same directory as your original workbook, with the same filename as the copied sheet (wb.SaveAs ThisWorkbook.Path & "" & ws.Name).
Sub Macro1()
'Step 1: Declare your variables
Dim ws As Worksheet
Dim wb As Workbook
'Step 2: Start looping through sheets
For Each ws In ThisWorkbook.Worksheets
'Step 3: Create new workbook and save it
Set wb = Workbooks.Add
wb.SaveAs ThisWorkbook.Path & "" & ws.Name
'Step 4: Copy the target sheet to the new workbook
ws.Copy Before:=wb.Worksheets(1)
wb.Close SaveChanges:=True
'Step 5: Loop back to the next worksheet
Next ws
End Sub
Not all valid worksheet names translate to valid filenames.
Windows has specific rules regarding filenames. You can't use these characters when naming a file: backslash (), forward slash (/), colon (:), asterisk (*), question mark (?), pipe (|), double quote (“), greater than (>) and less than (<).
The twist is that you can use a few of these restricted characters in your sheet names; specifically, double quote, pipe, greater than, and less than. So, as you run this macro, naming the newly created files to match the sheet name may cause an error. For instance, the macro will throw an error if you try to create a new file from a sheet called May|Revenue (because of the pipe character).
Step 1 declares two object variables. The ws variable creates a memory container for each worksheet through which the macro loops. The wb variable creates the container for the new workbooks you create.
In Step 2, the macro starts looping through the sheets. The use of the ThisWorkbook object ensures that the active sheet that is being copied is from the workbook containing the code, not from the new workbook that is created.
In Step 3, you create the new workbook and save it. You save this new book in the same path as the original workbook (ThisWorkbook). The filename is set to the same name as the currently active sheet.
Step 4 copies the currently active sheet and uses the Before parameter to send it to the new book as the first tab.
Step 5 loops back to get the next sheet. After all sheets have been evaluated, the macro ends.
This macro will not work on a workbook that has not been initially saved — that is to say, saved at least one time.
How to use the macro
To implement this macro, you can copy and paste it into a standard module:
- Activate Visual Basic Editor by pressing Alt+F11.
- Right-click the project/workbook name in the project window.
- Choose Insert ⇒ Module.
- Type or paste the code in the newly created module.
Printing Specified Worksheets
If you want to print specific sheets manually in Excel, you need to hold down the Ctrl key, select the sheets you want to print, and then click Print. If you do this often enough, you may want to consider using the simple macro in this section.
How the macro works
This macro is easy. All you have to do is pass in an array the sheets you want printed, and then you use the PrintOut method to trigger the print job. All the sheets you have entered are printed in one go.
Sub Macro1()
'Print certain sheets
ActiveWorkbook.Sheets( _
Array("Sheet1", "Sheet3", "Sheet5")).PrintOut Copies:=1
End Sub
Want to print all worksheets in a workbook? The following macro is even easier:
Sub Macro1()
'Print all sheets
ActiveWorkbook.Worksheets.PrintOut Copies:=1
End Sub
How to use the macro
The best place to store this macro is in your personal macro workbook so that the macro is always available to you. The personal macro workbook is loaded whenever you start Excel. In the VBE project window, it is named personal.xlsb.
- Activate Visual Basic Editor by pressing Alt+F11.
- Right-click personal.xlb in the project window.
- Choose Insert ⇒ Module.
- Type or paste the code in the newly created module.
If you don’t see personal.xlb in your project window, the file doesn't exist yet. You’ll have to record a macro using Personal Macro Workbook as the destination.
To record the macro in your personal macro workbook, display the Record Macro dialog box before you start recording. Then click the Store Macro In drop-down box and select the Personal Macro Workbook option t. Simply record a couple of cell clicks and then stop recording. You can discard the recorded macro and replace it with this one.
Protecting All Worksheets
Before you distribute your workbook, you may want to apply sheet protection to all the sheets. However, as you can see in Figure 5-5, Excel will disable the Protect Sheet command if you try to protect multiple sheets at one time. You will be forced to protect one sheet at a time.
Figure 5-5: The Protect Sheet command is disabled if you try to protect more than one sheet at a time.
You can use the macro in this section to protect all sheets at one time.
How the macro works
In this macro, you loop through the worksheets and simply apply protection with a password. The Password argument defines the password needed to remove the protection. The Password argument is optional. If you omit it, the sheet will still be protected; you just won’t need to enter a password to unprotect it.
Excel passwords are case-sensitive, so you’ll want pay attention to the exact capitalization you are using in your macro.
Sub Macro1()
'Step 1: Declare your variables
Dim ws As Worksheet
'Step 2: Start looping through all worksheets
For Each ws In ActiveWorkbook.Worksheets
'Step 3: Protect and loop to next worksheet
ws.Protect Password:="RED"
Next ws
End Sub
Step 1 declares an object called ws. This step creates a memory container for each worksheet you loop through.
Step 2 starts the looping, telling Excel that you want to enumerate through all worksheets in this workbook.
In Step 3, the macro applies protection with the given password, and then loops back to get the worksheet.
How to use the macro
The best place to store this macro is in your personal macro workbook so that the macro is always available to you. The personal macro workbook is loaded whenever you start Excel. In the VBE project window, it is named personal.xlsb.
- Activate Visual Basic Editor by pressing Alt+F11.
- Right-click personal.xlb in the project window.
- Choose Insert ⇒ Module.
- Type or paste the code in the newly created module.
If you don’t see personal.xlb in your project window, the file doesn't exist yet. You’ll have to record a macro using Personal Macro Workbook as the destination.
To record the macro in your personal macro workbook, display the Record Macro dialog box before you start recording. Then click the Store Macro In drop-down box and select the Personal Macro Workbook option. Then simply record a few cell clicks and then stop recording. You can discard the recorded macro and replace it with this one.
Unprotecting All Worksheets
You may find yourself constantly having to unprotect multiple worksheets manually. However, as you can see in Figure 5-6, Excel will disable the Unprotect Sheet command if you try to unprotect multiple sheets at one time. You'll be forced to unprotect one sheet at a time.
Figure 5-6: The Unprotect Sheet command is disabled if you try to unprotect more than one sheet at a time.
You can use the macro in this section to unprotect all sheets automatically.
How the macro works
The macro loops through the worksheets and uses the Password argument to unprotect each sheet:
Sub Macro1()
'Step 1: Declare your variables
Dim ws As Worksheet
'Step 2: Start looping through all worksheets
For Each ws In ActiveWorkbook.Worksheets
'Step 3: Loop to next worksheet
ws.UnProtect Password:="RED"
Next ws
End Sub
Step 1 declares an object called ws. This step creates a memory container for each worksheet you loop through.
Step 2 starts the looping, telling Excel to enumerate through all worksheets in this workbook.
Step 3 unprotects the active sheet, providing the password as needed, and then loops back to get the worksheet.
The assumption is that all worksheets that need to be unprotected have the same password. If this not the case, you need to explicitly unprotect each sheet with its corresponding password:
Sub Macro1()
Sheets("Sheet1").UnProtect Password:="RED"
Sheets("Sheet2").UnProtect Password:="BLUE"
Sheets("Sheet3").UnProtect Password:="YELLOW"
Sheets("Sheet4").UnProtect Password:="GREEN"
End Sub
How to use the macro
The best place to store this kind of a macro is in your personal macro workbook so that the macro is always available to you. The personal macro workbook is loaded whenever you start Excel. In the VBE project window, it will be named personal.xlsb.
- Activate Visual Basic Editor by pressing Alt+F11.
- Right-click personal.xlb in the project window.
- Choose Insert ⇒ Module.
- Type or paste the code in the newly created module.
If you don’t see personal.xlb in your project window, the file doesn't exist yet. You’ll have to record a macro, using Personal Macro Workbook as the destination.
To record the macro in your personal macro workbook, display the Record Macro dialog box before you start recording. Then click the Store Macro In drop-down box and select the Personal Macro Workbook option. Simply record a few cell clicks and then stop recording. You can discard the recorded macro and replace it with this one.
Creating a Table of Contents for Your Worksheets
With the exception of sorting worksheets, creating a table of contents for the worksheets in a workbook is the most commonly requested Excel macro. The reason is probably not lost on you because you often work with files that have more worksheet tabs than can easily be seen or navigated. A table of contents like the one in Figure 5-7 helps.
Figure 5-7: A table of contents can help you more easily navigate your workbook.
The following macro not only creates a list of worksheet names in the workbook but also ads hyperlinks so that you can easily jump to a sheet with a simple click.
How the macro works
It’s easy to get intimidated when looking at the macro in this section because a lot is going on. However, if you step back and consider the few simple actions it does, it becomes less scary. The macro
- Removes any previous Table of Contents sheet
- Creates a new Table of Contents sheet
- Grabs the name of each worksheet and pastes it to the table of contents
- Adds a hyperlink to each entry in the table of contents
That doesn’t sound so bad. Now look at the code:
Sub Macro1()
'Step 1: Declare your variables
Dim i As Long
'Step 2: Delete previous TOC if it exists
On Error Resume Next
Application.DisplayAlerts = False
Sheets("Table Of Contents").Delete
Application.DisplayAlerts = True
On Error GoTo 0
'Step 3: Add a new TOC sheet as the first sheet
ThisWorkbook.Sheets.Add _
Before:=ThisWorkbook.Worksheets(1)
ActiveSheet.Name = "Table Of Contents"
'Step 4: Start the i counter
For i = 1 To Sheets.Count
'Step 5: Select next available row
ActiveSheet.Cells(i, 1).Select
'Step 6: Add sheet name and hyperlink
ActiveSheet.Hyperlinks.Add _
Anchor:=ActiveSheet.Cells(i, 1), _
Address:="", _
SubAddress:="'" & Sheets(i).Name & "'!A1", _
TextToDisplay:=Sheets(i).Name
'Step 7: Loop back and increment i
Next i
End Sub
Step 1 declares an Integer variable called i to serve as the counter as the macro iterates through the sheets.
Note that this macro is not looping through the sheets the way previous macros in this chapter did. In previous macros, you looped through the Worksheets collection and selected each worksheet there. In this procedure, you use a counter (your i variable). The main reason is because you have to not only keep track of the sheets but also enter each sheet name on a new row in a table of contents. The idea is that as the counter progresses through the sheets, it also serves to move the cursor down in the table of contents so each new entry goes on a new row.
Step 2 essentially attempts to delete any previous sheet called Table of Contents. Because there may not be any Table of Contents sheet to delete, you have to start Step 2 with the On Error Resume Next error handler. This handler tells Excel to continue the macro if an error is encountered here. You then delete the Table of Contents sheet using the DisplayAlerts method, which effectively turns off Excel’s warnings so you don’t have to confirm the deletion. Finally, you reset the error handler to trap all errors again by entering On Error GoTo 0.
In Step 3, you add a new sheet to the workbook by using the Before argument to position the new sheet as the first sheet. You then name the sheet Table of Contents. As mentioned, when you add a new worksheet, it automatically becomes the active sheet. Because this new sheet has the focus throughout the procedure, any references to ActiveSheet in this code refer to the Table of Contents sheet.
Step 4 starts the i counter at 1 and ends it at the maximum count of all sheets in the workbook. Again, instead of looping through the Worksheets collection like you did in previous macros, you simply use the i counter as an index number that you can pass to the Sheets object. When the maximum number is reached, the macro ends.
Step 5 selects the corresponding row in the Table of Contents sheet. That is to say, if the i counter is on 1, it selects the first row in the Table of Contents sheet. If the i counter is at 2, it selects the second row, and so on.
You select rows by using the Cells item, which provides a handy way of selecting ranges through code. It requires only relative row and column positions as parameters. So Cells(1,1) translates to row 1, column 1 (or cell A1). Cells(5, 3) translates to row 5, column 3 (or cell C5). The numeric parameters in the Cells item are particularly useful when you want to loop through a series of rows or columns by using an incrementing index number.
Step 6 uses the Hyperlinks.Add method to add the sheet name and hyperlinks to the selected cell. This step feeds the Hyperlinks.Add method the parameters it needs to build out the hyperlinks.
The last step in the macro loops back to increment the i counter to the next count. When the i counter reaches a number that equals the count of worksheets in the workbook, the macro ends.
How to use the macro
To implement this macro, you can copy and paste it into a standard module:
- Activate Visual Basic Editor by pressing Alt+F11.
- Right-click project/workbook name in the project window.
- Choose Insert ⇒ Module.
- Type or paste the code in the newly created module.
Zooming In and Out of a Worksheet with Double-Click
Some spreadsheets are huge and you're forced to shrink the font size down so that you can see a decent portion of the spreadsheet on the screen. If you find that you are constantly zooming in and out of a spreadsheet, alternating between scanning large sections of data and reading specific cells, use the handy macro in this section, which will auto-zoom on a double-click.
How the macro works
With this macro in place, you can double-click a cell in the spreadsheet to zoom in 200 percent. Double-click again and Excel zooms back to 100 percent. You can change the values and complexity in the code to fit your needs.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Check current zoom state
'Zoom to 100% if at 100
'Zoom 200% if currently at 100
If ActiveWindow.Zoom <> 100 Then
ActiveWindow.Zoom = 100
Else
ActiveWindow.Zoom = 200
End If
End Sub
The side effect of double-clicking a cell is that it goes into edit mode. You can exit edit mode by pressing the escape key (Esc). If you find it annoying to keep pressing Esc when triggering this macro, add the following statement to the end of the procedure:
Application.SendKeys ("{ESC}")
This statement mimics an Esc keypress.
How to use the macro
To implement this macro, you need to copy and paste it into the Worksheet_BeforeDoubleClick event code window. Placing the macro there allows it to run each time you double-click the sheet.
- Activate Visual Basic Editor by pressing Alt+F11.
- In the project window, find your project/workbook name and click the plus sign next to it to see all the sheets.
- Click the sheet from which you want to trigger the code.
- In the Event drop-down list, select the BeforeDoubleClick event (see Figure 5-8).
- Type or paste the code in the newly created module.
Figure 5-8: Enter your code in the Worksheet BeforeDoubleClick event.
Highlighting the Active Row and Column
When looking at a table of numbers, it would be nice if Excel could automatically highlight the row and column you’re on (as demonstrated in Figure 5-9). This effect gives your eyes a lead line up and down the column as well as left and right across the row.
Figure 5-9: The highlighted row and column make it easy to track data horizontally and vertically.
The following macro enables the effect you see in Figure 5-9 with just a simple double-click. When the macro is in place, Excel highlights the row and column for the active cell, greatly improving your ability to view and edit a large grid.
How the macro works
Take a look at how this macro works:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Step 1: Declare your variables
Dim strRange As String
'Step 2: Build the range string
strRange = Target.Cells.Address & "," & _
Target.Cells.EntireColumn.Address & "," & _
Target.Cells.EntireRow.Address
'Step 3: Pass the range string to a range
Range(strRange).Select
End Sub
In Step 1, you first declare an object called strRange. This step creates a memory container you can use to build a range string.
A range string is nothing more than the address for a range. “A1” is a range string that points to cell A1. “A1:G5” is also a range string; it points to a range of cells encompassing cells A1 to G5. In Step 2, you're building a range string that encompasses the double-clicked cell (called Target in this macro), the entire active row, and the entire active column. The Address property for these three ranges is captured and pieced together into the strRange variable.
In Step 3, you feed the strRange variable as the address for a Range.Select statement. This line of the code finally highlights the double-clicked selection.
How to use the macro
To implement this macro, you need to copy and paste it into the Worksheet_BeforeDoubleClick event code window. Placing the macro there allows it to run each time you double-click on the sheet.
- Activate Visual Basic Editor by pressing Alt+F11.
- In the project window, find your project/workbook name and click the plus sign next to it to see all the sheets.
- Click the sheet from which you want to trigger the code.
- In the Event drop-down list, select the BeforeDoubleClick event (see Figure 5-10).
- Type or paste the code in the newly created module.
Figure 5-10: Enter your code in the Worksheet BeforeDoubleClick event.