Macro 1: Creating a New Workbook from Scratch
You may sometimes want or need to create a new workbook in an automated way. For instance,
you may need to copy data from a table and paste it into a newly created workbook. The following macro copies a range of cells from the active sheet and pastes the data into a new workbook.
How it works
This macro is relatively intuitive as you read through the lines of the code.
Sub Macro1()
‘Step 1 Copy the data
Sheets(“Example 1”).Range(“B4:C15”).Copy
‘Step 2 Create a new workbook
Workbooks.Add
‘Step 3 Paste the data
ActiveSheet.Paste Destination:=Range(“A1”)
‘Step 4 Turn off application alerts
Application.DisplayAlerts = False
‘Step 5 Save the newly created workbook ActiveWorkbook.SaveAs _ Filename:=”C:\Temp\MyNewBook.xlsx”
‘Step 6 Turn application alerts back on
Application.DisplayAlerts = True
End Sub
Macro 2: Saving a Workbook When a Particular
Cell Is Changed
Private Sub Worksheet_Change(ByVal Target As Range)
‘Step 1: Does the changed range intersect specified range? If Intersect(Target, Range(“C5:C16”)) Is Nothing Then
‘Step 2: If there is no intersection, exit procedure
Exit Sub
‘Step 3: If there is an intersection, save the workbook
Else
ActiveWorkbook.Save
‘Close out the If statement
End If
End Sub
Macro 3: Saving a Workbook Before Closing
This macro is an excellent way to protect users from inadvertently closing their file before saving. When implemented, this macro ensures that Excel automatically saves before closing the workbook.
Select Case
Case Is=
Case Is=
With a Select Case statement, you can perform many conditional checks. In this case, we are simply checking for OK or Cancel. Take a look at the code.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
‘Step 1: Activate the message box and start the check
Select Case MsgBox(“Save and close?”, vbOKCancel)
‘Step 2: Cancel button pressed, cancel the close
Case Is = vbCancel
Cancel = True
‘Step 3: OK button pressed, save the workbook and close
Case Is = vbOK ActiveWorkbook.Save
‘Step 4: Close your Select Case statement
End Select
End Sub
Macro 4: Protect a Worksheet on Workbook Close
Sometimes you need to send your workbook out into the world with specific worksheets protected. If you find that you're constantly protecting and unprotecting sheets before distributing your workbooks, this macro can help you.
How it works
This code is triggered by the workbook's BeforeClose event. When you try to close the workbook, this event fires, running the code within. The macro automatically protects the specified sheet with the given password, and then saves the workbook.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
‘Step 1: Protect the sheet with a password
Sheets(“Sheet1”).Protect Password:=”RED”
‘Step 2: Save the workbook
ActiveWorkbook.Save
End Sub
Macro 5: Unprotect a Worksheet on Workbook
Open
If you've distributed workbooks with protected sheets, you likely get the workbooks back with the sheets still protected. Often, you need to unprotect the worksheets in a workbook before continuing your work. If you find that you are continuously unprotecting worksheets, this macro may be just
the ticket.
How it works
This code is triggered by the workbook's Open event. When you open a workbook, this event
triggers, running the code within. This macro automatically unprotects the specified sheet with the given password when the workbook is opened.
PrivateSubWorkbook_Open()
‘Step 1: Protect the sheet with a password Sheets(“Sheet1”).Unprotect Password:=”RED” End Sub
The macro explicitly names the sheet we want to unprotect — Sheet1, in this case. Then it passes the password required to unprotect the sheet. Be aware that Excel passwords are case-sensitive, so pay attention to the exact password and capitalization that you are using.
Macro 6: Open a Workbook to a Specific Tab
In some situations, it's imperative that your workbook be started on a specific worksheet. With this macro, if a user is working with your workbook, they can't go astray because the workbook starts
How it works
This macro uses the workbook's Open event to start the workbook on the specified sheet when the workbook is opened.
Private Sub Workbook_Open()
‘Step 1: Select the specified sheet
Sheets(“Sheet1”).Select
End Sub
The macro explicitly names the sheet the workbook should jump to when it's opened.
Macro 7: Opening a Specific Workbook Defined by the User
Want to give yourself or your users a quick way to search for and open a file? This macro uses a simple technique that opens a friendly dialog box, allowing you to browse for and open the Excel file of your choosing.
Sub Macro7()
‘Step 1: Define a string variable. Dim FName As Variant
‘Step 2: GetOpenFilename Method activates dialog box. FName = Application.GetOpenFilename( _ FileFilter:=”Excel Workbooks,*.xl*”, _
Title:=”Choose a Workbook to Open”, _ MultiSelect:=False)
‘Step 3: If a file was chosen, open it! If FName <> False Then
Workbooks.Open Filename:=FName
End If
End Sub
Macro 8: Determine Whether a Workbook Is
Already Open
The previous macro automatically opened a workbook based on the user's selection. As we think about automatically opening workbooks, you must consider what may happen if you attempt to open a book that is already open. In the non-VBA world, Excel attempts to open the file again, with a warning that any unsaved changes will be lost. In VBA, it's a good idea to protect against such an occurrence by checking if a given file is already open before trying to open it again.
The first thing to notice about this macro is that it is a function, not a sub procedure. As you will see, making this macro a function enables us to pass any filename to it to test whether that file is already open.
The gist of this code is simple. We are testing a given filename to see if it can be assigned to an object variable. Only opened workbooks can be assigned to an object variable. When we try to assign a closed workbook to the variable, an error occurs.
So if the given workbook can be assigned, the workbook is open; if an error occurs, the workbook is closed.
Function FileIsOpenTest(TargetWorkbook As String) As Boolean
‘Step 1: Declare variables
Dim TestBook As Workbook
‘Step 2: Tell Excel to Resume on Error
On Error Resume Next
‘Step 3: Try to assign the target workbook to TestBook
Set TestBook = Workbooks(TargetWorkbook)
‘Step 4: If no error occurred then Workbook is already open
If Err.Number = 0 Then FileIsOpenTest = True Else
FileIsOpenTest = False
End If
End Function
Sub Macro8()
‘Step 1: Define a string variable. Dim FName As Variant
Dim FNFileOnly As String
‘Step 2: GetOpenFilename Method activates dialog box. FName = Application.GetOpenFilename( _ FileFilter:=”Excel Workbooks,*.xl*”, _
Title:=”Choose a Workbook to Open”, _ MultiSelect:=False)
‘Step 3: Open the chosen file if not already opened. If FName <> False Then
FNFileOnly = StrReverse(Left(StrReverse(FName), _ InStr(StrReverse(FName), “\”) - 1))
If FileIsOpenTest(FNFileOnly) = True Then
MsgBox “The given file is already open” Else
Workbooks.Open Filename:=FName
End If End If End Sub
Macro 9: Determine Whether a Workbook Exists in a Directory
You may have a process that manipulates a file somewhere on your PC. For example, you may need to open an existing workbook to add new data to it on a daily basis. In these cases, you may need to test to see whether the file you need to manipulate actually exists. This macro allows you to pass a file path to evaluate whether the file is there.
How it works
The first thing to notice about this macro is that it is a function, not a sub procedure. Making this macro a function enables us to pass any file path to it.
In this macro, we use the Dir function. The Dir function returns a string that represents the name of the file that matches what you pass to it. This function can be used in lots of ways, but here, we are using it to check if the file path we pass to it exists.
Function FileExists(FPath As String) As Boolean
‘Step 1: Declare your variables. Dim FName As String
‘Step 2: Use the Dir function to get the file name
FName = Dir(FPath)
‘Step 3: If file exists, return True else False
If FName <> “” Then FileExists = True _ Else: FileExists = False
End Function
1. Step 1 declares a string variable that holds the filename that returns from the Dir function.
FName is the name of the string variable.
2. In Step 2, we attempt to set the FName variable. We do this by passing the FPath variable to the Dir function. This FPath variable is passed via the function declarations (see the first line of the code). This structure prevents us from having to hard-code a file path, passing it as a variable instead.
3. If the FName variable can't be set, this means the path we passed does not exist. Thus the
FName variable is empty. Step 3 merely translates that result to a True or False expression.
Sub Macro9)
If FileExists(“C:\Temp\MyNewBook.xlsx”) = True Then
MsgBox “File exists.” Else
MsgBox “File does not exist.” End If
End Sub
Macro 10: Refresh All Data Connections in
Workbook on Open
Your workbook may have connections to external data sources such as web queries, MSQuery connections, PivotTable connections, and so on. In these cases, it may be helpful to refresh these data connections automatically when the workbook is opened. This macro does the trick.
How it works
This macro is an easy one-liner that uses the RefreshAll method. This method refreshes all the connections in a given workbook or worksheet. In this case, we are pointing it to the entire workbook.
Private Sub Workbook_Open()
‘Step 1: Use the RefreshAll method Workbooks(ThisWorkbook.Name).RefreshAll End Sub
The thing to note in this macro is that we are using the ThisWorkbook object. This object is an easy and safe way for you to point to the current workbook. The difference between ThisWorkbook and ActiveWorkbook is subtle but important. The ThisWorkbook object refers to the workbook that the code is contained in. The ActiveWorkbook object refers to the workbook that is currently active. 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 refreshing connections in other workbooks, so you use ThisWorkbook..
Macro 11: Close All Workbooks at Once
One of the more annoying things in Excel is closing many workbooks at once. For each workbook you have opened, you need to activate the work, close it, and confirm save changes. There is no
easy way to close them all down at one time. This little macro takes care of that annoyance.
How it works
In this macro, the Workbooks collection loops through all the open workbooks. As the macro loops through each workbook, it saves and closes them down.
Sub Macro11()
‘Step 1: Declare your variables
Dim wb As Workbook
‘Step 2: Loop through workbooks, save and close
For Each wb In Workbooks wb.Close SaveChanges:=True Next wb
End Sub
1. Step 1 declares an object variable that represents a Workbook object. This allows us to enumerate through all the open workbooks, capturing their names as we go.
2. Step 2 simply loops through the open workbooks, saving and closing them. If you don't want to save them, change the SaveChanges argument from True to False.
Macro 12: Open All Workbooks in a Directory
Here's a scenario: You've written a cool macro that applies some automated processing to each
workbook you open. Now the problem is that you need to go into your directory, open each workbook, run the macro, save it, close the workbook, and then open the next one. Opening each workbook in a directory is typically a time-consuming manual process. This macro solves that problem.
How it works
In this macro, we use the Dir function. The Dir function returns a string that represents the name of the file that matches what you pass to it.
In this code, we use the Dir function to enumerate through all the .xlsx files in a given directory, capturing each file's name. Then we open each file, run some code, and finally close the file after saving.
Sub Macro12()
‘Step 1:Declare your variables
Dim MyFiles As String
‘Step 2: Specify a target directory
MyFiles = Dir(“C:\Temp\*.xlsx”) Do While MyFiles <> “”
‘Step 3: Open Workbooks one by one
Workbooks.Open “C:\Temp\” & MyFiles
‘run some code here
MsgBox ActiveWorkbook.Name
ActiveWorkbook.Close SaveChanges:=True
‘Step 4: Next File in the Directory
MyFiles = Dir
Loop
End Sub
1. Step 1 declares the MyFiles string variable that will capture each filename that is in the enumeration.
2. In Step 2, the macro uses the Dir function to specify the directory and file type we are
looking for. Note that the code here is looking for *.xlsx. This means that only .xlsx files will be looped through. If you are looking for .xls files, you need to change that (along with the
directory you need to search). This macro passes any filename it finds to the MyFiles string variable.
3. Step 3 opens the file, does some stuff (this is where you would put in any macro code to perform the desired actions), and then we save and close the file. In this simple example, we are calling a message box to show each filename as it opens.
4. The last step of the macro loops back to find more files. If there are no more files, the
MyFiles variable will be blank. If that is the case, the loop and macro end.
Macro 13: Print All Workbooks in a Directory
If you need to print from multiple workbooks in a directory, you can use this macro.
How it works
In this macro, we use the Dir function to return a string that represents the name of the file that matches what you pass to it.
In this code, we use the Dir function to enumerate through all the .xlsx files in a given directory, capturing each file's name. Then we open each file, print, and close the file.
Sub Macro13()
‘Step 1:Declare your variables
Dim MyFiles As String
‘Step 2: Specify a target directory
MyFiles = Dir(“C:\Temp\*.xlsx”) Do While MyFiles <> “”
‘Step 3: Open Workbooks one by one Workbooks.Open “C:\Temp\” & MyFiles ActiveWorkbook.Sheets(“Sheet1”).PrintOut Copies:=1
ActiveWorkbook.Close SaveChanges:=False
‘Step 4: Next File in the Directory
MyFiles = Dir
Loop
End Sub
1. Step 1 declares the MyFiles string variable that will capture each filename that is in the enumeration.
2. Step 2 uses the Dir function to specify the directory and file type we are looking for. Note
that the code here is looking for *.xlsx. This means that only .xlsx files will be looped through. If you are looking for .xls files, you will need to specify that (along with the directory you need to search). The macro passes any filename it finds to the MyFiles string variable.
3. Step 3 opens the file and then prints out one copy of Sheet1. Needless to say, you will probably want to change which sheets to print. You can also change the number of copies to print.
4. Step 4 loops back to find more files. If there are no more files, the MyFiles variable is blank. If that is the case, the loop and macro end.
Macro 14: Preventing the Workbook from Closing
Until a Cell Is Populated
There are times when you don't want a user closing out a workbook without entering a specific piece of data. In these situations, you want Excel to deny the user the ability to close the workbook until the target cell is filled in. This is where this macro comes in.
How it works
This code is triggered by the workbook's BeforeClose event. When you try to close the workbook, this event fires, running the code within. This macro checks to see if the target cell
(cell C7, in this case) is empty. If it is empty, the close process is cancelled. If C7 is not empty, the workbook saves and closes.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
‘Step 1: Check to see if Cell C7 is blank
If Sheets(“Sheet1”).Range(“C7”).Value = “” Then
‘Step 2: Blank: cancel the Close and tell the user
Cancel = True
MsgBox “Cell C7 cannot be blank”
‘Step 3: Not Blank; Save and Close
Else
ActiveWorkbook.Close SaveChanges:=True
End If
End Sub
Macro 15: Create a Backup of a Current
Workbook with Today's Date
We all know that making backups of your work is important. Now you can have a macro do it for you. This simple macro saves your workbook to a new file with today's date as part of the name.
How it works
The trick to this macro is piecing together the new filename. The new filename has three pieces:
the path, today's date, and the original filename.
The path is captured by using the Path property of the ThisWorkbook object. Today's date is grabbed with the Date function.
You'll notice that we are formatting the date (Format(Date, “mm-dd-yy”)). This is because by default, the Date function returns mm/dd/yyyy. We use hyphens instead of forward slashes because the forward slashes would cause the file save to fail. (Windows does not allow forward slashes in filenames.)
The last piece of the new filename is the original filename. We use the Name property of the
ThisWorkbook object to capture that:
Sub Macro15()
‘Step 1: Save workbook with new filename ThisWorkbook.SaveCopyAs _ Filename:=ThisWorkbook.Path & “\” & _ Format(Date, “mm-dd-yy”) & “ “ & _ ThisWorkbook.Name
End Sub
In the one and only step, the macro builds a new filename and uses the SaveCopyAs method to save the file.
Part III: Automating Worksheet Tasks with
Macros
This Part addresses macros that work at the worksheet level. In this Part, you'll find macros for adding, deleting, and renaming worksheets, and much more.
In This Part
Macro 16 Add and Name a New Worksheet
Macro 17 Delete All but the Active Worksheet
Macro 18 Hide All but the Active Worksheet
Macro 19 Unhide All Worksheets in a Workbook
Macro 20 Moving Worksheets Around
Macro 21 Sort Worksheets by Name
Macro 22 Group Worksheets by Color
Macro 23 Copy a Worksheet to a New Workbook
Macro 24 Create a New Workbook for Each Worksheet
Macro 25 Print Specified Worksheets
Macro 26 Protect All Worksheets
Macro 27 Unprotect All Worksheets
Macro 28 Create a Table of Contents for Your Worksheets
Macro 29 Zooming In and Out of a Worksheet with Double-Click
Macro 30 Highlight the Active Row and Column
Excel analysts often need to automate tasks related to worksheets. Whether it is un-hiding all sheets in a workbook, or printing all sheets at the same time, many tasks can be automated to save time and gain efficiencies. In this Part, we cover some of the more useful macros related to worksheets.
Macro 16: Add and Name a New Worksheet
We start off this chapter with one of the simplest worksheet-related automations you can apply with a macro — adding and naming a new worksheet.
How it works
If you read through the lines of the code, you'll see this macro is relatively intuitive.
Sub Macro16()
‘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
Here's how this macro works:
1. You must anticipate that if you give the new sheet a name that already exists, an error occurs. 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.
2. 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. We give the sheet a new name by changing the Name property of the ActiveSheet object. In this case, we are naming the worksheet with the current date and time.
As with workbooks, each time you add a new sheet via VBA, it automatically becomes the active sheet. Finally, in Step 2, notice that the macro exits the procedure. It has to do this so that it doesn't accidentally go into Step 3 (which should come into play only if an error occurs).
3. Step 3 notifies the user that the sheet name already exists. Again, this step should only be activated if an error occurs.
Macro 17: Delete All but the Active Worksheet
At times, you may want to delete all but the active worksheet. In these situations, you can use this macro.
How it works
This macro loops the worksheets and matches 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 method in Step 4. This effectively turns off Excel's warnings so you don't have to confirm each delete.
Sub Macro17()
‘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
1. The macro first declares an object called ws. This creates a memory container for each worksheet it loops through.
2. 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 the code is contained in. The ActiveWorkbook object refers to the workbook that is currently active. 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, we don't want to risk deleting sheets in other workbooks, so we use ThisWorkbook.
3. In this step, the macro simply compares the active sheet name to the sheet that is currently being looped.
4. If the sheet names are different, the macro deletes the sheet. As mentioned before, we use
DisplayAlerts to suppress any confirmation checks from Excel.
5. In Step 5, the macro loops back to get the next sheet. After all sheets are evaluated, the macro ends.
Macro 18: Hide All but the Active Worksheet
You may not want to delete all but the active sheet as we did in the last 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 showing. However, you can hide all but the active sheet.
How it works
This macro loops the worksheets and matches each worksheet name to the active sheet's name. Each time the macro loops, it hides any unmatched worksheet.
Sub Macro18()
‘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
Macro 19: Unhide All Worksheets in a Workbook
If you've ever had to unhide multiple sheets in Excel, you know what a pain it is. You need to click four times for each sheet you want to unhide. Although that may not sound like a lot, try to unhide ten or more sheets — it gets to be a pain fast. This macro makes easy work of that task.
How it works
This macro loops the worksheets and changes the visible state.
Sub Macro19()
‘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
Macro 20: Moving Worksheets Around
We've all had to rearrange our spreadsheet so that some sheet came before or after other sheet. If you find that you often have to do this, here is a macro that can help.
How it 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 need to specify where to move the sheet to. You can do this using the After argument, the Before argument, or both.
Sub Macro20()
‘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 does three things. First, it moves the active sheet to the end. Nothing in VBA lets you point to “the last sheet.” But you can find the maximum count of worksheets, and then use that number as an index for the Worksheets object. This means that we can enter something like Worksheets(3) to point to the third sheet in a workbook. Thus, you can use Worksheet(Worksheets.Count) to point to the last sheet.
Next, this macro moves the active sheet to the beginning of the workbook. This one is simple; we use Worksheets(1) to point to the first sheet in the workbook, and then move the active sheet before that one.
Finally, the macro demonstrates that you can move sheets simply by calling them out by name. In this example, we are moving Sheet1 before Sheet12.
If you don't see personal.xlb in your project window, it means it 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, select the Personal Macro Workbook
option in the Record Macro dialog box before you start recording. This option is in the Store Macro In drop-down list. Simply record a couple of cell clicks and then stop recording. You can discard the recorded macro and replace it with this one.
Macro 21: Sort Worksheets by Name
You may often need to sort worksheets alphabetically by name. 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 anymore, you can use this macro to do it for you.
How it works
This macro looks more complicated than it is. The activity in this macro is actually fairly simple. It simply iterates through the sheets in the workbook, comparing the current sheet to the previous one. If the name of the previous sheet is greater than the current sheet (alphabetically), the macro moves the current sheet before it. By the time all the iterations are done, you've got a sorted workbook!
Sub Macro21()
‘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: If Move Current sheet Before Previous Sheets(CurrentSheetIndex).Move _ Before:=Sheets(PrevSheetIndex)
End If
‘Step 5 Loop back around to iterate again
Next PrevSheetIndex Next CurrentSheetIndex End Sub
Note that this technique is doing a text-based sort, so you may not get the results you were expecting when working with number-based sheet names. For instance, Sheet10 comes before Sheet2 because textually,
1 comes before 2. Excel doesn't do the numbers-based sorting that says 2 comes before 10.
1. Step 1 declares two integer variables. The CurrentSheetIndex holds the index number for the current sheet iteration, and the PrevSheetIndex variable holds the index number for the previous sheet iteration.
2. In Step 2, the macro starts iteration counts for both variables. Note that the count for the
PrevSheetIndex is one number behind the CurrentSheetIndex. After the counts are set, we
start looping.
3. In Step 3, we check to see whether the name of the previous sheet is greater than that of the current sheet.
In this step, note the use of the UCase function. We use this to get both names in the same uppercase state. This prevents sorting errors due to differing case states.
4. Step 4 is reached only if the previous sheet name is greater than the current sheet name. In this step, we use the Move method to move the current sheet before the previous sheet.
5. In Step 5, we 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 been spent, the macro ends.
If you don't see personal.xlb in your project window, it means it 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, select the Personal Macro Workbook option in the Record Macro dialog box before you start recording. This option is in the Store Macro In drop-down list. Simply record a couple of cell clicks and then stop recording. You can discard the recorded macro and replace it with this one.
Macro 22: Group Worksheets by Color
Many of us assign colors to our worksheet tabs. This allows for the visual confirmation that the data in a certain tab is somehow related to another tab because both have the same color. This macro groups worksheets based on their tab colors.
How it 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 done, all of the sheets are grouped together based on their tab colors.
Sub Macro22()
‘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: If Move Current sheet Before Previous Sheets(PrevSheetIndex).Move _ Before:=Sheets(CurrentSheetIndex)
End If
‘Step 5 Loop back around to iterate again
Next PrevSheetIndex Next CurrentSheetIndex End Sub
1. Step 1 declares two integer variables. The CurrentSheetIndex holds the index number for the current sheet iteration, and the PrevSheetIndex variable holds the index number for the previous sheet iteration.
2. Step 2 starts iteration counts for both variables. Note that the count for the PrevSheetIndex is one number behind the CurrentSheetIndex. After the counts are set, the macro starts looping.
3. 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.
4. 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.
5. In Step 5, the macro goes 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 of the iterations have run, the macro ends.
If you don't see personal.xlb in your project window, it 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, select the Personal Macro Workbook option in the Record Macro dialog box before you start recording. This option is in the Store Macro In drop-down list. Simply record a couple of cell clicks and then stop recording. You can discard the recorded macro and replace it with this one.
Macro 23: Copy 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. So if you need to programmatically copy an entire sheet to a brand new workbook, this macro delivers.
How it works
In this macro, the active sheet is first being copied. Then we 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. This ensures that the active sheet that is being copied is from the workbook that the code is in, not the new workbook that is created.
Sub Macro23()
‘Copy sheet, and send to new workbook ThisWorkbook.ActiveSheet.Copy _ Before:=Workbooks.Add.Worksheets(1) End Sub
Macro 24: Create a New 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 of the worksheets in their existing workbook. You can imagine what an ordeal this would be if you were to do it manually. The following macro helps automate that task.
How it works
In this macro, you are looping 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 being saved in the same directory as your original workbook, with the same filename as the copied sheet (wb.SaveAs ThisWorkbook.Path & “\” & ws.Name).
Sub Macro24()
‘Step 1: Declare all the variables. Dim ws As Worksheet
Dim wb As Workbook
‘Step 2: Start the 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 around to the next worksheet
Next ws
End Sub
Not all valid worksheet names translate to valid filenames. Windows has specific rules that prevent you from naming files with certain characters. You cannot 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 (<).
As you're running this macro, naming the newly created files to match the sheet name may cause an error. For instance, the macro throws an error when creating a new file from a sheet called May| Revenue (because of the pipe character). To make a long story short, avoid naming your worksheets with these restricted characters.
1. Step 1 declares two object variables. The ws variable creates a memory container for each worksheet the macro loops through. The wb variable creates the container for the new workbooks we create.
2. 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 the code is in, not the new workbook that is created.
3. In Step 3, we create the new workbook and save it. We save this new book in the same path as the original workbook (ThisWorkbook). The filename is set to be the same name as the currently active sheet.
4. Step 4 copies the currently active sheet and uses the Before parameter to send it to the new book as the first tab.
5. Step 5 loops back to get the next sheet. After all of the sheets are evaluated, themacro ends.
Macro 25: Print Specified Worksheets
If you want to print specific sheets manually in Excel, you need to hold down the CTRL key on the keyboard, select the sheets you want to print, and then click Print. If you do this often enough, you may consider using this very simple macro.
How it works
This one is easy. All we have to do is pass the sheets we want printed in an array as seen here in this macro. Then we use the PrintOut method to trigger the print job. All the sheets you have entered are printed in one go.
Sub Macro25()
‘Print Certain Sheets
ActiveWorkbook.Sheets( _
Array(“Sheet1”, “Sheet3”, “Sheet5”)).PrintOut Copies:=1
End Sub
Want to print all worksheets in a workbook? This one is even easier.
Sub Macro25()
‘Print All Sheets
ActiveWorkbook.Worksheets.PrintOut Copies:=1
End Sub
If you don't see personal.xlb in your project window, it means it 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, select the Personal Macro Workbook option in the Record Macro dialog box before you start recording. This option is in the Store Macro In drop-down list. Simply record a couple of cell clicks and then stop recording. You can discard the recorded macro and replace it with this one.
Macro 26: Protect All Worksheets
Before you distribute your workbook, you may want to apply sheet protection to all of the sheets. Instead of protecting each sheet manually, you can use this macro.
How it works
In this macro, you are looping the worksheets and simply applying protection with a password. The Password argument defines the password needed to remove the protection. The Password argument is completely optional. If you omit it altogether, the sheet will still be protected; you just won't need to enter a password to unprotect it. Also, be aware that Excel passwords are case- sensitive, so you'll want to pay attention to the exact capitalization you are using.
Sub Macro26()
‘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
1. Step 1 declares an object called ws. This creates a memory container for each worksheet we loop through.
2. Step 2 starts the looping, telling Excel we want to enumerate through all worksheets in this workbook.
3. In Step 3, the macro applies protection with the given password, and then loops back to get the worksheet.
If you don't see personal.xlb in your project window, it 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, select the Personal Macro Workbook option in the Record Macro dialog box before you start recording. This option is in the Store Macro In drop-down list. Simply record a couple of cell clicks and then stop recording. You can discard the recorded macro and replace it with this one.
Macro 27: Unprotect All Worksheets
You may find yourself constantly having to unprotect multiple worksheets manually. The following macro does the same thing programmatically.
How it works
This macro loops the worksheets and uses the Password argument to unprotect each sheet.
Sub Macro27()
‘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
1. Step 1 declares an object called ws. This creates a memory container for each worksheet we loop through.
2. Step 2 starts the looping, telling Excel to enumerate through all worksheets in this workbook.
3. Step 3 unprotects the active sheet, providing the password as needed, and then loops back to get the worksheet.
Obviously, the assumption is that all the worksheets that need to be unprotected have the same
password. If this is not the case, you need to explicitly unprotect each sheet with its corresponding password.
Sub Macro27b()
Sheets(“Sheet1”).UnProtect Password:=”RED” Sheets(“Sheet2”).UnProtect Password:=”BLUE” Sheets(“Sheet3”).UnProtect Password:=”YELLOW” Sheets(“Sheet4”).UnProtect Password:=”GREEN” End Sub
If you don't see personal.xlb in your project window, it means it 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, select the Personal Macro Workbook option in the Record Macro dialog box before you start recording. This option is in the Store Macro In drop-down list. Simply record a couple of cell clicks and then stop recording. You can discard the recorded macro and replace it with this one.
Macro 28: Create a Table of Contents for Your
Worksheets
Outside 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. We often have to work with files that have more worksheet tabs than can easily be seen or navigated. A table of contents definitely helps.
The following macro not only creates a list of worksheet names in the workbook, but it also ads hyperlinks so that you can easily jump to a sheet with a simple click.
How it works
It's easy to get intimidated when looking at this macro. There is a lot going on here. However, if you step back and consider the few simple actions it does, it becomes a little less scary:
• It removes any previous Table of Contents sheet
• It creates a new Table of Contents sheet
• It grabs the name of each worksheet and pastes it on the Table of Contents
• It adds a hyperlink to each entry in the Table of Contents
That doesn't sound so bad. Now look at the code:
Sub Macro28()
‘Step 1: Declare Variables
Dim i As Long
‘Step 2: Delete Previous TOC if 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 increment i
Next i
End Sub
1. 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 Part did. In previous macros, we looped through the worksheets collection and selected each worksheet there. In this procedure, we are using a counter (our i variable). The main reason is because we not only have to keep track of the sheets, but we also have to manage to enter each sheet name
on a new row into 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.
2. 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, we have to start Step 2 with the On Error Resume Next error handler. This tells Excel to continue the macro if an error is
encountered here. We then delete the Table of Contents sheet using the DisplayAlerts method,
which effectively turns off Excel's warnings so we don't have to confirm the deletion. Finally, we reset the error handler to trap all errors again by entering On Error GoTo 0.
3. In Step 3, we add a new sheet to the workbook using the Before argument to position the new sheet as the first sheet. We then name the sheet Table of Contents. As we mentioned previously
in this Part, 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.
4. 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 we've done in previous macros, we are simply using the i counter as an index number that we can pass to the Sheets object. When the maximum number is reached, the macro ends.
5. 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.
We are able to do this using the Cells item. The Cells item provides an extremely 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 handy when you want to loop through a series of rows or columns using an incrementing index number.
6. 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.
7. 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.
Macro 29: Zooming In and Out of a Worksheet with Double-Click
Some spreadsheets are huge. Sometimes, we are forced to shrink the font size down so that we can see a decent portion of the spreadsheet on the screen. If you find that you are constantly zooming inand out of a spreadsheet, alternating between scanning large sections of data and reading specific cells, here is a handy macro that will auto-zoom on double-click.
How it works
With this macro in place, you can double-click on a cell in the spreadsheet to zoom in 200 percent. Double-click again and Excel zooms back to 100 percent. Obviously, 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 to at 100
‘Zoom 200% if currently at 100
If ActiveWindow.Zoom <> 100 Then
ActiveWindow.Zoom = 100
Else
ActiveWindow.Zoom = 200
End If
End Sub
Note that the side effect of double-clicking a cell is that it goes into edit mode. You can exit edit mode by pressing Esc on your keyboard. If you find it annoying to repeatedly press Esc when triggering this macro, you can add this statement to the end of the procedure:
Application.SendKeys (“{ESC}”)
This statement mimics you pressing Esc on your keyboard.
Figure 3-1: Type or paste your code into the Worksheet_BeforeDoubleClick event code window.
Macro 30: Highlight the Active Row and Column
When looking at a table of numbers, it would be nice if Excel automatically highlighted the row and column you're on (as demonstrated in Figure 3-2). This effect gives your eyes a lead line up and down the column as well as left and right across the row.
Figure 3-2: A highlighted row and column makes it easy to track data horizontally and vertically.
The following macro enables the effect you see in Figure 3-2 with just a simple double-click.
When the macro is in place, Excel highlights the row and column for the cell that is active, greatly improving your ability to view and edit a large grid.
How it works
Take a look at how this macro works:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
‘Step 1: Declare Variables
Dim strRange As String
‘Step2: 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
1. We first declare an object called strRange. This creates a memory container we can use to build a range string.
2. 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; this points to a range of cells encompassing cells A1 to G5. In Step 2, we are 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 properties for these three ranges are captured and pieced together into the strRange variable.
3. In Step 3, we feed the strRange variable as the address for a Range.Select statement. This is the line of the code that finally highlights the double-clicked selection.
Part IV: Selecting and Modifying Ranges
This Part is all about ranges: selecting them, modifying them, hiding them, and more.
In This Part
Macro 31 Selecting and Formatting a Range
Macro 32 Creating and Selecting Named Ranges
Macro 33 Enumerating Through a Range of Cells
Macro 34 Select and Format All Named Ranges
Macro 35 Inserting Blank Rows in a Range
Macro 36 Unhide All Rows and Columns
Macro 37 Deleting Blank Rows
Macro 38 Deleting Blank Columns
Macro 39 Select and Format All Formulas in a Workbook
Macro 40 Find and Select the First Blank Row or Column
Macro 41 Apply Alternate Color Banding
Macro 42 Sort a Range on Double-Click
Macro 43 Limit Range Movement to a Particular Area
Macro 44 Dynamically Set the Print Area of a Worksheet
One of the most important things you do in Excel is navigating the worksheet. When you work with
Excel manually, you are constantly navigating to appropriate ranges, finding the last row, moving to the last column, hiding and unhiding ranges, and so on. This all comes instinctively as part of doing work in Excel.
When you attempt to automate your work through VBA, you'll find that navigating your spreadsheet remains an important part of the automation process. In many cases, you need to dynamically navigate and manipulate Excel ranges, just as you would manually — only through VBA code.
This chapter provides some of the most commonly used macros in terms of navigating and working with ranges.
The code for this Part can be found on this book's companion website. See this book's Introduction for more on the companion website.
Macro 31: Selecting and Formatting a Range
One of the basic things you need to do in VBA is to select a specific range to do something with it. This simple macro selects the range D5:D16.
How it works
In this macro, you explicitly define the range to select by using the Range object.
Sub Macro31a() Range(“D5:D16”).Select End Sub
After the range of cells is selected, you can use any of the Range properties to manipulate the cells. We've altered this macro so that the range is colored yellow, converted to number formatting, and bold.
Sub Macro31a() Range(“D5:D16”).Select Selection.NumberFormat = “#,##0” Selection.Font.Bold = True Selection.Interior.ColorIndex = 36
End Sub
You notice that we refer to Selection many times in the previous sample code. To write more efficient code, you can simply refer to the range, using the With…End With statement. This statement tells Excel that any action you perform applies to the object to which you've pointed. Note that this macro doesn't actually select the range at all. This is a key point. In a macro, we can work with a range without selecting it first.
Sub Macro31a()
With Range(“D5:D16”)
.NumberFormat = “#,##0”
.Font.Bold = True
.Interior.ColorIndex = 36
End With
End Sub
Another way you can select a range is by using the Cells item of the Range object.
The Cells item gives us an extremely handy way of selecting ranges through code. It requires only relative row and column positions as parameters. Cells(5, 4) translates to row 5, column 4 (or Cell D5). Cells(16, 4) translates to row 16, column 4 (or cell D16).
If you want to select a range of cells, simply pass two items into the Range object. This macro performs the same selection of range D5:D16:
Sub Macro31a()
Range(Cells(5, 4), Cells(16, 4)).Select
End Sub
Here is the full formatting code using the Cells item. Again, note that this macro doesn't actually select the range we are altering. We can work with a range without selecting it first.
Sub Macro31a()
With Range(Cells(5, 4), Cells(16, 4))
.NumberFormat = “#,##0”
.Font.Bold = True
.Interior.ColorIndex = 36
End With
End Sub
Macro 32: Creating and Selecting Named Ranges
One of the more useful features in Excel is the ability to name your range (that is, to give your range a user-friendly name, so that you can more easily identify and refer to it via VBA).
Here are the steps you would perform to create a named range manually.
1. Select the range you wish to name.
2. Go to the Formulas tab in the Ribbon and choose the Define Name command (see Figure 4-
1).
3. Give the chosen range a user-friendly name in the New Name dialog box, as shown in Figure
4-2.
When you click OK, your range is named. To confirm this, you can go to the Formula tab and select the Name Manager command. This activates the Name Manager dialog box (see Figure 4-3),
where you can see all the applied named ranges.
Creating a named range via VBA is much less involved. You can directly define the Name property of the Range object:
Sub Macro32a() Range(“D6:D17”).Name = “MyData” End Sub
Admittedly, you'd be hard pressed to find a situation where you would need to automate the creation of named ranges. The real efficiency comes in manipulating them via VBA.
How it works
You simply pass the name of the range through the Range object. This allows you to select the range:
Sub Macro32b() Range(“MyData”).Select End Sub
As with normal ranges, you can refer to the range using the With…End With statement. This statement tells Excel that any action you perform applies to the object to which you've pointed. This not only prevents you from having to repeat syntax, but it also allows for the easy addition of actions by simply adding them between the With and End With statements.
Sub Macro32a()
With Range(“MyData”)
.NumberFormat = “#,##0”
.Font.Bold = True
.Interior.ColorIndex = 36
End With
End Sub
Macro 33: Enumerating Through a Range of Cells
One must-have VBA skill is the ability to enumerate (or loop) through a range. If you do any serious macro work in Excel, you will soon encounter the need to go through a range of cells one by one and perform some action.
This basic macro shows you a simple way to enumerate through a range.
How it works
In this macro, we are essentially using two Range object variables. One of the variables captures the scope of data we are working with, whereas the other is used to hold each individual cell as we go through the range. Then we use the For Each statement to activate or bring each cell in the target range into focus:
Sub Macro33()
‘Step1: Declare your variables. Dim MyRange As Range
Dim MyCell As Range
‘Step 2: Define the target Range. Set MyRange = Range(“D6:D17”)
‘Step 3: Start looping through the range. For Each MyCell In MyRange
‘Step 4: Do something with each cell. If MyCell.Value > 3000 Then MyCell.Font.Bold = True
End If
‘Step 5: Get the next cell in the range
Next MyCell
End Sub
1. The macro first declares two Range object variables. One, called MyRange, holds the entire targetrange. The other, called MyCell, holds each cell in the range as the macro enumerates
through them one by one.
2. In Step 2, we fill the MyRange variable with the target range. In this example, we are using Range(“D6:D17”). If your target range is a named range, you could simply enter its name — Range(“MyNamedRange”).
3. In this step, the macro starts looping through each cell in the target range, activating each cell as it goes through.
4. After a cell is activated, you would do something with it. That “something” really depends on the task at hand. You may want to delete rows when the active cell has a certain value, or you may want to insert a row between each active cell. In this example, the macro is changing the font to Bold for any cell that has a value greater than 3,000.
5. In Step 5, the macro loops back to get the next cell. After all cells in the target range are activated, the macro ends.
Macro 34: Select and Format All Named Ranges
If you spend your time auditing other people's worksheets, you'll know that Excel users love their named ranges. It's not uncommon to encounter spreadsheets where dozens of cells and ranges are given individual names. This makes auditing a spreadsheet an extremely muddy experience. It sometimes helps to know where the named ranges are. Here is a macro you can use to color all of the named ranges in a workbook yellow.
How it works
In this macro, we are looping through the Names collection of the active workbook to capture each named range. When a named range is captured, we color the range.
Sub Macro34()
‘Step 1: Declare your variables. Dim RangeName As Name
Dim HighlightRange As Range
‘Step 2: Tell Excel to Continue if Error. On Error Resume Next
‘Step 3: Loop through each Named Range.
For Each RangeName In ActiveWorkbook.Names
‘Step 4: Capture the RefersToRange
Set HighlightRange = RangeName.RefersToRange
‘Step 5: Color the Range
HighlightRange.Interior.ColorIndex = 36
‘Step 6: Loop back around to get the next range
Next RangeName
End Sub
1. We first declare two object variables. The first variable called RangeName holds each named range as the macro enumerates through the Names collection. The second variable called HighlightRange captures the range to which RangeName is referring.
2. Technically, an Excel user can assign a “name” to things that are not actually a range (such as constants or formulas). So with that in mind, we have to anticipate that Excel will throw an error if the RefersToRange property of the named range does not represent a range address. In this step, we tell Excel to ignore any error that is thrown and continue to the next line of code. This ensures that the code doesn't abruptly stop due to a bad range address.
3. In this step, the macro starts looping through each name in the active workbooks Names
collection.
4. After a named range is activated, the macro captures the address in our HighlightRange
object variable. This exposes all the properties we can use to format the range.
5. In Step 5, we assign a color to the cells in the captured range.
6. Finally, we loop back to get the next named range. The macro ends after we have enumerated through all of the names.
If you don't see personal.xlb in your project window, it 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, select the Personal Macro Workbook option in the Record Macro dialog box before you start recording. This option is in the Store Macro In drop-down list. Simply record a couple of cell clicks and then stop recording. You can discard the recorded macro and replace it with this one.
Macro 35: Inserting Blank Rows in a Range
Occasionally, you may need to dynamically insert rows into your dataset. Although blank rows are generally bothersome, in some situations, the final formatted version of your report requires them to separate data. The macro in this section adds blank rows into a range.
How it works
This macro performs a reverse loop through the chosen range using a counter. It starts at the last row of the range inserting two blank rows, and then moves to the previous row in the range. It keeps doing that same insert for every loop, each time incrementing the counter to the previous row.
Sub Macro35()
‘Step1: Declare your variables. Dim MyRange As Range
Dim iCounter As Long
‘Step 2: Define the target Range. Set MyRange = Range(“C6:D17”)
‘Step 3: Start reverse looping through the range. For iCounter = MyRange.Rows.Count To 2 Step -1
‘Step 4: Insert two blank rows. MyRange.Rows(iCounter).EntireRow.Insert MyRange.Rows(iCounter).EntireRow.Insert
‘Step 5: Increment the counter down
Next iCounter
End Sub
1. We first declare two variables. The first variable is an object variable called MyRange. This is an object variable that defines the target range. The other variable is a Long Integer variable called iCounter. This variable serves as an incremental counter.
2. In Step 2, the macro fills the MyRange variable with the target range. In this example, we are using Range(“C6:D17”). If your target range is a named range, you could simply enter its name
— Range(“MyNamedRange”).
3. In this step, the macro sets the parameters for the incremental counter to start at the max count for the range (MyRange.Rows.Count) and end at 2 (the second row of the chosen range). Note that we are using the Step-1 qualifier. Because we specify Step -1, Excel knows we are going to increment the counter backwards, moving back one increment on each iteration. In all, Step 3 tells Excel to start at the last row of the chosen range, moving backward until it gets to the
second row of the range.
4. When working with a range, you can explicitly call out a specific row in the range by passing a row index number to the Rows collection of the range. For instance, Range(“D6:D17”).Rows(5) points to the fifth row in the range D6:D17.
In Step 4, the macro uses the iCounter variable as an index number for the Rows collection of MyRange. This helps pinpoint which exact row the macro is working with in the current loop. The macro then uses the EntireRow.Insert method to insert a new blank row. Because we want two blank rows, we do this twice.
5. In Step 5, the macro loops back to increment the counter down.
Macro 36: Unhide All Rows and Columns
When you are auditing a spreadsheet that you did not create, you often want to ensure you're getting a full view of what is exactly in the spreadsheet. To do so, you need to ensure that no columns and rows are hidden. This simple macro automatically unhides all rows and columns for you.
How it works
In this macro, we call on the Columns collection and the Rows collection of the worksheet. Each collection has properties that dictate where their objects are hidden or visible. Running this macro unhides every column in the Columns collection and every row in the Rows collection.
Sub Macro36() Columns.EntireColumn.Hidden = False Rows.EntireRow.Hidden = False
End Sub
If you don't see personal.xlb in your project window, it means it 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, select the Personal Macro Workbook option in the Record Macro dialog box before you start recording. This option is in the Store
Macro In drop-down list. Simply record a couple of cell clicks and then stop recording. You can discard the recorded macro and replace it with this one.
Macro 37: Deleting Blank Rows
Work with Excel long enough, and you'll find out that blank rows can often cause havoc on many levels. They can cause problems with formulas, introduce risk when copying and pasting, and sometimes cause strange behaviors in PivotTables. If you find that you are manually searching out and deleting blank rows in your data sets, this macro can help automate that task.
How it works
In this macro, we are using the UsedRange property of the Activesheet object to define the range we are working with. The UsedRange property gives us a range that encompasses the cells that have been used to enter data. We then establish a counter that starts at the last row of the used
range to check if the entire row is empty. If the entire row is indeed empty, we remove the row. We keep doing that same delete for every loop, each time incrementing the counter to the previous
row.
Sub Macro37()
‘Step1: Declare your variables. Dim MyRange As Range
Dim iCounter As Long
‘Step 2: Define the target Range. Set MyRange = ActiveSheet.UsedRange
‘Step 3: Start reverse looping through the range. For iCounter = MyRange.Rows.Count To 1 Step -1
‘Step 4: If entire row is empty then delete it.
If Application.CountA(Rows(iCounter).EntireRow) = 0 Then
Rows(iCounter).Delete
End If
‘Step 5: Increment the counter down
Next iCounter
End Sub
1. The macro first declares two variables. The first variable is an Object variable called MyRange. This is an object variable that defines our target range. The other variable is a Long Integer variable called iCounter. This variable serves as an incremental counter.
2. In Step 2, the macro fills the MyRange variable with the UsedRange property of the ActiveSheet object. The UsedRange property gives us a range that encompasses the cells that have been used to enter data. Note that if we wanted to specify an actual range or a named range, we could simply enter its name — Range(“MyNamedRange”).
3. In this step, the macro sets the parameters for the incremental counter to start at the max count for the range (MyRange.Rows.Count) and end at 1 (the first row of the chosen range). Note that we are using the Step-1 qualifier. Because we specify Step -1, Excel knows we are going to increment the counter backwards, moving back one increment on each iteration. In all, Step 3
tells Excel to start at the last row of the chosen range, moving backward until it gets to the first row of the range.
4. When working with a range, you can explicitly call out a specific row in the range by passing a row index number to the Rows collection of the range. For instance, Range(“D6:D17”).Rows(5) points to the fifth row in the range D6:D17.
In Step 4, the macro uses the iCounter variable as an index number for the Rows collection of MyRange. This helps pinpoint which exact row we are working with in the current loop. The macro checks to see whether the cells in that row are empty. If they are, the macro deletes the entire row.
5. In Step 5, the macro loops back to increment the counter down.
If you don't see personal.xlb in your project window, it means it 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, select the Personal Macro Workbook option in the Record Macro dialog box before you start recording. This option is in the Store Macro In drop-down list. Simply record a couple of cell clicks and then stop recording. You can discard the recorded macro and replace it with this one.
Macro 38: Deleting Blank Columns
Just as with blank rows, blank columns also have the potential of causing unforeseen errors. If you find that you are manually searching out and deleting blank columns in your data sets, this macro can automate that task.
How it works
In this macro, we are using the UsedRange property of the ActiveSheet object to define the range we are working with. The UsedRange property gives us a range that encompasses the cells that have been used to enter data. We then establish a counter that starts at the last column of the used range, checking if the entire column is empty. If the entire column is indeed empty, we remove the
column. We keep doing that same delete for every loop, each time incrementing the counter to the previous column.
Sub Macro38()
‘Step1: Declare your variables. Dim MyRange As Range
Dim iCounter As Long
‘Step 2: Define the target Range. Set MyRange = ActiveSheet.UsedRange
‘Step 3: Start reverse looping through the range. For iCounter = MyRange.Columns.Count To 1 Step -1
‘Step 4: If entire column is empty then delete it.
If Application.CountA(Columns(iCounter).EntireColumn) = 0 Then
Columns(iCounter).Delete
End If
‘Step 5: Increment the counter down
Next iCounter
End Sub
1. Step 1 first declares two variables. The first variable is an object variable called MyRange. This is an Object variable that defines the target range. The other variable is a Long Integer variable called iCounter. This variable serves as an incremental counter.
2. Step 2 fills the MyRange variable with the UsedRange property of the ActiveSheet object. The UsedRange property gives us a range that encompasses the cells that have been used to enter data. Note that if we wanted to specify an actual range or a named range, we could simply enter its name — Range(“MyNamedRange”).
3. In this step, the macro sets the parameters for our incremental counter to start at the max count for the range (MyRange.Columns.Count) and end at 1 (the first row of the chosen range). Note that we are using the Step-1 qualifier. Because we specify Step -1, Excel knows we are going to increment the counter backwards; moving back one increment on each iteration. In all, Step 3 tells Excel that we want to start at the last column of the chosen range, moving backward until
we get to the first column of the range.
4. When working with a range, you can explicitly call out a specific column in the range by passing a column index number to the Columns collection of the range. For instance, Range(“A1:D17”).Columns(2) points to the second column in the range (column B).
In Step 4, the macro uses the iCounter variable as an index number for the Columns collection of MyRange. This helps pinpoint exactly which column we are working with in the current loop. The macro checks to see whether all the cells in that column are empty. If they are, the macro deletes the entire column.
5. In Step 5, the macro loops back to increment the counter down.
If you don't see personal.xlb in your project window, it 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, select the Personal Macro Workbook option in the Record Macro dialog box before you start recording. This option is in the Store Macro In drop-down box. Simply record a couple of cell clicks and then stop recording. You can discard the recorded macro and replace it with this one.
Macro 39: Select and Format All Formulas in a
Workbook
When auditing an Excel workbook, it's paramount to have a firm grasp of all the formulas in each sheet. This means finding all the formulas, which can be an arduous task if done manually. However, Excel provides us with a slick way of finding and tagging all the formulas on a worksheet. The macro in this section exploits this functionality to dynamically find all cells that contain formulas.
How it works
Excel has a set of predefined “special cells” that you can select by using the Go To Special dialog box. To select special cells manually, go to the Home tab on the Ribbon and select Go To Special. This brings up the Go To Special dialog box shown in Figure 4-4. Here, you can select a set of cells based on a few defining attributes. One of those defining attributes is formulas. Selecting the Formulas option effectively selects all cells that contain formulas.
Figure 4-4: The Go To Special dialog box.
This macro programmatically does the same thing for the entire workbook at the same time. Here, we are using the SpecialCells method of the Cells collection. The SpecialCells method requires type parameter that represents the type of special cell. In this case, we are using xlCellTypeFormulas.
In short, we are referring to a special range that consists only of cells that contain formulas. We refer to this special range using the With…End With statement. This statement tells Excel that any action you perform applies only to the range to which you've pointed. Here, we are coloring the interior of the cells in the chosen range.
Sub Macro39()
‘Step 1: Declare your Variables
Dim ws As Worksheet
‘Step 2: Avoid Error if no formulas are found
On Error Resume Next
‘Step 3: Start looping through worksheets
For Each ws In ActiveWorkbook.Worksheets
‘Step 4: Select cells and highlight them
With ws.Cells.SpecialCells(xlCellTypeFormulas)
.Interior.ColorIndex = 36
End With
‘Step 5: Get next worksheet
Next ws
End Sub
1. Step 1 declares an object called ws. This creates a memory container for each worksheet the macro loops through.
2. If the spreadsheet contains no formulas, Excel throws an error. To avoid the error, we tell
Excel to continue with the macro if an error is triggered.
3. Step 3 begins the looping, telling Excel to evaluate all worksheets in the active workbook.
4. In this Step, the macro selects all cells containing formulas, and then formats them.
5. In Step 5, we loop back to get the next sheet. After all of the sheets are evaluated, the macro ends.
If you don't see personal.xlb in your project window, it 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, select the Personal Macro Workbook option in the Record Macro dialog box before you start recording. This option is in the Store Macro In drop-down list. Simply record a couple of cell clicks and then stop recording. You can discard the recorded macro and replace it with this one.
Macro 40: Find and Select the First Blank Row or
Column
You may often run across scenarios where you have to append rows or columns to an existing data set. When you need to append rows, you will need to be able to find the last used row and then move down to the next empty cell. Likewise, in situations where you need to append columns, you need to be able to find the last used column and then move over the next empty cell. The macros in this section allow you to dynamically find and select the first blank row or column. They are meant to be used in conjunction with other macros. After all, these macros simply find and select the first blank row or column.
How it works
These macros both use the Cells item and the Offset property as key navigation tools.
The Cells item belongs to the Range object. It gives us an extremely handy way of selecting ranges through code. It requires only relative row and column positions as parameters. Cells(5,4) translates to row 5, column 4 (or Cell D5). Cells(16, 4) translates to row 16, column 4 (or cell D16).
In addition to passing hard numbers to the Cells item, you can also pass expressions.
Cells(Rows.Count, 1) is the same as selecting the last row in the spreadsheet and the first column in the spreadsheet. In Excel 2010, that essentially translates to cell A1048576.
Cells(1, Columns.Count) is the same as selecting the first row in the spreadsheet and the last column in the spreadsheet. In Excel 2010, that translates to cell XFD1.
Combining the Cells statement with the End property allows you to jump to the last used row or column. This statement is equivalent to going to cell A1048576 and pressing Ctrl+Shift+Up Arrow on the keyboard. When you run this, Excel automatically jumps to the last used row in column A.
Cells(Rows.Count, 1).End(xlUp).Select
Running this statement is equivalent to going to cell XFD1 and pressing Ctrl+Shift+Left Arrow on the keyboard. This gets you to the last used column in row 1.
Cells(1, Columns.Count).End(xlToLeft).Select
When you get to the last used row or column, you can use the Offset property to move down or over to the next blank row or column.
The Offset property uses a row and column index to specify a changing base point.
For example, this statement selects cell A2 because the row index in the offset is moving the row base point by one:
Range(“A1”).Offset(1, 0).Select
This statement selects cell C4 because the row and column indexes move the base point by three rows and two columns:
Range(“A1”).Offset(3, 2).Select
Pulling all these concepts together, we can create a macro that selects the first blank row or column.
This macro selects the first blank row.
Sub Macro40a()
‘Step 1: Declare Your Variables. Dim LastRow As Long
‘Step 2: Capture the last used row number. LastRow = Cells(Rows.Count, 1).End(xlUp).Row
‘Step 3: Select the next row down Cells(LastRow, 1).Offset(1, 0).Select End Sub
1. Step 1 first declares a Long Integer variable called LastRow to hold the row number of the last used row.
2. In Step 2, we capture the last used row by starting at the very last row in the worksheet and using the End property to jump up to the first non-empty cell (the equivalent of going to cell A1048576 and pressing Ctrl+Shift+Up Arrow on the keyboard).
3. In this step, we use the Offset property to move down one row and select the first blank cell in column A.
This macro selects the first blank column:
Sub Macro40b()
‘Step 1: Declare Your Variables. Dim LastColumn As Long
‘Step 2: Capture the last used column number.
LastColumn = Cells(5, Columns.Count).End(xlToLeft).Column
‘Step 3: Select the next column over
Cells(5, LastColumn).Offset(0, 1).Select
End Sub
1. We first declare a Long Integer variable called LastColumn to hold the column number of the last used column.
2. In Step 2, we capture the last used column by starting at the very last column in the worksheet and using the End property to jump up to the first non-empty column (the equivalent of going to cell XFD5 and pressing Ctrl+Shift+Left Arrow on the keyboard).
3. In this step, we use the Offset property to move over one column and select the first blank column in row 5.
Macro 41: Apply Alternate Color Banding
Color banding is an effect where each row of a data set is colored in alternating shades (see Figure 4-5). You would typically apply alternating row colors to reports you distribute to people who need to review each row of data. Color banding makes the data a little easier to read. This macro allows you to automatically apply alternating colors to each row in the selected range.
Figure 4-5: Color banding helps make your data sets easier to read.
How it works
In this macro, we are essentially using two Range object variables. One of the variables captures
the scope of data we are working with, whereas the other is used to hold each individual cell as we go through the range. Then we use the For Each statement to activate or bring each cell in the target range into focus. When each row is in focus, we use the Offset property to evaluate the color index of the previous row. If the color index is white, we apply the alternate green color index.
Sub Macro41()
‘Step1: Declare your variables. Dim MyRange As Range
Dim MyRow As Range
‘Step 2: Define the target Range. Set MyRange = Selection
‘Step 3: Start looping through the range. For Each MyRow In MyRange.Rows
‘Step 4: Check if the row is an even number. If MyRow.Row Mod 2 = 0 Then
‘Step 5: Apply appropriate alternate color. MyRow.Interior.ColorIndex = 35
Else
MyRow.Interior.ColorIndex = 2
End If
‘Step 6: Loop back to get next row. Next MyRow
End Sub
1. We first declare two Range object variables. One, called MyRange, holds the entire target range. The other, called MyCell, holds each cell in the range as the macro enumerates through them one by one.
2. Step 2 fills the MyRange variable with the target range. In this example, we are using the selected range — the range that was selected on the spreadsheet. You can easily set the
MyRange variable to a specific range such as Range(“A1:Z100”). Also, if your target range is a named range, you could simply enter its name: Range(“MyNamedRange”).
3. In this step, the macro starts through each cell in the target range, activating each cell as it goes through.
4. When a cell is activated, we determine if the current row is an even row number.
5. If the row number is indeed even, the macro uses the alternate green color index 35. If not, it uses the color index 2.
6. In Step 6, the macro loops back to get the next cell. After all of the cells in the target range are activated, the macro ends.
If you don't see personal.xlb in your project window, it 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, select the Personal Macro Workbook option in the Record Macro dialog box before you start recording. This option is in the Store Macro In drop-down list. Simply record a couple of cell clicks and then stop recording. You can discard the recorded macro and replace it with this one.
Macro 42: Sort a Range on Double-Click
When you distribute your Excel reports to your customers, it's often nice to add a few bells and whistles. One of the easier enhancements to apply is the ability to sort when a column header is double-clicked. Although this may sound complicated, it's relatively easy with this macro.
How it works
In this macro, we first find the last non-empty row (using the concepts outlined in this chapter under “Macro 40: Find and Select the First Blank Row or Column”). We then use that row number to define the target range of rows we need to sort. Using the Sort method, we sort the target rows by the column we doubled-clicked.
Double-clicking will put Excel in Edit mode, which you can cancel by pressing Esc.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
‘Step 1: Declare your Variables
Dim LastRow As Long
‘Step 2: Find last non-empty row
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
‘Step 3: Sort ascending on double-clicked column
Rows(“6:” & LastRow).Sort _ Key1:=Cells(6, ActiveCell.Column), _ Order1:=xlAscending
End Sub
1. We first declare a Long Integer variable called LastRow to hold the row number of the last non-empty row.
2. In Step 2, we capture the last non-empty row by starting at the very last row in the worksheet
and using the End property to jump up to the first non-empty cell (equivalent of going to cell
A1048576 and pressing Ctrl+Shift+Up Arrow on the keyboard).
Note that you need to change the column number in this cell to one that is appropriate for your data set. That is to say, if your table starts on Column J, you would need to change the statement in Step 2 to Cells(Rows.Count, 10).End(xlUp).Row because column J is the tenth column in the worksheet.
3. In this step, we define the total row range for our data. Keep in mind that the range of rows has to start with the first row of data (excluding headers) and end with the last non-empty row. In this case, our data set starts on row 6. So we use the Sort method on Rows(“6:” & LastRow). The Key argument here tells Excel which range to sort on.
Again, you will want to ensure the range you use here starts with the first row of data (excluding the headers).
Macro 43: Limit Range Movement to a Particular
Area
Excel gives you the ability to limit the range of cells that a user can scroll through. The macro we demonstrate here is something you can easily implement today.
How it works
Excel's ScrollArea property allows you to set the scroll area for a particular worksheet. For instance, this statement sets the scroll area on Sheet1 so the user cannot activate any cells outside of A1:M17.
Sheets(“Sheet1”).ScrollArea = “A1:M17”
Because this setting is not saved with a workbook, you'll have to reset it each time the workbook is opened. You can accomplish this by implementing this statement in the Workbook_Open event:
Private Sub Worksheet_Open() Sheets(“Sheet1”).ScrollArea = “A1:M17”End Sub
If for some reason you need to clear the scroll area limits, you can remove the restriction with this statement:
ActiveSheet.ScrollArea = “”
Macro 44: Dynamically Set the Print Area of a
Worksheet
In certain situations, you may find yourself constantly adding data to your spreadsheets. When you do, you may have to constantly resize the print area of the worksheet to encapsulate any new data that you've added. Why keep doing this manually when you can implement a macro to dynamically adjust the print area to capture any new data you've added?
How it works
In this simple macro, we use the PrintArea property to define the range of cells that will be included when printing. As you can see, we are simply feeding the PrintArea property with the address of the UsedRange property. The UsedRange property gives us a range that encompasses the cells that have been used to enter data.
To keep this dynamic, we implement the code in the worksheet's Change event:
Private Sub Worksheet_Change(ByVal Target As Range) ActiveSheet.PageSetup.PrintArea = ActiveSheet.UsedRange.Address End Sub
Figure 4-8: Type or paste your code in the Worksheet_Change event code window.
Part V: Working with Data
This Part is all about managing data: cleaning it up, formatting it, and more.
In This Part
Macro 45 Copy and Paste a Range
Macro 46 Convert All Formulas in a Range to Values
Macro 47 Perform the Text to Columns Command on All Columns
Macro 48 Convert Trailing Minus Signs
Macro 49 Trim Spaces from All Cells in a Range
Macro 50 Truncate ZIP Codes to the Left Five
Macro 51 Padding Cells with Zeros
Macro 52 Replace Blanks Cells with a Value
Macro 53 Append Text to the Left or Right of Your Cells
Macro 54 Create a Super Data Cleanup Macro
Macro 55 Clean Up Non-Printing Characters
Macro 56 Highlight Duplicates in a Range of Data
Macro 57 Hide All Rows but Those Containing Duplicate Data
Macro 58 Selectively Hide AutoFilter Drop-down Arrows
Macro 59 Copy Filtered Rows to a New Workbook
Macro 60 Create a New Sheet for Each Item in an AutoFilter
Macro 61 Show Filtered Columns in the Status Bar
When working with information in Excel, you often have to transform the data in some way. Transforming it generally means cleaning, standardizing, or shaping data in ways that are appropriate for your work. This can mean anything from cleaning out extra spaces, to padding numbers with zeros, to filtering data for certain criteria.
The macros in this Part shows you some of the more useful macros you can use to dynamically transform the data in your workbooks. If you like, you can combine these macros into one, running each piece of code in a sequence that essentially automates the scrubbing and shaping of your data.
The code for this Part can be found on this book's companion website. See this book's Introduction for more on the companion website.
Macro 45: Copy and Paste a Range
One of the basic data manipulation skills you'll need to learn is copying and pasting a range of data. It's fairly easy to do this manually. Luckily, it's just as easy to copy and paste via VBA.
How it works
In this macro, we use the Copy method of the Range object to copy data from D6:D17 and paste to L6:L17. Note the use of the Destination argument. This argument tells Excel where to paste the data.
Sub Macro45a() Sheets(“Sheet1”).Range(“D6:D17”).Copy _ Destination:=Sheets(“Sheet1”).Range(“L6:L17”) End Sub
When working with your spreadsheet, you likely often have to copy formulas and paste them as values. To do this in a macro, you can use the PasteSpecial method. In this example, we copy the formulas F6:F17 to M6:M17. Notice that we are not only pasting as values using xlPasteValues, but we are also using xlPasteFormats to apply the formatting from the copied range.
Sub Macro45b() Sheets(“Sheet1”).Range(“F6:F17”).Copy Sheets(“Sheet1”).Range(“M6:M17”).PasteSpecial xlPasteValues Sheets(“Sheet1”).Range(“M6:M17”).PasteSpecial xlPasteFormats End Sub
Keep in mind that the ranges specified here are for demonstration purposes. Alter the ranges to suit the data in your worksheet.
Macro 46: Convert All Formulas in a Range to
Values
In some situations, you may want to apply formulas in a certain workbook, but you don't necessarily want to keep or distribute the formulas with your workbook. In these situations, you may want to convert all the formulas in a given range to values.
How it works
In this macro, we essentially use two Range object variables. One of the variables captures the scope of data we are working with, whereas the other is used to hold each individual cell as we go through the range. Then we use the For Each statement to activate or bring each cell in the target range into focus. Every time a cell is activated, we check to see whether the cell contains a formula. If it does, we replace the formula with the value that is shown in the cell.
Sub Macro46()
‘Step 1: Declare your variables
Dim MyRange As Range
Dim MyCell As Range
‘Step 2: Save the Workbook before changing cells? Select Case MsgBox(“Can't Undo this action. “ & _ “Save Workbook First?”, vbYesNoCancel)
Case Is = vbYes ThisWorkbook.Save Case Is = vbCancel Exit Sub
End Select
‘Step 3: Define the target Range. Set MyRange = Selection
‘Step 4: Start looping through the range. For Each MyCell In MyRange
‘Step 5: If cell has formula, set to the value shown. If MyCell.HasFormula Then
MyCell.Formula = MyCell.Value
End If
‘Step 6: Get the next cell in the range
Next MyCell
End Sub
1. Step 1 declares two Range object variables, one called MyRange to hold the entire target range, and the other called MyCell to hold each cell in the range as we enumerate through them one by one.
2. When you run a macro, it destroys the undo stack. This means you can't undo the changes a macro makes. Because we are actually changing data, we need to give ourselves the option of saving the workbook before running the macro. This is what Step 2 does.
Here, we call up a message box that asks if we want to save the workbook first. It then gives us three choices: Yes, No, and Cancel. Clicking Yes saves the workbook and continues with the macro. Clicking Cancel exits the procedure without running the macro. Clicking No runs the macro without saving the workbook.
3. Step 3 fills the MyRange variable with the target range. In this example, we use the selected range — the range that was selected on the spreadsheet. You can easily set the MyRange variable to a specific range such as Range(“A1:Z100”). Also, if your target range is a named range, you can simply enter its name: Range(“MyNamedRange”).
4. This step starts looping through each cell in the target range, activating each cell as it goes through.
5. After a cell is activated, the macro uses the HasFormula property to check whether the cell contains a formula. If it does, we set the cell to equal the value that is shown in the cell. This effectively replaces the formula with a hard-coded value.
6. Step 6 loops back to get the next cell. After all cells in the target range are activated, the macro ends.
Macro 47: Perform the Text to Columns Command on All Columns
When you import data from other sources, you may sometimes wind up with cells where the number values are formatted as text. You typically recognize this problem because no matter what you do, you can't format the numbers in these cells to numeric, currency, or percentage formats. You may also see a smart tag on the cells (see Figure 5-1) that tells you the cell is formatted as text.
Again, this is a fairly simple action. The problem, however, is that Excel doesn't let you perform the Text to Columns fix on multiple columns. You have to apply this fix one column at a time. This can be a real nuisance if you've got this issue in many columns.
Here is where a simple macro can help you save your sanity.
How it works
In this macro, we use two Range object variables to go through our target range, leveraging the For Each statement to activate each cell in the target range. Every time a cell is activated, we simply reset the value of the cell. This in effect does the same thing as the Text to Columns command.
Sub Macro47()
‘Step 1: Declare your variables
Dim MyRange As Range
Dim MyCell As Range
‘Step 2: Save the Workbook before changing cells? Select Case MsgBox(“Can't Undo this action. “ & _ “Save Workbook First?”, vbYesNoCancel)
Case Is = vbYes ThisWorkbook.Save Case Is = vbCancel Exit Sub
End Select
‘Step 3: Define the target Range. Set MyRange = Selection
‘Step 4: Start looping through the range. For Each MyCell In MyRange
‘Step 5: Reset the cell value. If Not IsEmpty(MyCell) Then MyCell.Value = MyCell.Value
End If
‘Step 6: Get the next cell in the range
Next MyCell
End Sub
1. Step 1 declares two Range object variables, one called MyRange to hold the entire target range, and the other called MyCell to hold each cell in the range as the macro enumerates through them one by one.
2. When you run a macro, it destroys the undo stack. This means you can't undo the changes a macro makes. Because the macro is actually changing data, we need to give ourselves the option of saving the workbook before running the macro. This is what Step 2 does. Here, we call up a message box that asks if we want to save the workbook first. It gives us three choices: Yes, No, and Cancel. Clicking Yes saves the workbook and continues with the macro. Clicking Cancel exits the procedure without running the macro. Clicking No runs the macro without saving the workbook.
3. Step 3 fills the MyRange variable with the target range. In this example, we are using the
selected range — the range that was selected on the spreadsheet. You can easily set the
MyRange variable to a specific range such as Range(“A1:Z100”). Also, if your target range is a named range, you can simply enter its name: Range(“MyNamedRange”).
4. Step 4 starts looping through each cell in the target range, activating each cell as we go through.
5. After a cell is activated, the macro uses the IsEmpty function to make sure the cell is not empty. We do this to save a little on performance by skipping the cell if there is nothing in it. We then simply reset the cell to its own value. This removes any formatting mismatch.
6. Step 6 loops back to get the next cell. After all cells in the target range are activated, the macro ends.
Macro 48: Convert Trailing Minus Signs
Legacy and mainframe systems are notorious for outputting trailing minus signs. In other words, instead of a number like -142, some systems output 142-. This obviously wreaks havoc on your spreadsheet — especially if you need to perform mathematic operations on the data. This nifty macro goes through a target range and fixes all the negative minus signs so that they show up in front of the number instead of at the end.
How it works
In this macro, we use two Range object variables to go through our target range, leveraging the For Each statement to activate each cell in the target range. Every time a cell is activated, we convert the value of the cell into a Double numeric data type by using the Cdbl function. The Double data type forces any negative signs to show at the front of the number.
Sub Macro48()
‘Step 1: Declare your variables
Dim MyRange As Range
Dim MyCell As Range
‘Step 2: Save the Workbook before changing cells? Select Case MsgBox(“Can't Undo this action. “ & _ “Save Workbook First?”, vbYesNoCancel)
Case Is = vbYes
ThisWorkbook.Save
Case Is = vbCancel
Exit Sub
End Select
‘Step 3: Define the target Range. Set MyRange = Selection
‘Step 4: Start looping through the range. For Each MyCell In MyRange
‘Step 5: Convert the value to a Double. If IsNumeric(MyCell) Then
MyCell = CDbl(MyCell) End If
‘Step 6: Get the next cell in the range
Next MyCell
End Sub
1. Step 1 declares two Range object variables, one called MyRange to hold the entire target range, and the other called MyCell to hold each cell in the range as we enumerate through them one by one.
2. When you run a macro, it destroys the undo stack. This means you can't undo the changes a macro makes. Because we are actually changing data, we need to give ourselves the option of saving the workbook before running the macro. This is what Step 2 does. Here, we call up a message box that asks if we want to save the workbook first. It then gives us three choices: Yes, No, and Cancel. Clicking Yes saves the workbook and continues with the macro. Clicking Cancel exits the procedure without running the macro. Clicking No runs the macro without saving the workbook.
3. Step 3 fills the MyRange variable with the target range. In this example, we are using the selected range — the range that was selected on the spreadsheet. You can easily set the
MyRange variable to a specific range such as Range(“A1:Z100”). Also, if your target range is a named range, you can simply enter its name — Range(“MyNamedRange”).
4. Step 4 starts looping through each cell in the target range, activating each cell as it goes through.
5. After a cell is activated, Step 5 uses the IsNumeric function to check to see if the value can be evaluated as a number. This is to ensure we don't affect textual fields. We then pass the cell's value through the Cdbl function. This effectively converts the value to the Double numeric data type, forcing the minus sign to the front.
6. Step 6 loops back to get the next cell. After all cells in the target range are activated, the macro ends.
Because we define the target range as the current selection, you want to be sure to select the area where your data exists before running this code. In other words, you wouldn't want to select the entire worksheet. Otherwise, every empty cell in the spreadsheet would be filled with a zero. Of course, you can ensure this is never a problem by explicitly defining the target range, such as Set MyRange = Range(“A1:Z100”).
Macro 49: Trim Spaces from All Cells in a Range
A frequent problem when you import dates from other sources is leading or trailing spaces. That is, the values that are imported have spaces at the beginning or end of the cell. This obviously makes it difficult to do things like VLOOKUP or sorting. Here is a macro that makes it easy to search for and remove extra spaces in your cells.
How it works
In this macro, we enumerate through a target range, passing each cell in that range through the Trim
function.
Sub Macro49()
‘Step 1:Declare your variables
Dim MyRange As Range
Dim MyCell As Range
‘Step 2: Save the Workbook before changing cells? Select Case MsgBox(“Can't Undo this action. “ & _ “Save Workbook First?”, vbYesNoCancel)
Case Is = vbYes ThisWorkbook.Save Case Is = vbCancel Exit Sub
End Select
‘Step 3: Define the target Range. Set MyRange = Selection
‘Step 4: Start looping through the range. For Each MyCell In MyRange
‘Step 5: Trim the Spaces.
If Not IsEmpty(MyCell) Then
MyCell = Trim(MyCell) End If
‘Step 6: Get the next cell in the range
Next MyCell
End Sub
1. Step 1 declares two Range object variables, one called MyRange to hold the entire target range, and the other called MyCell to hold each cell in the range as the macro enumerates through them one by one.
2. When you run a macro, it destroys the undo stack. You can't undo the changes a macro makes. Because we are actually changing data, we need to give ourselves the option of saving the workbook before running the macro. Step 2 does this. Here, we call up a message box that asks if we want to save the workbook first. It then gives us three choices: Yes, No, and Cancel. Clicking Yes saves the workbook and continues with the macro. Clicking Cancel exits the procedure without running the macro. Clicking No runs the macro without saving the workbook.
3. Step 3 fills the MyRange variable with the target range. In this example, we are using the selected range — the range that was selected on the spreadsheet. You can easily set the
MyRange variable to a specific range such as Range(“A1:Z100”). Also, if your target range is a named range, you can simply enter its name — Range(“MyNamedRange”).
4. Step 4 starts looping through each cell in the target range, activating each cell as we go through.
5. After a cell is activated, the macro uses the IsEmpty function to make sure the cell is not empty. We do this to save a little on performance by skipping the cell if there is nothing in it. We then pass the value of that cell to the Trim function. The Trim function is a native Excel function that removes leading and trailing spaces.
6. Step 6 loops back to get the next cell. After all cells in the target range are activated, the macro ends.
Macro 50: Truncate ZIP Codes to the Left Five
U.S. ZIP codes come in either 5 or 10 digits. Some systems output a 10-digit ZIP code, which, for the purposes of a lot of Excel analysis, is too many. A common data standardization task is to truncate ZIP codes to the left five digits. Many of us use formulas to do this, but if you are constantly cleaning up your ZIP codes, the macro outlined in this section can help automate that task.
It's important to note that although this macro solves a specific problem, the concept of truncating data remains useful for many other types of data cleanup activities.
How it works
This macro uses the Left function to extract the left five characters of each ZIP code in the given
range.
Sub Macro50()
‘Step 1: Declare your variables
Dim MyRange As Range
Dim MyCell As Range
‘Step 2: Save the Workbook before changing cells? Select Case MsgBox(“Can't Undo this action. “ & _ “Save Workbook First?”, vbYesNoCancel)
Case Is = vbYes ThisWorkbook.Save Case Is = vbCancel Exit Sub
End Select
‘Step 3: Define the target Range. Set MyRange = Selection
‘Step 4: Start looping through the range. For Each MyCell In MyRange
‘Step 5: Extract out the left 5 characters. If Not IsEmpty(MyCell) Then
MyCell = Left(MyCell, 5) End If
‘Step 6: Get the next cell in the range
Next MyCell
End Sub
1. Step 1 declares two Range object variables, one called MyRange to hold the entire target range, and the other called MyCell to hold each cell in the range as the macro enumerates through them one by one.
2. When you run a macro, it destroys the undo stack. This means you can't undo the changes a macro makes. Because we are actually changing data, we need to give ourselves the option of saving the workbook before running the macro. This is what Step 2 does. Here, we call up a message box that asks if we want to save the workbook first. It gives us three choices: Yes, No, and Cancel. Clicking Yes saves the workbook and continues with the macro. Clicking Cancel exits the procedure without running the macro. Clicking No runs the macro without saving the workbook.
3. Step 3 fills the MyRange variable with the target range. In this example, we are using the selected range — the range that was selected on the spreadsheet. You can easily set the
MyRange variable to a specific range such as Range(“A1:Z100”). Also, if your target range is a named range, you can simply enter its name: Range(“MyNamedRange”).
4. Step 4 starts looping through each cell in the target range, activating each cell.
5. After a cell is activated, Step 5 uses the IsEmpty function to make sure the cell is not empty. We do this to save a little on performance by skipping the cell if there is nothing in it. We then pass the cell's value through Left function. The Left function allows you to extract out the nth left-most characters in a string. In this scenario, we need the left five characters in order to truncate the ZIP code to five digits.
6. Step 6 loops back to get the next cell. After all of the cells in the target range are activated, the macro ends.
As you may have guessed, you can also use the Right function to extract out the nth right-most characters in a string. As an example, it's not uncommon to work with product numbers where the first few characters hold a particular attribute or meaning, whereas the last few characters point to the actual product (as in 100-4567). You can extract out the actual product by using Right(Product_Number, 4).
Because we define the target range as the current selection, be sure to select the area where your data exists before running this code. In other words, you wouldn't want to select cells that don't conform to
the logic you placed in this macro. Otherwise, every cell you select is truncated, whether you mean it to be or not. Of course, you can ensure this is never a problem by explicitly defining the target range, such as Set MyRange = Range(“A1:Z100”).
Macro 51: Padding Cells with Zeros
Many systems require unique identifiers (such as customer number, order number, or product number) to have a fixed character length. For instance, you frequently see customer numbers that look like this: 00000045478. This concept of taking a unique identifier and forcing it to have a fixed length is typically referred to as padding. The number is padded with zeros to achieve the prerequisite character length.
It's a pain to do this manually in Excel. However, with a macro, padding numbers with zeros is a breeze.
Some Excel gurus are quick to point out that you can apply a custom number format to pad numbers with zeros by going to the Format Cells dialog box, selecting Custom on the Number tab, and entering
0000000000 as the custom format.
The problem with this solution is that the padding is cosmetic only. A quick glance at the formula bar reveals that the data actually remains numeric without the padding (it does not become textual). So if you copy and
paste the data into another platform or non-Excel table, you lose the cosmetic padding.
How it works
Say that all your customer numbers need to be 10 characters long. So for each customer number, you need to pad the number with enough zeros to get it to 10 characters. This macro does just that.
As you review this macro, keep in mind that you need to change the padding logic in Step 5 to match your situation.
Sub Macro51()
‘Step 1: Declare your variables
Dim MyRange As Range
Dim MyCell As Range
‘Step 2: Save the Workbook before changing cells? Select Case MsgBox(“Can't Undo this action. “ & _ “Save Workbook First?”, vbYesNoCancel)
Case Is = vbYes ThisWorkbook.Save Case Is = vbCancel Exit Sub
End Select
‘Step 3: Define the target Range. Set MyRange = Selection
‘Step 4: Start looping through the range. For Each MyCell In MyRange
‘Step 5: Pad with ten zeros then take the right 10
If Not IsEmpty(MyCell) Then MyCell.NumberFormat = “@” MyCell = “0000000000” & MyCell MyCell = Right(MyCell, 10)
End If
‘Step 6: Get the next cell in the range
Next MyCell
End Sub
1. Step 1 declares two Range object variables, one called MyRange to hold the entire target range, and the other called MyCell to hold each cell in the range as the macro enumerates through them one by one.
2. When you run a macro, it destroys the undo stack, meaning that you can't undo the changes a macro makes. Because we are actually changing data, we need to give ourselves the option of saving the workbook before running the macro. This is what Step 2 does. Here, we call up a message box that asks if we want to save the workbook first. It then gives us three choices: Yes, No, and Cancel. Clicking Yes saves the workbook and continues with the macro. Clicking Cancel exits the procedure without running the macro. Clicking No runs the macro without saving the workbook.
3. Step 3 fills the MyRange variable with the target range. In this example, we use the selected range — the range that was selected on the spreadsheet. You can easily set the MyRange variable to a specific range such as Range(“A1:Z100”). Also, if your target range is a namedrange, you can simply enter its name: Range(“MyNamedRange”).
4. Step 4 starts looping through each cell in the target range, activating each cell.
5. After a cell is activated, Step 5 uses the IsEmpty function to make sure the cell is not empty. We do this to save a little on performance by skipping the cell if there is nothing in it.
The macro then ensures that the cell is formatted as text. This is because a cell formatted as a number cannot have leading zeros — Excel would automatically remove them. On the next line, we use the NumberFormat property to specify that the format is @. This symbol indicates text formatting.
Next, the macro concatenates the cell value with 10 zeros. We do this simply by explicitly entering 10 zeros in the code, and then using the ampersand (&) to combine them with the cell value.
Finally, Step 5 uses the Right function to extract out the 10 right-most characters. This effectively gives us the cell value, padded with enough zeros to make 10 characters.
6. Step 6 loops back to get the next cell. After all cells in the target range are activated, the macro ends.
Macro 52: Replace Blanks Cells with a Value
In some analyses, blank cells can cause of all kinds of trouble. They can cause sorting issues, they can prevent proper auto filling, they can cause your pivot tables to apply the Count function instead of the Sum function, and so on.
Blanks aren't always bad, but if they are causing you trouble, this is a macro you can use to quickly replace the blanks in a given range with a value that indicates a blank cell.
How it works
This macro enumerates through the cells in the given range, and then uses the Len function to check the length of the value in the active cell. Blank cells have a character length of 0. If the length is indeed 0, the macro enters a 0 in the cell, effectively replacing the blanks.
Sub Macro52()
‘Step 1: Declare your variables
Dim MyRange As Range
Dim MyCell As Range
‘Step 2: Save the Workbook before changing cells? Select Case MsgBox(“Can't Undo this action. “ & _ “Save Workbook First?”, vbYesNoCancel)
Case Is = vbYes ThisWorkbook.Save Case Is = vbCancel Exit Sub
End Select
‘Step 3: Define the target Range. Set MyRange = Selection
‘Step 4: Start looping through the range. For Each MyCell In MyRange
‘Step 5: Ensure the cell has Text formatting. If Len(MyCell.Value) = 0 Then
MyCell = 0
End If
‘Step 6: Get the next cell in the range
Next MyCell
End Sub
1. We first declare two Range object variables, one called MyRange to hold the entire target range, and the other called MyCell to hold each cell in the range as the macro enumerates through them one by one.
2. When you run a macro, it destroys the undo stack. This means you can't undo the changes a macro makes. Because we are actually changing data, we need to give ourselves the option of saving the workbook before running the macro. This is what Step 2 does. Here, we call up a message box that asks if we want to save the workbook first. It then gives us three choices: Yes, No, and Cancel. Clicking Yes saves the workbook and continues with the macro. Clicking Cancel exits the procedure without running the macro. Clicking No runs the macro without saving the workbook.
3. Step 3 fills the MyRange variable with the target range. In this example, we are using the selected range — the range that was selected on the spreadsheet. You can easily set the
MyRange variable to a specific range such as Range(“A1:Z100”). Also, if your target range is a named range, you can simply enter its name: Range(“MyNamedRange”).
4. Step 4 starts looping through each cell in the target range, activating each cell.
5. After a cell is activated, we use the IsEmpty function to make sure the cell is not empty. We do this to save a little on performance by skipping the cell if there is nothing in it. We then use the Len function, which is a standard Excel function that returns a number corresponding to the length of the string being evaluated. If the cell is blank, the length will be 0, at which point, the macro replaces the blank with a 0. You could obviously replace the blank with any value you'd like (N/A, TBD, No Data, and so on).
6. Step 6 loops back to get the next cell. After all cells in the target range are activated, the macro ends.
Because we define the target range as the current selection, you want to be sure to select the area where your data exists before running this code. That is to say, you wouldn't want to select the entire worksheet. Otherwise, every empty cell in the spreadsheet would be filled with a zero. Of course, you can ensure this is never a problem by explicitly defining a range, such as Set MyRange = Range(“A1:Z100”).
Macro 53: Append Text to the Left or Right of
Your Cells
Every so often, you may come upon a situation where you need to attach data to the beginning or end of the cells in a range. For instance, you may need to add an area code to a set of phone numbers. This macro demonstrates how you can automate the data standardization tasks that require appending data to values.
How it works
This macro uses two Range object variables to go through the target range, leveraging the For Each statement to activate each cell in the target range. Every time a cell is activated, the macro attaches an area code to the beginning of the cell value.
Sub Macro53()
‘Step 1: Declare your variables
Dim MyRange As Range
Dim MyCell As Range
‘Step 2: Save the Workbook before changing cells? Select Case MsgBox(“Can't Undo this action. “ & _ “Save Workbook First?”, vbYesNoCancel)
Case Is = vbYes ThisWorkbook.Save Case Is = vbCancel Exit Sub
End Select
‘Step 3: Define the target Range. Set MyRange = Selection
‘Step 4: Start looping through the range. For Each MyCell In MyRange
‘Step 5: Ensure the cell has Text formatting.
If Not IsEmpty(MyCell) Then MyCell = “(972) “ & MyCell End If
‘Step 6: Get the next cell in the range
Next MyCell
End Sub
1. Step 1 declares two Range object variables, one called MyRange to hold the entire target range, and the other called MyCell to hold each cell in the range as we enumerate through them one by one.
2. When you run a macro, it destroys the undo stack. This means you can't undo the changes a macro makes. Because we are actually changing data, we need to give ourselves the option of saving the workbook before running the macro. This is what Step 2 does. Here, we call up a message box that asks if we want to save the workbook first. It then gives us three choices: Yes, No, and Cancel. Clicking Yes saves the workbook and continues with the macro. Clicking Cancel exits the procedure without running the macro. Clicking No runs the macro without saving the workbook.
3. Step 3 fills the MyRange variable with the target range. In this example, we are using the selected range — the range that was selected on the spreadsheet. You can easily set the
MyRange variable to a specific range such as Range(“A1:Z100”). Also, if your target range is a named range, you can simply enter its name: Range(“MyNamedRange”).
4. Step 4 starts looping through each cell in the target range, activating each cell as we go through.
5. After a cell is activated, we use the ampersand (&) to combine an area code with the cell value. If you need to append text to the end of the cell value, you would simply place the ampersand and the text at the end. For instance, MyCell = MyCell & “Added Text”.
6. Step 6 loops back to get the next cell. After all cells in the target range are activated, the macro ends.
Macro 54: Create a Super Data Cleanup Macro
As we mentioned at the beginning of this Part, you can combine the macros we have covered thus far into a super data cleanup macro. This allows you to clean and standardize your data in one fellswoop, saving loads of time and headaches.
How it works
In this macro, we combine several of the data transformation macros we have covered into one. Note that we only need to declare the two Range object variables one time. With each action, we point these variables to different ranges. Although you have to alter the ranges and the tasks in this macro, it gives you a good idea of how to start an all-purpose data cleanup procedure that suits your needs.
Sub Macro54()
‘Step 1: Declare your variables
Dim MyRange As Range
Dim MyCell As Range
‘Step 2: Save the Workbook before changing cells? Select Case MsgBox(“Can't Undo this action. “ & _ “Save Workbook First?”, vbYesNoCancel)
Case Is = vbYes ThisWorkbook.Save Case Is = vbCancel Exit Sub
End Select
‘Step 3: Perform Text to Columns
Set MyRange = Range(“F6:I17”) For Each MyCell In MyRange
If Not IsEmpty(MyCell) Then MyCell.Value = MyCell.Value End If
Next MyCell
‘Step 4: Pad Customer Numbers with zeros
Set MyRange = Range(“B6:B17”) For Each MyCell In MyRange
If Not IsEmpty(MyCell) Then MyCell.NumberFormat = “@” MyCell = “0000000000” & MyCell MyCell = Right(MyCell, 10)
End If
Next MyCell
‘Step 5: Truncate Postal Codes to 5 digits
Set MyRange = Range(“C6:C17”) For Each MyCell In MyRange
If Not IsEmpty(MyCell) Then
MyCell = Left(MyCell, 5) End If
Next MyCell
‘Step 6: Add Area code to Phone Numbers
Set MyRange = Range(“D6:D17”) For Each MyCell In MyRange
If Not IsEmpty(MyCell) Then MyCell = “(972) “ & MyCell End If
Next MyCell
‘Step 7: Trim Spaces from Product Numbers
Set MyRange = Range(“E6:E17”) For Each MyCell In MyRange
If Not IsEmpty(MyCell) Then
MyCell = Trim(MyCell) End If
Next MyCell
‘Step 8: Replace Blanks with zeros
Set MyRange = Range(“F6:I17”) For Each MyCell In MyRange
If Len(MyCell.Value) = 0 Then
MyCell = 0
End If
Next MyCell
End Sub
Macro 55: Clean Up Non-Printing Characters
Sometimes you have non-printing characters in your data such as line feeds, carriage returns, and non-breaking spaces. These characters often need to be removed before you can use the data for serious analysis.
Now, anyone who has worked with Excel for more than a month knows about the Find and
Replace functionality. You may have even recorded a macro while performing a Find and Replace (a recorded macro is an excellent way to automate your find and replace procedures). So your initial reaction may be to simply find and replace these characters. The problem is that these non- printing characters are for the most part invisible and thus difficult to clean up with the normal Find and Replace routines. The easiest way to clean them up is through VBA.
If you find yourself struggling with those pesky invisible characters, use this general purpose macro to find and remove all the non-printing characters.
How it works
This macro is a relatively simple Find and Replace routine. We are using the Replace method, telling Excel what to find and what to replace it with. This is similar to the syntax you would see when recording a macro while manually performing a Find and Replace.
The difference is that instead of hard-coding the text to find, this macro uses character codes to
specify our search text. Every character has an underlying ASCII code, similar to a serial number. For instance, the lowercase letter a has an ASCII code of 97. The lower case letter c has an ASCII code of 99. Likewise, invisible characters also have a code:
The line feed character code is 10.
The carriage return character code is 13.
The non-breaking space character code is 160.
This macro utilizes the Replace method, passing each character's ASCII code as the search item. Each character code is then replaced with an empty string:
Sub Macro55()
‘Step 1: Remove Line Feeds
ActiveSheet.UsedRange.Replace What:=Chr(10), Replacement:=””
‘Step 2: Remove Carriage Returns
ActiveSheet.UsedRange.Replace What:=Chr(13), Replacement:=””
‘Step 3: Remove Non-Breaking Spaces ActiveSheet.UsedRange.Replace What:=Chr(160), Replacement:=”” End Sub
1. Step 1 looks for and removes the Line Feed character. The code for this character is 10. We can identify the code 10 character by passing id through the Chr function. After Chr(10) is identified as the search item, this step then passes an empty string to the Replacement argument.
Note the use of ActiveSheet.UsedRange. This essentially tells Excel to look in all the cells
that have had data entered into them. You can replace the UsedRange object with an actual range if needed.
2. Step 2 finds and removes the carriage return character.
3. Step 3 finds and removes the non-breaking spaces character.
The characters covered in this macro are only a few of many non-printing characters. However, these are the ones you most commonly run into. If you work with others, you can simply add a new line of code, specifying the appropriate character code. Type ASCII code listing into any search engine to see a list of the codes for various characters.
Macro 56: Highlight Duplicates in a Range of Data
Ever wanted to expose the duplicate values in a range? The macro in this section does just that. There are many manual ways to find and highlight duplicates — ways involving formulas, conditional formatting, sorting, and so on. However, all these manual methods take setup and some level of maintenance as the data changes. This macro simplifies the task, allowing you to find and highlight duplicates in your data with a click of the mouse.
How it works
This macro enumerates through the cells in the target range, leveraging the For Each statement to activate each cell one at a time. We then use the CountIf function to count the number of times the value in the active cell occurs in the range selected. If that number is greater than one, we format the cell yellow.
Sub Macro56()
‘Step 1: Declare your variables
Dim MyRange As Range
Dim MyCell As Range
‘Step 2: Define the target Range. Set MyRange = Selection
‘Step 3: Start looping through the range. For Each MyCell In MyRange
‘Step 4: Ensure the cell has Text formatting.
If WorksheetFunction.CountIf(MyRange, MyCell.Value) > 1 Then
MyCell.Interior.ColorIndex = 36
End If
‘Step 5: Get the next cell in the range
Next MyCell
End Sub
1. Step 1 declares two Range object variables, one called MyRange to hold the entire target range, and the other called MyCell to hold each cell in the range as the macro enumerates through them one by one.
2. Step 2 fills the MyRange variable with the target range. In this example, we are using the selected range — the range that was selected on the spreadsheet. You can easily set the
MyRange variable to a specific range such as Range(“A1:Z100”). Also, if your target range is a named range, you can simply enter its name: Range(“MyNamedRange”).
3. Step 3 starts looping through each cell in the target range, activating each cell.
4. The WorksheetFunction object provides a way for us to be able to run many of Excel's spreadsheet functions in VBA. Step 4 uses the WorksheetFunction object to run a CountIf function in VBA.
In this case, we are counting the number of times the active cell value (MyCell.Value) is found in the given range (MyRange). If the CountIf expression evaluates to greater than 1, the macro changes the interior color of the cell.
5. Step 5 loops back to get the next cell. After all cells in the target range are activated, the macro ends.
Macro 57: Hide All Rows but Those Containing
Duplicate Data
With the previous macro, you can quickly find and highlight duplicates in your data. This in itself can be quite useful. But if you have many records in your range, you may want to take the extra step of hiding all the non-duplicate rows. Doing so exposes the duplicate values further because they will be the only rows showing.
How it works
This macro enumerates through the cells in the target range, leveraging the For Each statement to activate each cell one at a time. We then use the CountIf function to count the number of times the value in the active cell occurs in the range selected. If that number is one, we hide the row in which the active cell resides. If that number is greater than one, we format the cell yellow and leave the row visible.
Sub Macro57()
‘Step 1: Declare your variables
Dim MyRange As Range
Dim MyCell As Range
‘Step 2: Define the target Range. Set MyRange = Selection
‘Step 3: Start looping through the range. For Each MyCell In MyRange
‘Step 4: Ensure the cell has Text formatting. If Not IsEmpty(MyCell) Then
If WorksheetFunction.CountIf(MyRange, MyCell) > 1 Then
MyCell.Interior.ColorIndex = 36
MyCell.EntireRow.Hidden = False
Else
MyCell.EntireRow.Hidden = True
End If
End If
‘Step 5: Get the next cell in the range
Next MyCell
End Sub
1. Step 1 declares two Range object variables, one called MyRange to hold the entire target range, and the other called MyCell to hold each cell in the range as we enumerate through them one by one.
2. Step 2 fills the MyRange variable with the target range. In this example, we are using the selected range — the range that was selected on the spreadsheet. You can easily set the
MyRange variable to a specific range such as Range(“A1:Z100”). Also, if your target range is a named range, you can simply enter its name: Range(“MyNamedRange”).
3. Step 3 loops through each cell in the target range, activating each cell as we go through.
4. We first use the IsEmpty function to make sure the cell is not empty. We do this so the macro won't automatically hide rows with no data in the target range.
We then use the WorksheetFunction object to run a CountIf function in VBA. In this particular scenario, we are counting the number of times the active cell value (MyCell.Value) is found in the given range (MyRange).
If the CountIf expression evaluates to greater than 1, we change the interior color of the cell and set the EntireRow property to Hidden=False. This ensures the row is visible.
If the CountIf expression does not evaluate to greater than 1, the macro jumps to the Else argument. Here we set the EntireRow property to Hidden=True. This ensures the row is not visible.
5. Step 5 loops back to get the next cell. After all cells in the target range are activated, the macro ends.
Because we define the target range as the current selection, you want to be sure to select the area where your data exists before running this code. You wouldn't want to select an entire column or the entire worksheet. Otherwise, any cell that contains data that is unique (not duplicated) triggers the hiding of the row. Alternatively, you can explicitly define the target range to ensure this is never a problem — such as Set MyRange = Range(“A1:Z100”).
Macro 58: Selectively Hide AutoFilter Drop-down
Arrows
It goes without saying that the AutoFilter function in Excel is one of the most useful. Nothing else allows for faster on-the-spot filtering and analysis. The only problem is that the standard AutoFilter functionality applies drop-down arrows to every column in the chosen dataset (see Figure 5-4). This is all right in most situations, but what if you want to prevent your users from using the AutoFilter drop-down arrows on some of the columns in your data?
The good news is that with a little VBA, you can selectively hide AutoFilter drop-down arrows, as shown in Figure 5-5.
Figure 5-4: The standard AutoFilter functionality adds drop-down arrows to all of the columns in your data.
Figure 5-5: With a little VBA, you can choose to hide certain AutoFilter drop-down arrows.
How it works
In VBA, we can use the AutoFilter object to turn on AutoFilters for a specific range. For instance:
Range(“B5:G5”).AutoFilter
After an AutoFilter is applied, we can manipulate each of the columns in the AutoFilter by
pointing to it. For example, you can perform some action on the third column in the AutoFilter, like this:
Range(“B5:G5”).AutoFilter Field:3
You can perform many actions on an AutoFilter field. In this scenario, we are interested in making the drop-down arrow on field three invisible. For this, we can use the VisibleDropDown parameter. Setting this parameter to False makes the drop-down arrow invisible.
Range(“B5:G5”).AutoFilter Field:3, VisibleDropDown:=False
Here is an example of a macro where we turn on AutoFilters, and then make only the first and last drop-down arrows visible.
Sub Macro58()
With Range(“B5:G5”)
.AutoFilter
.AutoFilter Field:=1, VisibleDropDown:=True
.AutoFilter Field:=2, VisibleDropDown:=False
.AutoFilter Field:=3, VisibleDropDown:=False
.AutoFilter Field:=4, VisibleDropDown:=False
.AutoFilter Field:=5, VisibleDropDown:=False
.AutoFilter Field:=6, VisibleDropDown:=True
End With
End Sub
Not only are we pointing to a specific range, but we are also explicitly pointing to each field. When implementing this type of a macro in your environment, alter the code to suit your particular data set.
Macro 59: Copy Filtered Rows to a New
Workbook
Often, when you're working with a set of data that is AutoFiltered, you want to extract the filtered rows to a new workbook. Of course, you can manually copy the filtered rows, open a new workbook, paste the rows, and then format the newly pasted data so that all the columns fit. But if you are doing this frequently enough, you may want to have a macro to speed up the process.
How it works
This macro captures the AutoFilter range, opens a new workbook, then pastes the data.
Sub Macro59()
‘Step 1: Check for AutoFilter - Exit if none exists
If ActiveSheet.AutoFilterMode = False Then
Exit Sub
End If
‘Step 2: Copy the Autofiltered Range to new workbook ActiveSheet.AutoFilter.Range.Copy Workbooks.Add.Worksheets(1).Paste
‘Step 3: Size the columns to fit
Cells.EntireColumn.AutoFit
End Sub
1. Step 1 uses the AutoFilterMode property to check whether the sheet even has AutoFilters applied. If not, we exit the procedure.
2. Each AutoFilter object has a Range property. This Range property obligingly returns the rows to which the AutoFilter applies, meaning it returns only the rows that are shown in the filtered data set. In Step 2, we use the Copy method to capture those rows, and then we paste the rows to a new workbook. Note that we use Workbooks.Add.Worksheets(1). This tells Excel to paste the data into the first sheet of the newly created workbook.
3. Step 3 simply tells Excel to size the column widths to autofit the data we just pasted.
Macro 60: Create a New Sheet for Each Item in an
AutoFilter
One of the most common tasks an Excel user is confronted with is separating a data set into separate sheets. For instance, if you have a set of data that has rows for the east, west, south, and north regions of the U.S., you may be asked to create a new sheet for the east data, a new sheet for the west data, a new sheet for the south, and one for the north. In these situations, you would normally have to manually filter each region, and then copy and paste the data into new sheets. This can be quite a painful exercise if you have to do it one time. If you have to perform this same exercise on an ongoing basis? Well, let's just say it can be difficult to come to work.
The good news is that you can use a macro to do the heavy lifting for you.
How it works
The basic premise of this macro is in itself simple. We start with a data set that contains an
AutoFilter (similar to the one shown in Figure 5-6).
Figure 5-6: Start with a data set that has an AutoFilter applied.
We point the macro to the field that is used to separate the data into separate sheets. In this case, we need to create a separate sheet for each region. As you can see in Figure 5-6, the Region field is the first field in the AutoFiltered data set.
The macro goes through this field, capturing the unique data items in this field (North, South, East, West). Then one at a time, it uses each unique data item as the filter criteria on the AutoFilter, effectively filtering the data for that item.
Each time a region is filtered, the macro copies the filtered range and pastes the data into a new sheet. After the data is pasted, it names the sheet the same name as the filter criteria.
This macro is a little tough to look at first glance, so take the time to walk through each step in detail.
Sub Macro60()
‘Step 1: Declare your Variables
Dim MySheet As Worksheet
Dim MyRange As Range
Dim UList As Collection Dim UListValue As Variant Dim i As Long
‘Step 2: Set the Sheet that contains the AutoFilter
Set MySheet = ActiveSheet
‘Step 3: If the sheet is not auto-filtered, then exit
If MySheet.AutoFilterMode = False Then
Exit Sub
End If
‘Step 4: Specify the Column # that holds the data you want filtered
Set MyRange = Range(MySheet.AutoFilter.Range.Columns(1).Address)
‘Step 5: Create a new Collection Object
Set UList = New Collection
‘Step 6: Fill the Collection Object with Unique Values
On Error Resume Next
For i = 2 To MyRange.Rows.Count
UList.Add MyRange.Cells(i, 1), CStr(MyRange.Cells(i, 1)) Next i
On Error GoTo 0
‘Step 7: Start looping in through the collection Values
For Each UListValue In UList
‘Step 8: Delete any Sheets that may have been previously created
On Error Resume Next Application.DisplayAlerts = False Sheets(CStr(UListValue)).Delete Application.DisplayAlerts = True On Error GoTo 0
‘Step 9: Filter the AutoFilter to match the current Value
MyRange.AutoFilter Field:=1, Criteria1:=UListValue
‘Step 10: Copy the AutoFiltered Range to new Sheet
MySheet.AutoFilter.Range.Copy
Worksheets.Add.Paste
ActiveSheet.Name = Left(UListValue, 30) Cells.EntireColumn.AutoFit
‘Step 11: Loop back to get the next collection Value
Next UListValue
‘Step 12: Go back to main Sheet and removed filters
MySheet.AutoFilter.ShowAllData
MySheet.Select
End Sub
1. Step 1 starts the macro by declaring five variables. MySheet is a worksheet variable that is used to identify the sheet in which the AutoFiltered data resides. MyRange is a range variable that holds the range of our main filter field (the Region field in this scenario). UList is a Collection object that helps us extract the unique items from our main filter field. UListValue holds the individual unique items as we enumerate through them. Finally, the i variable serves as a simple counter for our MyRange variable.
2. Step 2 sets the MySheet variable to hold the sheet in which the AutoFilter resides. It's important to do this because we need to refer back to this sheet throughout the macro. Here, we are assuming the macro will be fired from the sheet that holds the AutoFilter, so we use ActiveSheet.
You can also alter the macro to explicitly use a sheet name instead of ActiveSheet, like Set MySheet = Sheets(“YourSheetName”). This is safer because you have no risk of unintentionally firing the macro from the wrong sheet. But it essentially ensures that the macro only works for the sheet you explicitly specified.
3. Step 3 checks the AutoFilterMode property to see if the sheet even has AutoFilters applied. If not, it exits the procedure.
4. If the macro reaches Step 4, we have determined that there is indeed an AutoFilter applied in
MySheet.
Now we need to capture the column number that holds the items that will be used to parse our
data set into separate sheets. As you can see, in Figure 5-6, the region column is the first column in our AutoFilter range. So we set the MyRange field to Columns(1) of the AutoFilter range. This is important! We eventually use the specified column to create a unique list of items with which we parse our data. When you implement this macro in your environment, you need to change the column number used to match the field you need to parse.
5. Step 5 initializes the UList Collection object. A Collection object is a container that can hold an array of unique data items.
In fact, a Collection object can only hold unique data. If you try to fill it with non-unique data, it throws an error. Because of this, it makes for an excellent way to quickly find and store a list of unique data items.
We use the collection object to hold a unique list of items from our MyRange variable. In this scenario, because our MyRange variable points to the Region column, the Collection object eventually holds a unique list of regions (East, North, South, West).
6. Step 6 fills the UList Collection object with the unique data items in MyRange.
To do so, it uses the i variable to loop through the rows of the MyRange column. You'll notice that we start i at 2; this is because row 1 contains the header label (Region). We don't want to include the header label as one of the unique items in our collection object.
On each loop, the macro tries to add the current cell to the UList collection. The syntax to add an item to a collection is
CollectionName.Add ItemName, UniqueKeyIdentifier
In this case, we are adding each cell in MyRange as both the item name and unique key identifier. Because the UList collection throws an error if the data items are not unique, we wrap the entire section in On Error Resume Next and On Error Goto 0. This ensures that if duplicate items are added, the UList collection ignores them. At the end of the loop, we have a unique list of all the data items in MyRange. Again, in this scenario, this means we have a unique list of regions (East, North, South, West).
7. Step 7 works exclusively with the UList collection. This collection holds the unique list of items we use as both the filter criteria for our AutoFilter and the Sheet names for our newly created sheets. The macro starts looping through the list with the UListValue variable.
8. Each time we run this macro, a new sheet is added for each unique item in our target filter field, with sheet names to match. If we run this macro more than one time, an error may be thrown because we will be creating a sheet that already exists. To ensure this doesn't happen, Step 8 deletes any sheet whose name matches the UListValue data item.
9. Step 9 uses the UListValue to filter the AutoFilter. We are dynamically passing the
UListValue as the Criteria for Field1:
MyRange.AutoFilter Field:=1, Criteria1:=UListValue
The field number here is very important! Because the Region field is the first field (see Figure
5-6), we are specifying Field 1. When you implement this macro in your environment, you need
to change the field number to match the field you need to parse.
10. Each AutoFilter object has a Range property. This Range property returns the rows to which the AutoFilter applies, meaning it returns only the rows that are shown in the filtered data set. Step 10 uses the Copy method to capture the newly filtered rows and paste the rows to a
new sheet. The macro then names the sheet to match UListValue.
Note that we are wrapping UListValue in the Left function. Specifically, we are telling Excel to limit the name of the sheet to the left 31 characters in the UListValue. We do this because the limit for sheet names is 31 characters. Anything longer than 31 characters throws an error.
11. Step 11 loops back to get the next value from the UList collection.
12. The macro ends by jumping to the original AutoFiltered data and clearing all filters. You may be wondering how to create a new workbook for each item in an AutoFilter.
This is a relatively easy change. Simply replace the code in Step 10 with this code.
‘Step 10: Copy the AutoFiltered Range to new Workbook ActiveSheet.AutoFilter.Range.Copy Workbooks.Add.Worksheets(1).Paste Cells.EntireColumn.AutoFit
ActiveWorkbook.SaveAs _
Filename:=”C:\Temp\” & CStr(UListValue) & “.xlsx” ActiveWorkbook.Close
Pay special attention to the fact that the path in this code is hard-coded to save in the C:Temp folder.
If you like, you can change this to suit your needs.
Macro 61: Show Filtered Columns in the Status
Bar
When you have a large table with many columns that are AutoFiltered, it is sometimes hard to tell which columns are filtered and which aren't. Of course, you could scroll through the columns, peering at each AutoFilter drop-down list for the telltale icon indicating the column is filtered, but
that can get old quickly.
This macro helps by specifically listing all the columns that are filtered in the status bar. The status bar is the bar (seen here in Figure 5-7) that runs across the bottom of the Excel window.
Figure 5-7: This macro lists all filtered columns in the status bar.
How it works
This macro loops through the fields in our AutoFiltered data set. As we loop, we check to see if each field is actually filtered. If so, we capture the field name in a text string. After looping through all the fields, we pass the final string to the StatusBar property.
Sub Macro61()
‘Step 1: Declare your Variables
Dim AF As AutoFilter
Dim TargetField As String Dim strOutput As String Dim i As Integer
‘Step 2: Check if AutoFilter exists - If not Exit If ActiveSheet.AutoFilterMode = False Then Application.StatusBar = False
Exit Sub
End If
‘Step 3: Set AutoFilter and start looping
Set AF = ActiveSheet.AutoFilter
For i = 1 To AF.Filters.Count
‘Step 4: Capture filtered field names
If AF.Filters(i).On Then
TargetField = AF.Range.Cells(1, i).Value strOutput = strOutput & “ | “ & TargetField End If
Next
‘Step 5: Display the filters if there are any
If strOutput = “” Then Application.StatusBar = False Else
Application.StatusBar = “DATA IS FILTERED ON “ & strOutput
End If
End Sub
1. Step 1 declares four variables. AF is an AutoFilter variable that is used to manipulate the AutoFilter object. TargetField is a string variable we use to hold the field names of any field that is actually filtered. strOutput is the string variable we use to build out the final text that goes into the status bar. Finally, the i variable serves as a simple counter, allowing us to iterate through the fields in our AutoFilter.
2. Step 2 checks the AutoFilterMode property to see if sheet even has AutoFilters applied. If not, we set the StatusBar property to False. This has the effect of clearing the status bar, releasing control back to Excel. We then exit the procedure.
3. Step 3 sets the AF variable to the AutoFilter on the active sheet. We then set our counter to count from 1 to the maximum number of columns in the AutoFiltered range. The AutoFilter object keeps track of its columns with index numbers. Column 1 is index 1; column 2 is index 2, and so on. The idea is that we can loop through each column in the AutoFilter by using the i variable as the index number.
4. Step 4 checks the status of AF.Filters object for each (i) – i being the index number of the column we are evaluating. If the AutoFilter for that column is filtered in any way, the status for that column is On.
If the filter for the column is indeed on, we capture the name of the field in the TargetField variable. We actually get the name of the field by referencing the Range of our AF AutoFilter object. With this range, we can use the Cells item to pinpoint the field name. Cells(1,1) captures the value in row one, column one. Cells(1,2) captures the value in row one, column two, and so on.
As you can see in Step 4, we have hard-coded the row to 1 and used the i variable to indicate the column index. This means that as the macro iterates through the columns, it always captures the value in row one as the TargetField name (row one is where the field name is likely to be).
After we have the TargetField name, we can pass that information a simple string container (strOutput in our case). strOutput simply keeps all the target field names we find and concatenates them into a readable text string.
5. Step 5 first checks to make sure that there is something in the strOutput string. If strOutput is empty, it means the macro found no columns in our AutoFilter that were filtered. In this case, Step 5 simply sets the StatusBar property to False, releasing control back to Excel.
If strOutput is not empty, Step 5 sets the StatusBar property to equal some helper text along with our strOutput string.
How to use it
You ideally want this macro to run each time a field is filtered. However, Excel does not have an
OnAutoFilter event. The closest thing to that is the Worksheet_Calculate event. That being
said, AutoFilters in themselves don't actually calculate anything, so you need to enter a “volatile” function on the sheet that contains your AutoFiltered data. A volatile function is one that forces a recalculation when any change is made on the worksheet.
In the sample files that come with this book, notice that we use the =Now() function. The Now function is a volatile function that returns a date and time. With this on the sheet, the worksheet is sure to recalculate each time the AutoFilter is changed.
Place the Now function anywhere on your sheet (by typing =Now() in any cell). Then copy and paste the macro into the Worksheet_Calculate event code window:
Private Sub Worksheet_Deactivate() Application.StatusBar = False
End Sub
Private Sub Worksheet_Activate() Call Worksheet_Calculate
End Sub
Also, add this piece of code in the workbook BeforeClose event:
Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.StatusBar = False
End Sub
The Worksheet_Deactivate event clears the status bar when you move to another sheet or workbook. This avoids confusion as you move between sheets.
The Worksheet_Activate event fires the macro in Worksheet_Calculate. This brings back the
Status Bar indicators when you navigate back to the filtered sheet.
The Workbook_BeforeClose event clears the status bar when you close the workbook. This avoids confusion as you move between workbooks.
Part VI: Working with PivotTables
This Part gives you the lowdown on PivotTables: sorting them, formatting them, hiding data, and more.
In This Part
Macro 62 Create a Backwards-Compatible PivotTable
Macro 63 Refresh All PivotTables Workbook
Macro 64 Create a PivotTable Inventory Summary
Macro 65 Make All PivotTables Use the Same Pivot Cache
Macro 66 Hide All Subtotals in a PivotTable
Macro 67 Adjust All Pivot Data Field Titles
Macro 68 Set All Data Items to Sum
Macro 69 Apply Number Formatting for All Data Items
Macro 70 Sort All Fields in Alphabetical Order
Macro 71 Apply Custom Sort to Data Items
Macro 72 Apply PivotTable Restrictions
Macro 73 Apply Pivot Field Restrictions
Macro 74 Automatically Delete Pivot Table Drill-Down Sheets
Macro 75 Print Pivot Table for Each Report Filter Item
Macro 76 Create New Workbook for Each Report Filter Item
Macro 77 Transpose Entire Data Range with a PivotTable
Excel offers a fairly robust object model for PivotTables. You can use the macro recorder to create a macro that does just about anything with a PivotTable, and the macro gets you 90 percent of the way to automation. For instance, you can record a macro that builds a PivotTable, and that macro records your steps and duplicates your tasks with relatively high fidelity. So if you find yourself needing to automate tasks like filtering out the top 10 items or grouping data items, you can reliably turn to the macro recorder to help write the VBA needed.
That being said, certain PivotTable-related tasks are not easily solved with the macro recorder. This is what this Part focuses on. Here, we cover the most common scenarios where macros help you gain efficiencies when working with PivotTables.
The code for this Part can be found on this book's companion website. See this book's Introduction for more on the companion website.
Macro 62: Create a Backwards-Compatible
PivotTable
If you are still using Excel 2003, you may know about the compatibility headaches that come with PivotTables between Excel 2003 and later versions. As you can imagine, the extraordinary increases in PivotTable limitations lead to some serious compatibility questions. For instance, later versions of Excel PivotTables can have more than 16,384 column fields and more than
1,000,000 unique data items. Excel 2003 can have only 256 column fields and 32,500 unique data items.
To solve these compatibility issues, Microsoft has initiated the concept of Compatibility mode. Compatibility mode is a state that Excel automatically enters when opening an xls file. When Excel is in Compatibility mode, it artificially takes on the limitations of Excel 2003. This means while you are working with an xls file, you cannot exceed any of the Excel 2003 PivotTable limitations, allowing you (as a user of Excel 2007 or 2010) to create PivotTables that work with Excel 2003.
If you are not in Compatibility mode (meaning you are working with an xlsx or xlsm file) and you create a PivotTable, the PivotTable object turns into a hard table when opened in Excel 2003. That is to say, PivotTables that are created in xlsx or xlsm files are destroyed when opened in Excel
2003.
To avoid this fiasco manually, Excel 2007 and 2010 users must go through these steps:
1. Create a blank workbook.
2. Save the file as an xls file.
3. Close the file.
4. Open it up again.
5. Start creating the PivotTable.
This is enough to drive you up the wall if you've got to do this every day.
An alternative is to use a macro that automatically starts a PivotTable in Table in the Excel 2003 version — even if you are not in Compatibility mode!
How it works
If you record a macro while creating a PivotTable in Excel 2007 or Excel 2010, the macro recorder generates the code to create your PivotTable. This code has several arguments in it. One of the arguments is the Version property. As the name implies, the Version property specifies the version of Excel the PivotTable was created in. The nifty thing is that you can change the Version in the code to force Excel to create a PivotTable that will work with Excel 2003.
Here is a listing of the different versions you can specify:
• xlPivotTableVersion2000 - Excel 2000
• xlPivotTableVersion10 - Excel 2002
• xlPivotTableVersion11 - Excel 2003
• xlPivotTableVersion12 - Excel 2007
• xlPivotTableVersion14 - Excel 2010
Here is an example of a macro that starts a PivotTable using Range(“A3:N86”) on Sheet1 as the source data.
Note that we changed the Version and DefaultVersion properties to xlPivotTableVersion11. This ensures that the PivotTable starts off as one that will work in Excel 2003.
No need to save your workbook as an .xls file first or to be in Compatibility mode. You can use a simple macro like this (just change the source data range) to create a PivotTable that will automatically work with Excel 2003.
Sub Macro62()
Dim SourceRange As Range
Set SourceRange = Sheets(“Sheet1”).Range(“A3:N86”) ActiveWorkbook.PivotCaches.Create( _ SourceType:=xlDatabase, _
SourceData:=SourceRange, _ Version:=xlPivotTableVersion11).CreatePivotTable _ TableDestination:=””, _
TableName:=””, _ DefaultVersion:=xlPivotTableVersion11
End Sub
Keep in mind that creating a PivotTable in the Excel 2003 version will essentially force the PivotTable to take on the limits of Excel 2003. This means any new PivotTable limit increases or PivotTable features added in Excel 2007 or Excel 2010 will not be available in your 2003 version PivotTable.
Macro 63: Refresh All PivotTables Workbook
It's not uncommon to have multiple PivotTables in the same workbook. Many times, these PivotTables link to data that changes, requiring a refresh of the PivotTables. If you find that you need to refresh your PivotTables en masse, you can use this macro to refresh all PivotTables on demand.
How it works
It's important to know that each PivotTable object is a child of the worksheet it sits in. The macro has to first loop through the worksheets in a workbook first, and then loop through the PivotTables in each worksheet. This macro does just that — loops through the worksheets, and then loops through the PivotTables. On each loop, the macro refreshes the PivotTable.
Sub Macro63()
‘Step 1: Declare your Variables
Dim ws As Worksheet
Dim pt As PivotTable
‘Step 2: Loop through each sheet in workbook
For Each ws In ThisWorkbook.Worksheets
‘Step 3: Loop through each PivotTable For Each pt In ws.PivotTables pt.RefreshTable
Next pt Next ws End Sub
1. Step 1 first declares an object called ws. This creates a memory container for each worksheet we loop through. It also declares an object called pt, which holds each PivotTable the macro loops through.
2. Step 2 starts the looping, telling Excel we want to evaluate all worksheets in this workbook.Notice we are using ThisWorkbook instead of ActiveWorkbook. The ThisWorkbook object refers to the workbook that the code is contained in. The ActiveWorkbook object refers to the workbook that is currently active. 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, we don't want to risk refreshing PivotTables in other workbooks, so we use ThisWorkbook.
3. Step 3 loops through all the PivotTables in each worksheet, and then triggers the RefreshTable method. After all PivotTables have been refreshed, the macro moves to the next sheet. After all sheets have been evaluated, the macro ends.
As an alternative method for refreshing all PivotTables in the workbook, you can use ThisWorkbook.RefreshAll. This refreshes all the PivotTables in the workbook. However, it also refreshes all query tables. So if you have data tables that are connected to an external source or the web, these will be affected by the RefreshAll method. If this is not a concern, you can simply enter ThisWorkbook.RefreshAll into a standard module.
Macro 64: Create a PivotTable Inventory
Summary
When your workbook contains multiple PivotTables, it's often helpful to have an inventory summary (similar to the one shown here in Figure 6-1) that outlines basic details about the PivotTables. With this type of summary, you can quickly see important information like the location of each PivotTable, the location of each PivotTable's source data, and the pivot cache index each PivotTable is using.
The following macro outputs such a summary.
How it works
When you create a PivotTable object variable, you expose all of a PivotTable's properties — properties like its name, location, cache index, and so on. In this macro, we loop through each PivotTable in the workbook and extract specific properties into a new worksheet.
Because each PivotTable object is a child of the worksheet it sits in, we have to first loop through the worksheets in a workbook first, and then loop through the PivotTables in each worksheet.
Take a moment to walk through the steps of this macro in detail.
Sub Macro64()
‘Step 1: Declare your Variables
Dim ws As Worksheet Dim pt As PivotTable Dim MyCell As Range
‘Step 2: Add a new sheet with column headers
Worksheets.Add
Range(“A1:F1”) = Array(“Pivot Name”, “Worksheet”, _ “Location”, “Cache Index”, _
“Source Data Location”, _ “Row Count”)
‘Step 3: Start Cursor at Cell A2 setting the anchor here
Set MyCell = ActiveSheet.Range(“A2”)
‘Step 4: Loop through each sheet in workbook
For Each ws In Worksheets
‘Step 5: Loop through each PivotTable For Each pt In ws.PivotTables MyCell.Offset(0, 0) = pt.Name MyCell.Offset(0, 1) = pt.Parent.Name
MyRange.Offset(0, 2) = pt.TableRange2.Address MyRange.Offset(0, 3) = pt.CacheIndex MyRange.Offset(0, 4) = Application.ConvertFormula _ (pt.PivotCache.SourceData, xlR1C1, xlA1) MyRange.Offset(0, 5) = pt.PivotCache.RecordCount
‘Step 6: Move Cursor down one row and set a new anchor
Set MyRange = MyRange.Offset(1, 0)
‘Step 7: Work through all PivotTables and worksheets
Next pt
Next ws
‘Step 8: Size columns to fit ActiveSheet.Cells.EntireColumn.AutoFit End Sub
1. Step 1 declares an object called ws. This creates a memory container for each worksheet we loop through. We then declare an object called pt, which holds each PivotTable we loop through. Finally, we create a range variable called MyCell. This variable acts as our cursor as we fill in the inventory summary.
2. Step 2 creates a new worksheet and adds column headings that range from A1 to F1. Note that we can add column headings using a simple array that contains our header labels. This new worksheet remains our active sheet from here on out.
3. Just as you would manually place your cursor in a cell if you were to start typing data, Step 3 places the MyCell cursor in cell A2 of the active sheet. This is our anchor point, allowing us to navigate from here.
Throughout the macro, you see the use of the Offset property. The Offset property allows us
to move a cursor x number of rows and x number of columns from an anchor point. For instance,
Range(A2).Offset(0,1) would move the cursor one column to the right. If we wanted to move the cursor one row down, we would enter Range(A2).Offset(1, 0).
In the macro, we navigate by using Offset on MyCell. For example, MyCell.Offset(0,4) would move the cursor four columns to the right of the anchor cell. After the cursor is in place, we can enter data.
4. Step 4 starts the looping, telling Excel we want to evaluate all worksheets in this workbook.
5. Step 5 loops through all the PivotTables in each worksheet. For each PivotTable it finds, it extracts out the appropriate property and fills in the table based on the cursor position (see Step
3).
We are using six PivotTable properties: Name, Parent.Range, TableRange2.Address,
CacheIndex, PivotCache.SourceData, and PivotCache.Recordcount. The Name property returns the name of the PivotTable.
The Parent.Range property gives us the sheet where the PivotTable resides. The
TableRange2.Address property returns the range that the PivotTable object sits in.
The CacheIndex property returns the index number of the pivot cache for the PivotTable. A pivot cache is a memory container that stores all the data for a PivotTable. When you create a new PivotTable, Excel takes a snapshot of the source data and creates a pivot cache. Each time you refresh a PivotTable, Excel goes back to the source data and takes another snapshot, thereby refreshing the pivot cache. Each pivot cache has a SourceData property that identifies the location of the data used to create the pivot cache. The PivotCache.SourceData property tells us which range will be called upon when we refresh the PivotTable. You can also pull out the record count of the source data by using the PivotCache.Recordcount property.
6. Each time the macro encounters a new PivotTable, it moves the MyCell cursor down a row, effectively starting a new row for each PivotTable.
7. Step 7 tells Excel to loop back around to iterate through all PivotTables and all worksheets. After all PivotTables have been evaluated, we move to the next sheet. After all sheets have been evaluated, the macro moves to the last step.
8. Step 8 finishes off with a little formatting, sizing the columns to fit the data.
Macro 65: Make All PivotTables Use the Same
Pivot Cache
If you work with PivotTables enough, you will undoubtedly find the need to analyze the same dataset in multiple ways. In most cases, this process requires you to create separate PivotTables from the same data source.
The problem is that each time you create a PivotTable, you are storing a snapshot of the data source in a pivot cache. Every pivot cache that is created increases your memory usage and file size. The side effect of this behavior is that your spreadsheet bloats with redundant data. Making your PivotTables share the same cache prevents this.
Starting with Excel 2007, Microsoft built in an automatic pivot cache sharing algorithm that recognizes when you are creating a PivotTable from the same source as an existing PivotTable. This reduces the
instances of creating superfluous pivot caches. However, you can still inadvertently create multiple pivot caches if the number of rows or columns captured from your source range is different for each of your PivotTables.
In addition to the reduction in file size, there are other benefits to sharing a pivot cache:
• You can refresh one PivotTable and all others that share the pivot cache are refreshed also.
• When you add a Calculated Field to one PivotTable, your newly created calculated field shows up in the other PivotTables' field list.
• When you add a Calculated Item to one PivotTable, it shows up in the others as well.
• Any grouping or ungrouping you perform affects all PivotTables sharing the same cache.
How it works
With the last macro, you are able to take an inventory of all your PivotTables. In that inventory summary, you can see the pivot cache index of each PivotTable (see Figure 6-1). Using this, you can determine which PivotTable contains the most appropriate pivot cache, and then force all others to share the same cache.
In this example, we are forcing all PivotTables to the pivot cache used by PivotTable1 on the UnitsSold sheet.
Sub Macro65()
‘Step 1: Declare your Variables
Dim ws As Worksheet
Dim pt As PivotTable
‘Step 2: Loop through each sheet in workbook
For Each ws In ThisWorkbook.Worksheets
‘Step 3: Loop through each PivotTable For Each pt In ws.PivotTables pt.CacheIndex = _
Sheets(“Units Sold”).PivotTables(“PivotTable1”).CacheIndex
Next pt Next ws End Sub
1. Step 1 declares an object called ws. This creates a memory container for each worksheet we loop through. We also declare an object called pt, which holds each PivotTable we loop through.
2. Step 2 starts the looping, telling Excel we want to evaluate all worksheets in this workbook. Notice we are using ThisWorkbook instead of ActiveWorkbook. ThisWorkbook object refers to the workbook that the code is contained in. ActiveWorkbook object refers to the workbook that is currently active. 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, we don't want to risk affecting PivotTables in other workbooks, so we use ThisWorkbook.
3. Step 3 loops through all the PivotTables in each worksheet, and then sets the CachIndex to the same one used by PivotTable1 on the “Units Sold” sheet. After all PivotTables have been refreshed, we move to the next sheet. After all sheets have been evaluated, the macro ends.
Macro 66: Hide All Subtotals in a PivotTable
When you create a PivotTable, Excel includes subtotals by default. This inevitably leads to a PivotTable report that inundates the eyes with all kinds of numbers, making it difficult to analyze. Figure 6-2 demonstrates this.
Figure 6-2: Subtotals can sometimes hinder analysis.
Manually removing Subtotals is easy enough; right-click the field headers and uncheck the Subtotal option. But if you're constantly hiding subtotals, you can save a little time by automating the
process with a simple macro.
You can manually hide all subtotals at once by going to the Ribbon and selecting PivotTable Tools⇒Design⇒Layout⇒Subtotals⇒Do Not Show Subtotals. But again, if you are building an automated process that routinely manipulates pivot tables without manual intervention, you may prefer the macro option.
How it works
If you record a macro while hiding a Subtotal in a PivotTable, Excel produces code similar to this:
ActiveSheet.PivotTables(“Pvt1″).PivotFields(“Region”).Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
That's right; Excel passes an array with exactly 12 False settings. There are 12 instances of False because there are twelve types of Subtotals — Sum, Avg, Count, Min, and Max, just to name a few. So when you turn off Subtotals while recording a macro, Excel sets each of the possible Subtotal types to False.
An alternative way of turning off Subtotals is to first set one of the 12 Subtotals to True. This
automatically forces the other 11 Subtotal types to False. We then set the same Subtotal to False, effectively hiding all Subtotals. In this piece of code, we are setting the first Subtotal to True, and then setting it to False. This removes the subtotal for Region.
With ActiveSheet.PivotTables(“Pvt1″).PivotFields(“Region”)
.Subtotals(1) = True
.Subtotals(1) = False
End With
In our macro, we use this trick to turn off subtotals for every pivot field in the active PivotTable.
Sub Macro66()
‘Step 1: Declare your Variables
Dim pt As PivotTable
Dim pf As PivotField
‘Step 2: Point to the PivotTable in the active cell
On Error Resume Next
Set pt = ActiveSheet.PivotTables(ActiveCell.PivotTable.Name)
‘Step 3: Exit if active cell is not in a PivotTable
If pt Is Nothing Then
MsgBox “You must place your cursor inside of a PivotTable.” Exit Sub
End If
‘Step 4: Loop through all pivot fields and remove totals
For Each pf In pt.PivotFields pf.Subtotals(1) = True pf.Subtotals(1) = False
Next pf
End Sub
1. Step 1 declares two object variables. This macro uses pt as the memory container for the PivotTable and uses pf as a memory container for the pivot fields. This allows us to loop through all the pivot fields in the PivotTable.
2. This macro is designed so that we infer the active PivotTable based on the active cell. That is to say, the active cell must be inside a PivotTable for this macro to run. The assumption is that when the cursor is inside a particular PivotTable, we want to perform the macro action on that pivot.
Step 2 sets the pt variable to the name of the PivotTable on which the active cell is found. We do this by using the ActiveCell.PivotTable.Name property to get the name of the target pivot.
If the active cell is not inside of a PivotTable, an error is thrown. This is why the macro uses the On Error Resume Next statement. This tells Excel to continue with the macro if there is an error.
3. Step 3 checks to whether the pt variable is filled with a PivotTable object. If the pt variable is set to Nothing, the active cell was not on a PivotTable, thus no PivotTable could be assigned to the variable. If this is the case, we tell the user in a message box, and then we exit the procedure.
4. If the macro reaches Step 4, it has successfully pointed to a PivotTable. We are ready to loop to all the fields in the PivotTable. We use a For Each statement to iterate through each pivot field. Each time a new pivot field is selected, we apply our Subtotal logic. After all the fields have been evaluated, the macro ends.
Macro 67: Adjust All Pivot Data Field Titles
When you create a PivotTable, Excel tries to help you out by prefacing each data field header with Sum of, Count of, or whichever operation you use. Often, this is not conducive to your reporting needs. You want clean titles that match your data source as closely as possible. Although it's true that you can manually adjust the titles for you data fields (one at a time), this macro fixes them all in one go.
How it works
Ideally, the name of the each data item matches the field name from your source data set (the original source data used to create the PivotTable). Unfortunately, PivotTables won't allow you to name a data field the exact name as the source data field. The workaround for this is to add a space to the end of the field name. Excel considers the field name (with a space) to be different from the source data field name, so it allows it. Cosmetically, the readers of your spreadsheet don't notice the space after the name.
This macro utilizes this workaround to rename your data fields. It loops through each data field in the PivotTable, and then resets each header to match its respective field in the source data plus a space character.
Sub Macro67()
‘Step 1: Declare your Variables
Dim pt As PivotTable
Dim pf As PivotField
‘Step 2: Point to the PivotTable in the active cell
On Error Resume Next
Set pt = ActiveSheet.PivotTables(ActiveCell.PivotTable.Name)
‘Step 3: Exit if active cell is not in a PivotTable
If pt Is Nothing Then
MsgBox “You must place your cursor inside of a PivotTable.” Exit Sub
End If
‘Step 4: Loop through all pivot fields adjust titles
For Each pf In pt.DataFields pf.Caption = pf.SourceName & Chr(160) Next pf
End Sub
1. Step 1 declares two object variables. It uses pt as the memory container for our PivotTable and pf as a memory container for the data fields. This allows the macro to loop through all the data fields in the PivotTable.
2. This macro is designed so that we infer the active PivotTable based on the active cell. In other words, the active cell must be inside a PivotTable for this macro to run. We assume that when the cursor is inside a particular PivotTable, we want to perform the macro action on that pivot.
Step 2 sets the pt variable to the name of the PivotTable on which the active cell is found. We do this by using the ActiveCell.PivotTable.Name property to get the name of the target pivot.
If the active cell is not inside of a PivotTable, an error is thrown. This is why we use the On
Error Resume Next statement. This tells Excel to continue with the macro if there is an error.
3. In Step 3, we check to see if the pt variable is filled with a PivotTable object. If the pt variable is set to Nothing, the active cell was not on a PivotTable, thus no PivotTable could be assigned to the variable. If this is the case, we tell the user in a message box, and then we exit the procedure.
4. If the macro reaches Step 4, it has successfully pointed to a PivotTable. The macro uses a For Each statement to iterate through each data field. Each time a new pivot field is selected, the macro changes the field name by setting the Caption property to match the field's SourceName. The SourceName property returns the name of the matching field in the original source data.
To that name, the macro concatenates a non-breaking space character: Chr(160).
Every character has an underlying ASCII code, similar to a serial number. For instance, the lowercase letter a has an ASCII code of 97. The lowercase letter c has an ASCII code of 99. Likewise, invisible characters such as the space have a code. You can use invisible characters in your macro by passing their code through the CHR function.
After the name has been changed, the macro moves to the next data field. After all the data fields have been evaluated, the macro ends.
Macro 68: Set All Data Items to Sum
When creating a PivotTable, Excel, by default, summarizes your data by either counting or summing the items. The logic Excel uses to decide whether to sum or count the fields you add to your PivotTable is fairly simple. If all of the cells in a column contain numeric data, Excel chooses to Sum. If the field you are adding contains a blank or text, Excel chooses Count.
Although this seems to make sense, in many instances, a pivot field that should be summed legitimately contains blanks. In these cases, we are forced to manually go in after Excel and change the calculation type from Count back to Sum. That's if we're paying attention! It's not uncommon to miss the fact that a pivot field is being counted instead of summed up.
The macro in this section aims to help by automatically setting each data item's calculation type to
Sum.
How it works
This macro loops through each data field in the PivotTable and changes the Function property to xlSum. You can alter this macro to use any one of the calculation choices: xlCount, xlAverage, xlMin, xlMax, and so on. When you go into the code window and type pf.Function =, you see a drop-down list showing you all your choices (see Figure 6-3).
Figure 6-3: Excel helps out by showing you your enumeration choices.
Sub Macro68()
‘Step 1: Declare your Variables
Dim pt As PivotTable
Dim pf As PivotField
‘Step 2: Point to the PivotTable in the active cell
On Error Resume Next
Set pt = ActiveSheet.PivotTables(ActiveCell.PivotTable.Name)
‘Step 3: Exit if active cell is not in a PivotTable
If pt Is Nothing Then
MsgBox “You must place your cursor inside of a PivotTable.” Exit Sub
End If
‘Step 4: Loop through all pivot fields apply SUM For Each pf In pt.DataFields
pf.Function = xlSum
Next pf
End Sub
1. Step 1 declares two object variables. It uses pt as the memory container for the PivotTable and pf as a memory container for the data fields. This allows us to loop through all the data fields in the PivotTable.
2. This macro is designed so that we infer the active PivotTable based on the active cell. The active cell must be inside a PivotTable for this macro to run. The assumption is that when the cursor is inside a particular PivotTable, we want to perform the macro action on that pivot.
Step 2 sets the pt variable to the name of the PivotTable on which the active cell is found. We do this by using the ActiveCell.PivotTable.Name property to get the name of the target pivot.
If the active cell is not inside of a PivotTable, an error is thrown. This is why we use the On
Error Resume Next statement. This tells Excel to continue with the macro if there is an error.
3. Step 3 checks to see if the pt variable is filled with a PivotTable object. If the pt variable is set to Nothing, the active cell was not on a PivotTable, thus no PivotTable could be assigned to the variable. If this is the case, we tell the user in a message box, and then we exit the
procedure.
4. If the macro has reached Step 4, it has successfully pointed to a PivotTable. It uses a For Each statement to iterate through each data field. Each time a new pivot field is selected, it alters the Function property to set the calculation used by the field. In this case, we are setting all the data fields in the PivotTable to Sum.
After the name has been changed, we move to the next data field. After all the data fields have been evaluated, the macro ends.
Macro 69: Apply Number Formatting for All Data
Items
A PivotTable does not inherently store number formatting in its pivot cache. Formatting takes up
memory; so in order to be as lean as possible, the pivot cache only contains data. Unfortunately, this results in the need to apply number formatting to every field you add to a PivotTable. This takes from eight to ten clicks of the mouse for every data field you add. When you have PivotTables that contain five or more data fields, you're talking about more than 40 clicks of the mouse!
Ideally, a PivotTable should be able to look back at its source data and adopt the number formatting from the fields there. The macro outlined in this section is designed to do just that. It recognizes the number formatting in the PivotTable's source data and applies the appropriate formatting to each field automatically.
How it works
Before running this code, you want to make sure that
• The source data for your PivotTable is accessible. The macro needs to see it in order to capture the correct number formatting.
• The source data is appropriately formatted. Money fields are formatted as currency, value fields are formatted as numbers, and so on.
This macro uses the PivotTable SourceData property to find the location of the source data. It then loops through each column in the source, capturing the header name and the number format of the first value under each column. After it has that information, the macro determines whether any of the data fields match the evaluated column. If it finds a match, the number formatting is applied to that data field.
Sub Macro69()
‘Step 1: Declare your Variables
Dim pt As PivotTable Dim pf As PivotField Dim SrcRange As Range
Dim strFormat As String Dim strLabel As String Dim i As Integer
‘Step 2: Point to the PivotTable in the activecell
On Error Resume Next
Set pt = ActiveSheet.PivotTables(ActiveCell.PivotTable.Name)
‘Step 3: Exit if active cell is not in a PivotTable
If pt Is Nothing Then
MsgBox “You must place your cursor inside of a PivotTable.” Exit Sub
End If
‘Step 4: Capture the source range Set SrcRange = _ Range(Application.ConvertFormula(pt.SourceData, xlR1C1, xlA1))
‘Step 5: Start looping through the columns in source range
For i = 1 To SrcRange.Columns.Count
‘Step 6: Trap the source column name and number format strLabel = SrcRange.Cells(1, i).Value
strFormat = SrcRange.Cells(2, i).NumberFormat
‘Step 7: Loop through the fields PivotTable data area
For Each pf In pt.DataFields
‘Step 8: Check for match on SourceName then apply format
If pf.SourceName = strLabel Then pf.NumberFormat = strFormat
End If Next pf Next i End Sub
1. Step 1 declares six variables. It uses pt as the memory container for our PivotTable and pf as a memory container for our data fields. The SrcRange variable holds the data range for the source data. The strFormat and strLabel variables are both text string variables used to hold the source column label and number formatting respectively. The i variable serves as a counter, helping us enumerate through the columns of the source data range.
2. The active cell must be inside a PivotTable for this macro to run. The assumption is that when the cursor is inside a particular PivotTable, we want to perform the macro action on that pivot.
Step 2 sets the pt variable to the name of the PivotTable on which the active cell is found. We do this by using the ActiveCell.PivotTable.Name property to get the name of the target pivot.
If the active cell is not inside a PivotTable, an error is thrown. This is why the macro uses the On Error Resume Next statement. This tells Excel to continue with the macro if there is an error.
3. Step 3 checks to see whether the pt variable is filled with a PivotTable object. If the pt variable is set to Nothing, the active cell was not on a PivotTable, thus no PivotTable could be assigned to the variable. If this is the case, we tell the user in a message box, and then we exit the procedure.
4. If the macro reaches Step 4, it has successfully pointed to a PivotTable. We immediately fill our SrcRange object variable with the PivotTable's source data range.
All PivotTables have a SourceData property that points to the address of its source. Unfortunately, the address is stored in the R1C1 reference style — like this: ‘Raw Data'!R3C1:R59470C14. Range objects cannot use the R1C1 style, so we need the address to be converted to ‘Raw Data'!$A$3:$N$59470.
This is a simple enough fix. We simply pass the SourceData property through the Application.ConvertFormula function. This handy function converts ranges to and from the R1C1 reference style.
5. After the range is captured, the macro starts looping through the columns in the source range. In this case, we manage the looping by using the i integer as an index number for the columns in the source range. We start the index number at 1 and end it at the maximum number of rows in
the source range.
6. As the macro loops through the columns in the source range, we capture the column header label and the column format.
We do this with the aid of the Cells item. The Cells item gives us an extremely handy way of selecting ranges through code. It requires only relative row and column positions as parameters. Cells(1,1) translates to row 1, column 1 (or the header row of the first column). Cells(2,
1) translates to row 2, column 1 (or the first value in the first column).
strLabel is filled by the header label taken from row 1 of the column that is selected.
strFormat is filled with the number formatting from row 2 of the column that is selected.
7. At this point, the macro has connected with the PivotTable's source data and captured the first column name and number formatting for that column. Now it starts looping through the data
fields in the PivotTable.
8. Step 8 simply compares each data field to see if its source matches the name in strLabel. If it does, that means the number formatting captured in strFormat belongs to that data field.
9. After all data fields have been evaluated, the macro increments i to the next column in the source range. After all columns have been evaluated, the macro ends.
Macro 70: Sort All Fields in Alphabetical Order
If you frequently add data to your PivotTables, you may notice that new data doesn't automatically fall into the sort order of the existing pivot data. Instead, it gets tacked to the bottom of the existing data. This means that your drop-down lists show all new data at the very bottom, whereas existing data is sorted alphabetically.
How it works
This macro works to reset the sorting on all data fields, ensuring that any new data snaps into place. The idea is to run it each time you refresh your PivotTable. In the code, we enumerate through each data field in the PivotTable, sorting each one as we go.
Sub Macro70()
‘Step 1: Declare your Variables
Dim pt As PivotTable
Dim pf As PivotField
‘Step 2: Point to the PivotTable in the activecell
On Error Resume Next
Set pt = ActiveSheet.PivotTables(ActiveCell.PivotTable.Name)
‘Step 3: Exit if active cell is not in a PivotTable
If pt Is Nothing Then
MsgBox “You must place your cursor inside of a PivotTable.” Exit Sub
End If
‘Step 4: Loop through all pivot fields and sort
For Each pf In pt.PivotFields pf.AutoSort xlAscending, pf.Name Next pf
End Sub
1. Step 1 declares two object variables, using pt as the memory container for the PivotTable and using pf as a memory container for our data fields. This allows the macro to loop through all the data fields in the PivotTable.
2. The active cell must be inside a PivotTable for this macro to run. The assumption is that when the cursor is inside a particular PivotTable, we want to perform the macro action on that pivot.
In Step 2, we set the pt variable to the name of the PivotTable on which the active cell is found. We do this by using the ActiveCell.PivotTable.Name property to get the name of the target pivot.
If the active cell is not inside of a PivotTable, an error is thrown. This is why we use the On
Error Resume Next statement. This tells Excel to continue with the macro if there is an error.
3. Step 3 checks to see whether the pt variable is filled with a PivotTable object. If the pt variable is set to Nothing, the active cell was not on a PivotTable, thus no PivotTable could be assigned to the variable. If this is the case, the macro puts up a message box to notify the user, and then exits the procedure.
4. Finally, we use a For Each statement to iterate through each pivot field. Each time a new pivot field is selected, we use the AutoSort method to reset the automatic sorting rules for the field. In this case, we are sorting all fields in ascending order. After all the data fields have been evaluated, the macro ends.
Macro 71: Apply Custom Sort to Data Items
On occasion, you may need to apply a custom sort to the data items in your PivotTable. For instance, if you work for a company in California, your organization may want the West region to come before the North and South. In these types of situations, neither the standard ascending nor
descending sort order will work.
How it works
You can automate the custom sorting of your fields by using the Position property of the PivotItems object. With the Position property, you can assign a position number that specifies the order in which you would like to see each pivot item.
In this example code, we first point to the Region pivot field in the Pvt1 PivotTable. Then we list each item along with the position number indicating the customer sort order we need.
Sub Macro71()
With Sheets(“Sheet1”).PivotTables(“Pvt1”).PivotFields(“Region “)
.PivotItems(“West”).Position = 1
.PivotItems(“North”).Position = 2
.PivotItems(“South”).Position = 3
End With
End Sub
The other solution is to set up a custom sort list. A custom sort list is a defined list that is stored in your instance of Excel. To create a custom sort list, go to the Excel Options dialog box and choose Edit
Custom Lists. Here, you can type West, North, and South in the List Entries box and click Add. After setting up a custom list, Excel realizes that the Region data items in your PivotTable match a custom list and sorts the field to match your custom list.
As brilliant as this option is, custom lists do not travel with your workbook, so a macro helps in cases where it's impractical to expect your clients or team members to set up their own custom sort lists.
Macro 72: Apply PivotTable Restrictions
We often send PivotTables to clients, coworkers, managers, and other groups of people. In some cases, we'd like to restrict the types of actions our users can take on the PivotTable reports we send them. The macro outlined in this section demonstrates some of the protection settings available via VBA.
How it works
The PivotTable object exposes several properties that allow you (the developer) to restrict different features and components of a PivotTable:
• EnableWizard: Setting this property to False disables the PivotTable Tools context menu that normally activates when clicking inside of a PivotTable. In Excel 2003, this setting disables the PivotTable and Pivot Chart Wizard.
• EnableDrilldown: Setting this property to False prevents users from getting to detailed data by double-clicking a data field.
• EnableFieldList: Setting this property to False prevents users from activating the field list or moving pivot fields around.
• EnableFieldDialog: Setting this property to False disables the users' ability to alter the pivot field via the Value Field Settings dialog box.
• PivotCache.EnableRefresh: Setting this property to False disables the ability to refresh the
PivotTable.
You can set any or all of these properties independently to either True or False. In this macro, we apply all of the restrictions to the target PivotTable.
Sub Macro72()
‘Step 1: Declare your Variables
Dim pt As PivotTable
‘Step 2: Point to the PivotTable in the activecell
On Error Resume Next
Set pt = ActiveSheet.PivotTables(ActiveCell.PivotTable.Name)
‘Step 3: Exit if active cell is not in a PivotTable
If pt Is Nothing Then
MsgBox “You must place your cursor inside of a PivotTable.” Exit Sub
End If
‘Step 4: Apply PivotTable Restrictions
With pt
.EnableWizard = False
.EnableDrilldown = False
.EnableFieldList = False
.EnableFieldDialog = False
.PivotCache.EnableRefresh = False
End With
End Sub
1. Step 1 declares the pt PivotTable object variable that serves as the memory container for our
PivotTable.
2. Step 2 sets the pt variable to the name of the PivotTable on which the active cell is found.
We do this by using the ActiveCell.PivotTable.Name property to get the name of the target pivot.
3. Step 3 checks to see if the pt variable is filled with a PivotTable object. If the pt variable isset to Nothing, the active cell was not on a PivotTable, thus no PivotTable could be assigned to the variable. If this is the case, the macro notifies the user in a message box, and then we exit the procedure.
4. In the last step of the macro, we are applying all PivotTable restrictions.
Macro 73: Apply Pivot Field Restrictions
Like PivotTable restrictions, pivot field restrictions enable us to restrict the types of actions our users can take on the pivot fields in a PivotTable. The macro outlined in this section demonstrates some of the protection settings available via VBA.
How it works
The PivotField object exposes several properties that allow you (the developer) to restrict different features and components of a PivotTable.
• DragToPage: Setting this property to False prevents the users from dragging any pivot field into the Report Filter area of the PivotTable.
• DragToRow: Setting this property to False prevents the users from dragging any pivot field into the Row area of the PivotTable.
• DragToColumn: Setting this property to False prevents the users from dragging any pivot field into the Column area of the PivotTable.
• DragToData: Setting this property to False prevents the users from dragging any pivot field into the Data area of the PivotTable.
• DragToHide: Setting this property to False prevents the users from dragging pivot fields off the PivotTable. It also prevents the use of the right-click menu to hide or remove pivot fields.
• EnableItemSelection: Setting this property to False disables the drop-down lists on each pivot field.
You can set any or all of these properties independently to either True or False. In this macro, we apply all of the restrictions to the target PivotTable.
Sub Macro73()
‘Step 1: Declare your Variables
Dim pt As PivotTable
Dim pf As PivotField
‘Step 2: Point to the PivotTable in the activecell
On Error Resume Next
Set pt = ActiveSheet.PivotTables(ActiveCell.PivotTable.Name)
‘Step 3: Exit if active cell is not in a PivotTable
If pt Is Nothing Then
MsgBox “You must place your cursor inside of a PivotTable.” Exit Sub
End If
‘Step 4: Apply Pivot Field Restrictions For Each pf In pt.PivotFields pf.EnableItemSelection = False pf.DragToPage = False
pf.DragToRow = False pf.DragToColumn = False pf.DragToData = False pf.DragToHide = False Next pf
End Sub
1. Step 1 declares two object variables, using pt as the memory container for our PivotTable and pf as a memory container for our pivot fields. This allows us to loop through all the pivot fields in the PivotTable.
2. Set the pt variable to the name of the PivotTable on which the active cell is found. We do this by using the ActiveCell.PivotTable.Name property to get the name of the target pivot.
3. Step 3 checks to see whether the pt variable is filled with a PivotTable object. If the pt variable is set to Nothing, the active cell was not on a PivotTable, thus no PivotTable could be assigned to the variable. If this is the case, the macro notifies the user via a message box, and then exits the procedure.
4. Step 4 of the macro uses a For Each statement to iterate through each pivot field. Each time a new pivot field is selected, we apply all of our pivot field restrictions.
Macro 74: Automatically Delete Pivot Table Drill- Down Sheets
One of the coolest features of a PivotTable is that it gives you the ability to double-click on a number and drill into the details. The details are output to a new sheet that you can review. In most cases, you don't want to keep these sheets. In fact, they often become a nuisance, forcing you to
take the time to clean them up by deleting them.
This is especially a problem when you distribute PivotTable reports to users who frequently drill into details. There is no guarantee they will remember to clean up the drill-down sheets. Although these sheets probably won't cause issues, they can clutter up the workbook.
Here is a technique you can implement to have your workbook automatically remove these drill- down sheets.
How it works
The basic premise of this macro is actually very simple. When the user clicks for details, outputting a drill-down sheet, the macro simply renames the output sheet so that the first ten characters are PivotDrill. Then before the workbook closes, the macro finds any sheet that starts with PivotDrill and deletes it.
The implementation does get a bit tricky because you essentially have to have two pieces of code. One piece goes in the Worksheet_BeforeDoubleClick event, whereas the other piece goes into the Workbook_BeforeClose event.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
‘Step 1: Declare your Variables
Dim pt As String
‘Step 2: Exit if Double-Click did not occur on a PivotTable
On Error Resume Next
If IsEmpty(Target) And ActiveCell.PivotField.Name <> “” Then
Cancel = True
Exit Sub
End If
‘Step 3: Set the PivotTable object
pt = ActiveSheet.Range(ActiveCell.Address).PivotTable
‘Step 4: If Drilldowns are Enabled, Drill down
If ActiveSheet.PivotTables(pt).EnableDrilldown Then
Selection.ShowDetail = True
ActiveSheet.Name = _
Replace(ActiveSheet.Name, “Sheet”, “PivotDrill”) End If
End Sub
1. Step 1 starts by creating the pt object variable for our PivotTable.
2. Step 2 checks the double-clicked cell. If the cell is not associated with any PivotTable, we cancel the double-click event.
3. If a PivotTable is indeed associated with a cell, Step 3 fills the pt variable with the
PivotTable.
4. Finally, Step 4 checks the EnableDrillDown property. If it is enabled, we trigger the
ShowDetail method. This outputs the drill-down details to a new worksheet.
The macro follows the output and renames the output sheet so that the first ten characters are PivotDrill. We do this by using the Replace function. The Replace function replaces certain text in an expression with other text. In this case, we are replacing the word Sheet with PivotDrill: Replace(ActiveSheet.Name, “Sheet”, “PivotDrill”).
Sheet1 becomes PivotDrill1; Sheet12 becomes PivotDrill12, and so on.
Next, the macro sets up the Worksheet_BeforeDoubleClick event. As the name suggests, this code runs when the workbook closes.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
‘Step 5: Declare your Variables
Dim ws As Worksheet
‘Step 6: Loop through worksheets
For Each ws In ThisWorkbook.Worksheets
‘Step 7: Delete any sheet that starts with PivotDrill If Left(ws.Name, 10) = “PivotDrill” Then Application.DisplayAlerts = False
ws.Delete Application.DisplayAlerts = True End If
Next ws
End Sub
5. Step 5 declares the ws Worksheet variable. This is used to hold worksheet objects as we loop through the workbook.
6. Step 6 starts the looping, telling Excel we want to evaluate all worksheets in this workbook.
7. In the last step, we evaluate the name of the sheet that has focus in the loop. If the left ten characters of that sheet name are PivotDrill, we delete the worksheet. After all of the sheets have been evaluated, all drill-down sheets have been cleaned up and the macro ends.
To implement this macro, you need to copy and paste it into the Workbook_BeforeClose event
code window. Placing the macro here allows it to run each time you try to close the workbook.
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. In the Project window, find your project/workbook name and click the plus sign next to it in order to see all the sheets.
3. Click ThisWorkbook.
4. Select the BeforeClose event in the Event drop-down list (see Figure 6-5).
5. Type or paste the code.
Macro 75: Print Pivot Table for Each Report FilterItem
Pivot tables provide an excellent mechanism to parse large data sets into printable files. You can build a PivotTable report, complete with aggregations and analysis, and then place a field (like Region) into the report filter. With the report filter, you can select each data item one at a time, and then print the PivotTable report.
The macro in this section demonstrates how to automatically iterate through all the values in a report filter and print.
How it works
In the Excel object model, the Report Filter drop-down list is known as the PageField. To print a
PivotTable for each data item in a report filter, we need to loop through the PivotItems
collection of the PageField object. As we loop, we dynamically change the selection in the report filter, and then use the ActiveSheet.PrintOut method to print the target range.
Sub Macro75()
‘Step 1: Declare your Variables
Dim pt As PivotTable Dim pf As PivotField Dim pi As PivotItem
‘Step 2: Point to the PivotTable in the activecell
On Error Resume Next
Set pt = ActiveSheet.PivotTables(ActiveCell.PivotTable.Name)
‘Step 3: Exit if active cell is not in a PivotTable
If pt Is Nothing Then
MsgBox “You must place your cursor inside of a PivotTable.” Exit Sub
End If
‘Step 4: Exit if more than one page field
If pt.PageFields.Count > 1 Then
MsgBox “Too many Report Filter Fields. Limit 1.” Exit Sub
End If
‘Step 5: Start looping through the page field and its pivot items
For Each pf In pt.PageFields
For Each pi In pf.PivotItems
‘Step 6: Change the selection in the report filter pt.PivotFields(pf.Name).CurrentPage = pi.Name
‘Step 7: Set Print Area and print ActiveSheet.PageSetup.PrintArea = pt.TableRange2.Address ActiveSheet.PrintOut Copies:=1
‘Step 8: Get the next page field item
Next pi Next pf End Sub
1. For this macro, Step 1 declares three variables: pt as the memory container for our
PivotTable, pf as a memory container for our page fields, and pi to hold each pivot item as weloop through the PageField object.
2. The active cell must be inside a PivotTable for this macro to run. The assumption is that when the cursor is inside a particular PivotTable, we want to perform the macro action on that pivot.
Step 2 sets the pt variable to the name of the PivotTable on which the active cell is found. We do this by using the ActiveCell.PivotTable.Name property to get the name of the target pivot.
If the active cell is not inside of a PivotTable, the macro throws an error. This is why we use the On Error Resume Next statement. This tells Excel to continue with the macro if there is an error.
3. Step 3 checks to see whether the pt variable is filled with a PivotTable object. If the pt variable is set to Nothing, the active cell was not on a PivotTable, thus no PivotTable could be assigned to the variable. If this is the case, the user is notified via a message box, and then we exit the procedure.
4. Step 4 determines whether there is more than one report filter field. (If the count of PageFields is greater than one, there is more than one report filter.) We do this check for a simple reason: We want to avoid printing reports for filters that just happen to be there. Without this check, you might wind up printing hundreds of pages. The macro stops with a message box if the field count is greater than 1.
You can remove this limitation should you need to simply by deleting or commenting out Step 4 in the macro.
5. Step 5 starts two loops. The outer loop tells Excel to iterate through all the report filters. The inner loop tells Excel to loop through all the pivot items in the report filter that currently has focus.
6. For each pivot item, the macro captures the item name and uses it to change the report filter selection. This effectively alters the PivotTable report to match the pivot item.
7. Step 7 prints the active sheet, and then moves to the next pivot item. After we have looped through all pivot items in the report filter, the macro moves to the next PageField. After all PageFields have been evaluated, the macro ends.
Macro 76: Create New Workbook for Each Report
Filter Item
Pivot tables provide an excellent mechanism to parse large data sets into separate files. You can build a PivotTable report, complete with aggregations and analysis, and then place a field (like Region) into the report filter. With the report filter, you can select each data item one at a time, and then export the PivotTable data to a new workbook.
The macro in this section demonstrates how to automatically iterate through all the values in a report filter and export to a new workbook.
How it works
In the Excel object model, the Report Filter drop-down list is known as the PageField. To print a PivotTable for each data item in a report filter, the macro needs to loop through the PivotItems collection of the PageField object. As the macro loops, it must dynamically change the selection in the report filter, and then export the PivotTable report to a new workbook.
Sub Macro76()
‘Step 1: Declare your Variables
Dim pt As PivotTable Dim pf As PivotField Dim pi As PivotItem
‘Step 2: Point to the PivotTable in the activecell
On Error Resume Next
Set pt = ActiveSheet.PivotTables(ActiveCell.PivotTable.Name)
‘Step 3: Exit if active cell is not in a PivotTable
If pt Is Nothing Then
MsgBox “You must place your cursor inside of a PivotTable.” Exit Sub
End If
‘Step 4: Exit if more than one page field
If pt.PageFields.Count > 1 Then
MsgBox “Too many Report Filter Fields. Limit 1.” Exit Sub
End If
‘Step 5: Start looping through the page field and its pivot items
For Each pf In pt.PageFields
For Each pi In pf.PivotItems
‘Step 6: Change the selection in the report filter pt.PivotFields(pf.Name).CurrentPage = pi.Name
‘Step 7: Copy the data area to a new workbook pt.TableRange1.Copy Workbooks.Add.Worksheets(1).Paste Application.DisplayAlerts = False ActiveWorkbook.SaveAs _
Filename:=”C:\Temp\” & pi.Name & “.xlsx” ActiveWorkbook.Close Application.DisplayAlerts = True
‘Step 8: Get the next page field item
Next pi
Next pf
End Sub
1. Step 1 declares three variables, pt as the memory container for our PivotTable, pf as a memory container for our page fields, and pi to hold each pivot item as the macro loops through the PageField object.
2. The active cell must be inside a PivotTable for this macro to run. The assumption is that when the cursor is inside a particular PivotTable, we will want to perform the macro action on that pivot.
Step 2 sets the pt variable to the name of the PivotTable on which the active cell is found. The macro does this by using the ActiveCell.PivotTable.Name property to get the name of the target pivot.
If the active cell is not inside of a PivotTable, an error is thrown. This is why we use the On
Error Resume Next statement. This tells Excel to continue with the macro if there is an error.
3. Step 3 checks to see whether the pt variable is filled with a PivotTable object. If the pt variable is set to Nothing, the active cell was not on a PivotTable, thus no PivotTable could be assigned to the variable. If this is the case, the macro notifies the user via a message box, and then we exit the procedure.
4. Step 4 determines whether there is more than one report filter field. If the count of PageFields is greater than one, there is more than one report filter. The reason we do this check is simple. We want to avoid printing reports for filters that just happen to be there.
Without this check, you might wind up printing hundreds of pages. The macro stops and displays a message box if the field count is greater than 1.
You can remove the one report filter limitation if you need to simply by deleting or commenting out Step 4 in the macro.
5. Step 5 starts two loops. The outer loop tells Excel to iterate through all the report filters. The inner loop tells Excel to loop through all the pivot items in the report filter that currently has focus.
6. For each pivot item, Step 6 captures the item name and uses it to change the report filter selection. This effectively alters the PivotTable report to match the pivot item.
7. Step 7 copies TableRange1 of the PivotTable object. TableRange1 is a built-in range object that points to the range of the main data area for the PivotTable. We then paste the data to a new workbook and save it. Note that you need to change the save path to one that works in your environment.
8. Step 8 moves to the next pivot item. After the macro has looped through all pivot items in the report filter, the macro moves to the next PageField. After all PageFields have been evaluated, the macro ends.
Macro 77: Transpose Entire Data Range with a
PivotTable
You may often encounter matrix-style data tables like the one shown in Figure 6-6. The problem is that the month headings are spread across the top of the table, pulling double duty as column labels and actual data values. In a PivotTable, this format would force you to manage and maintain 12 fields, each representing a different month.
Ideally, the data would be formatted in a more tabular format, as shown in Figure 6-7.
Multiple consolidation ranges can only output three base fields: Row, Column, and Value. The Row field is always made up of the first column in your data source. The Column field is made up of all the column headers after the first column in your data source. The Value field is made up of the values in your data source.
Because of this, you can only have one dimension column. To understand this, take a look at Figure 6-6. Note that the first column is essentially a concatenated column consisting of two data dimensions: Market and Category. This is because a multiple consolidation range pivot table can handle only one dimension field.
How it works
You can transpose a dataset with a multiple consolidation range PivotTable. The manual steps to do so are
1. Press Alt+D+P to call up the Excel 2003 PivotTable Wizard.
2. Click the option for Multiple Consolidation Ranges, and then click Next.
3. Select the I Will Create the Page Fields option, and then click Next.
4. Define the range you are working with and click Finish to create the PivotTable.
5. Double-click on the intersection of the Grand Total row and column.
This macro duplicates the steps above, allowing you to transpose your data set in a fraction of the time.
Sub Macro77()
‘Step 1: Declare your Variables
Dim SourceRange As Range
Dim GrandRowRange As Range
Dim GrandColumnRange As Range
‘Step 2: Define your data source range
Set SourceRange = Sheets(“Sheet1”).Range(“A4:M87”)
‘Step 3: Build Multiple Consolidation Range Pivot Table ActiveWorkbook.PivotCaches.Create(SourceType:=xlConsolidation, _ SourceData:=SourceRange.Address(ReferenceStyle:=xlR1C1), _ Version:=xlPivotTableVersion14).CreatePivotTable _ TableDestination:=””, _
TableName:=”Pvt2”, _ DefaultVersion:=xlPivotTableVersion14
‘Step 4: Find the Column and Row Grand Totals ActiveSheet.PivotTables(1).PivotSelect “'Row Grand Total'” Set GrandRowRange = Range(Selection.Address)
ActiveSheet.PivotTables(1).PivotSelect “'Column Grand Total'” Set GrandColumnRange = Range(Selection.Address)
‘Step 5: Drill into the intersection of Row and Column Intersect(GrandRowRange, GrandColumnRange).ShowDetail = True End Sub
Part VII: Manipulating Charts with Macros
Charts are the topic of this Part. We show you how to resize them, label them, format them, and more.
In This Part
Macro 78 Resize All Charts on a Worksheet
Macro 79 Align a Chart to a Specific Range
Macro 80 Create a Set of Disconnected Charts
Macro 81 Print All Charts on a Worksheet
Macro 82 Label First and Last Chart Points
Macro 83 Color Chart Series to Match Source Cell Colors
Macro 84 Color Chart Data Points to Match Source Cell Colors
For those of us tasked with building dashboards and reports, charts are a daily part of our work life. However, few of us have had the inclination to automate any aspect of our chart work with macros. Many of us would say that there are too many scope changes and iterative adjustments in the normal reporting environment to automate charting.
On many levels, that is true, but some aspects of our work lend themselves to a bit of automation. In this Part, we explore a handful of charting macros that can help you save time and become a bit more efficient.
The code for this Part can be found on this book's companion website. See this book's Introduction for more on the companion website.
Macro 78: Resize All Charts on a Worksheet
When building a dashboard, you often want to achieve some level of symmetry and balance. This sometimes requires some level of chart size standardization. The macro in this section gives you an easy way to set a standard height and width for all your charts at once.
How it works
All charts belong to the ChartObjects collection. To take an action on all charts at one time, you simply iterate through all the charts in ChartObjects. Each chart in the ChartObjects collection has an index number that you can use to bring it into focus. For example, ChartObjects(1) points to the first chart in the sheet.
In this macro, we use this concept to loop through the charts on the active sheet with a simple counter. Each time a new chart is brought into focus, we change its height and width to the size we've defined.
Sub Macro78()
‘Step 1: Declare your variables
Dim i As Integer
‘Step 2: Start Looping through all the charts
For i = 1 To ActiveSheet.ChartObjects.Count
‘Step 3: Activate each chart and size
With ActiveSheet.ChartObjects(i)
.Width = 300
.Height = 200
End With
‘Step 4: Increment to move to next chart
Next i
End Sub
1. Step 1 declares an integer object that is used as a looping mechanism. We call the variable i.
2. Step 2 starts the looping by setting i to count from 1 to the maximum number of charts in the
ChartObjects collection on the active sheet. When the code starts, i initiates with the number
1. As we loop, the variable increments up one number until it reaches a number equal to the maximum number of charts on the sheet.
3. Step 3 passes i to the ChartObjects collection as the index number. This brings a chart into focus. We then set the width and height of the chart to the number we specify here in the code. You can change these numbers to suit your needs.
4. In Step 4, the macro loops back around to increment i up one number and get the next chart. After all charts have been evaluated, the macro ends.
Macro 79: Align a Chart to a Specific Range
Along with adjusting the size of our charts, many of us spend a good bit of time positioning them so that they align nicely in our dashboards. This macro helps easily snap your charts to defined ranges, getting perfect positioning every time.
How it works
Every chart has four properties that dictate its size and position. These properties are Width, Height, Top, and Left. Interestingly enough, every Range object has these same properties. So if you set a chart's Width, Height, Top, and Left properties to match that of a particular range, the chart essentially snaps to that range.
The idea is that after you have decided how you want your dashboard to be laid out, you take note of the ranges that encompass each area of your dashboard. You then use those ranges in this macro to snap each chart to the appropriate range. In this example, we adjust four charts so that their Width, Height, Top, and Left properties match a given range.
Note that we are identifying each chart with a name. Charts are, by default, named “Chart” and the order number they were added (Chart 1, Chart 2, Chart 3, and so on). You can see what each of your charts is named by clicking any chart, and then going up to the Ribbon and selecting Format⇒Selection Pane. This activates a task pane (seen here in Figure 7-1) that lists all the objects on your sheet with their names.
Figure 7-1: The Selection Pane allows you to see all of your chart objects and their respective names.
You can use it to get the appropriate chart names for your version of this macro.
Sub Macro79()
Dim SnapRange As Range
Set SnapRange = ActiveSheet.Range(“B6:G19”) With ActiveSheet.ChartObjects(“Chart 1”)
.Height = SnapRange.Height
.Width = SnapRange.Width
.Top = SnapRange.Top
.Left = SnapRange.Left
End With
Set SnapRange = ActiveSheet.Range(“B21:G34”) With ActiveSheet.ChartObjects(“Chart 2”)
.Height = SnapRange.Height
.Width = SnapRange.Width
.Top = SnapRange.Top
.Left = SnapRange.Left
End With
Set SnapRange = ActiveSheet.Range(“I6:Q19”) With ActiveSheet.ChartObjects(“Chart 3”)
.Height = SnapRange.Height
.Width = SnapRange.Width
.Top = SnapRange.Top
.Left = SnapRange.Left
End With
Set SnapRange = ActiveSheet.Range(“I21:Q34”) With ActiveSheet.ChartObjects(“Chart 4”)
.Height = SnapRange.Height
.Width = SnapRange.Width
.Top = SnapRange.Top
.Left = SnapRange.Left
End With
End Sub
Macro 80: Create a Set of Disconnected Charts
When you need to copy charts from a workbook and paste them elsewhere (another workbook, PowerPoint, Outlook, and so on), it's often best to disconnect them from the original source data. This way, you won't get any of the annoying missing link messages that Excel throws. This macro copies all of the charts in the active sheet, pastes them into a new workbook, and disconnects them from the original source data.
How it works
This macro uses the ShapeRange.Group method to group all the charts on the active sheet into one shape. This is similar to what you would do if you were to group a set of shapes manually. After
the charts are grouped, we copy the group and paste it to a new workbook. We then use the BreakLink method to remove references to the original source data. When we do this, Excel hard- codes the chart data into array formulas.
Sub Macro80()
‘Step 1: Declare your variables
Dim wbLinks As Variant
‘Step 2: Group the charts, copy the group, and then ungroup
With ActiveSheet.ChartObjects.ShapeRange.Group
.Copy
.Ungroup
End With
‘Step 3: Paste into a new workbook and ungroup Workbooks.Add.Sheets(1).Paste Selection.ShapeRange.Ungroup
‘Step 4: Break the links
wbLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks) ActiveWorkbook.BreakLink Name:=wbLinks(1), _ Type:=xlLinkTypeExcelLinks
End Sub
1. Step 1 declares the wbLinks variant variable. The macro uses this in Step 4 to pass the link source when breaking the links.
2. Step 2 uses ChartObjects.ShapeRange.Group to group all the charts into a single shape. The macro then copies the group to the clipboard. After the group is copied, the macro ungroups the charts.
3. Step 3 creates a new workbook and pastes the copied group to Sheet 1. After the group has been pasted, we can ungroup so that each chart is separate again. Note that the newly created
workbook is now the active object, so all references to ActiveWorkbook point back to this workbook.
4. Step 4 captures the link source in the wbLinks variable. The macro then tells Excel to break the links.
Note that because this technique converts the chart source links to an array formula, this technique can fail if your chart contains too many data points. How many is too many? It can be different for every PC because it's limited by memory.
.
Macro 81: Print All Charts on a Worksheet
To print a chart, you can click any embedded chart in your worksheet and then click Print. This prints the chart on its own sheet without any of the other data on the sheet. This sounds easy enough, but it can become a chore if you've got to do this for many charts. This macro makes short work of this task.
How it works
All charts belong to the ChartObjects collection. To take an action on all charts at one time, you simply iterate through all the charts in ChartObjects. Each chart in the ChartObjects collection has an index number that you can use to bring it into focus. For example, ChartObjects(1) points to the first chart in the sheet.
In this macro, we use this concept to loop through the charts on the active sheet with a simple counter. Each time a new chart is brought into focus, print it.
Sub Macro81()
‘Step 1: Declare your variables
Dim ChartList As Integer
Dim i As Integer
‘Step 2: Start Looping through all the charts
For i = 1 To ActiveSheet.ChartObjects.Count
‘Step 3: Activate each chart and print
ActiveSheet.ChartObjects(i).Activate
ActiveChart.PageSetup.Orientation = xlLandscape
ActiveChart.PrintOut Copies:=1
‘Step 4: Increment to move to next chart
Next i
End Sub
1. Step 1 declares an integer object that is used as a looping mechanism. We call the variable i.
2. Step 2 starts the looping by setting i to count from 1 to the maximum number of charts in the
ChartObjects collection on the active sheet. When the code starts, i initiates with the number
1. As we loop, the variable increments up one number until it reaches a number equal to the maximum number of charts on the sheet.
3. Step 3 passes i to the ChartObjects collection as the index number. This brings a chart into focus. We then use the ActiveChart.Printout method to trigger the print. Note that you can adjust the Orientation property to either xlLandscape or xlPortrait depending on what you need.
4. Step 4 loops back around to increment i up one number and get the next chart. After all charts have been evaluated, the macro ends.
Macro 82: Label First and Last Chart Points
One of the best practices for dashboard building is to avoid overwhelming your customers with too much data at one time — especially in a chart, where they can lose sight of the primary message if focusing on inconsequential data.
One of the common ways dashboard designers help focus the message of a chart is to limit the data labels to only the key points — typically, the first and last data points.
That being said, it is a bit arduous to continuously adjust labels every time data is added or when a new chart is needed. The macro outlined in this section automates the adding of labels to the first and last data points.
How it works
All charts have a SeriesCollection object that holds the various data series. This macro loops through all the series, bringing each one into focus one at a time. With the series in focus, we can
use any of its many properties to manipulate it. Here, we are activating the data labels for the first and last data point in the series.
Sub Macro82()
‘Step 1: Declare your variables
Dim oChart As Chart
Dim MySeries As Series
‘Step 2: Point to the active chart
On Error Resume Next
Set oChart = ActiveChart
‘Step 3: Exit no chart has been selected
If oChart Is Nothing Then
MsgBox “You select a chart first.” Exit Sub
End If
‘Step 4: Loop through the chart series
For Each MySeries In oChart.SeriesCollection
‘Step 5: Clear ExistingData Labels
MySeries.ApplyDataLabels (xlDataLabelsShowNone)
‘Step 6: Add labels to the first and last data point MySeries.Points(1).ApplyDataLabels MySeries.Points(MySeries.Points.Count).ApplyDataLabels MySeries.DataLabels.Font.Bold = True
‘Step 7: Move to the next series
Next MySeries
End Sub
1. Step 1 declares two variables. We use oChart as the memory container for our chart. We use
MySeries as a memory container for each series in our chart.
2. This macro is designed so that we infer the target chart based on the chart selection. That is to say, a chart must be selected for this macro to run. The assumption is that we want to perform
the macro action on the chart we clicked on.
Step 2 sets the oChart variable to the ActiveChart. If a chart is not selected, an error is thrown. This is why we use the On Error Resume Next statement. This tells Excel to continue with the macro if there is an error.
3. Step 3 checks to see if the oChart variable is filled with a chart object. If the oChart variable is set to Nothing, no chart was selected before running the macro. If this is the case, we tell the user in a message box, and then we exit the procedure.
4. Step 4 uses the For…Each statement to start looping through the series in the active charts
SeriesCollection.
5. If data labels already exist, we need to clear them out. We can do this by using
xlDataLabelsShowNone.
6. Each data series has a Points collection, which holds all the data points for the chart. Like most collections in the Excel object model, data points have index numbers.
Step 6 of the macro uses index numbers to get to the first and last data points. The first data point is easy; we capture it by using MySeries.Points(1). After we have it in focus, we can
use the ApplyDataLabels method to turn on data labels for that one point.
The last data label is a bit trickier. We use MySeries.Points.Count to get the maximum number
of data points in the series. That is the index number of the last data point. We place the last data point in focus, and then we apply labels to it.
Finally, we adjust the formatting on the data labels so they have bold font.
7. Step 7 loops back around to get the next series. After we have gone through all the data series in the chart, the macro ends.
The best place to store this macro is in your Personal Macro Workbook. This way, 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.
If you don't see personal.xlb in your project window, it 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, select the Personal Macro Workbook option in the Record Macro dialog box before you start recording. This option is in the Store Macro In drop-down list box. Simply record a couple of cell clicks and then stop recording. You can discard the recorded macro and replace it with this one.
Macro 83: Color Chart Series to Match Source
Cell Colors
When you create a dashboard, you may have specific color schemes for various types of data. For example, you may want the North region to always appear in a certain color, or you may want certain products to have a trademark color. This gives your dashboards a familiarity and consistency that makes it easier for your audience to consume.
The macro in this section allows the series in your charts to automatically adopt colors in their source range. The idea is that you can color code the cells in the source range, and then fire this macro to force the chart to apply the same colors to each respective chart series. Although it's in black and white
his macro cannot capture colors that have been applied via conditional formatting or table color
banding. This is because conditional format coloring and table color banding are not applied directly to the cell. They are applied to objects that are separate but sit on top of the cells.
How it works
All charts have a SeriesCollection object that holds the various data series. In this macro, we loop through all the series, bringing each one into focus one at a time. With the series in focus, we can use any of its many properties to manipulate it.
In this case, we are setting the color to the color of the source range. We identify the source range for each series by evaluating its series formula. The series formula contains the range address of the source data. Passing that address to a range object, we can capture the exact color of cells, and then use that to color the series.
Sub Macro83()
‘Step 1: Declare your variables
Dim oChart As Chart
Dim MySeries As Series
Dim FormulaSplit As Variant
Dim SourceRangeColor As Long
‘Step 2: Point to the active chart
On Error Resume Next
Set oChart = ActiveChart
‘Step 3: Exit no chart has been selected
If oChart Is Nothing Then
MsgBox “You must select a chart first.” Exit Sub
End If
‘Step 4: Loop through the chart series
For Each MySeries In oChart.SeriesCollection
‘Step 5: Get Source Data Range for the target series
FormulaSplit = Split(MySeries.Formula, “,”)(2)
‘Step 6: Capture the color in the first cell
SourceRangeColor = Range(FormulaSplit).Item(1).Interior.Color
‘Step 7: Apply Coloring On Error Resume Next MySeries.Format.Line.ForeColor.RGB = SourceRangeColor MySeries.Format.Line.BackColor.RGB = SourceRangeColor MySeries.Format.Fill.ForeColor.RGB = SourceRangeColor If Not MySeries.MarkerStyle = xlMarkerStyleNone Then MySeries.MarkerBackgroundColor = SourceRangeColor MySeries.MarkerForegroundColor = SourceRangeColor
End If
‘Step 8: Move to the next series
Next MySeries
End Sub
1. Step 1 declares four variables. We use oChart as the memory container for our chart, MySeries as a memory container for each series in our chart, FormulaSplit to capture and store the source data range, and SourceRangeColor to capture and store the color index for the source range.
2. This macro is designed so that we infer the target chart based on the chart selection. In other words, a chart must be selected for this macro to run. The assumption is that we will want to perform the macro action on the chart we clicked on.
In Step 2, we set the oChart variable to the ActiveChart. If a chart is not selected, an error is thrown. This is why we use the On Error Resume Next statement. This tells Excel to continue with the macro if there is an error.
3. Step 3 checks to see whether the oChart variable is filled with a chart object. If the oChart variable is set to Nothing, no chart was selected before running the macro. If this is the case, we tell the user in a message box, and then we exit the procedure.
4. Step 4 uses the For…Each statement to start looping through the series in the active charts
SeriesCollection.
5. Every chart series has a series formula. The series formula contains references back to the spreadsheet, pointing to the cells used to create it. A typical series formula looks something like this:
=SERIES(Sheet1!$F$6,Sheet1!$D$7:$D$10,Sheet1!$F$7:$F$10,2)
Note that there are three distinct ranges in the formula. The first range points to the series name,
the second range points to the series data labels, and the third range points to the series data values.
Step 5 uses the Split function to parse this formula in order to extract out the range for the series data values.
6. Step 6 captures the color index of the first cell (item) in the source data range. We assume that the first cell will be formatted the same as the rest of the range.
7. After we have the color index, we can apply the color to the various series properties.
8. In the last step, we loop back around to get the next series. After we have gone through all the data series in the chart, the macro ends.
If you don't see personal.xlb in your project window, it means it 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, select the Personal Macro Workbook option in the Record Macro dialog box before you start recording. This option is in the Store Macro In drop-down list box. Simply record a couple of cell clicks and then stop recording. You can discard the recorded macro and replace it with this one.
Macro 84: Color Chart Data Points to Match
Source Cell Colors
In the previous macro, we force each chart series to apply the same colors as their respective source data ranges. This macro works the same way, but with data points. You would use this macro if you wanted to force a pie chart to adopt the color of each data point's source range.
This macro cannot capture colors that have been applied via conditional formatting or table color banding. This is because conditional format coloring and table color banding are not applied directly to the cell. They are applied to objects that are separate but sit on top of the cells.
In this case, we are setting the color to the color of the source range. We identify the source range for each series by evaluating its series formula. The series formula contains the range address of the source data. Passing that address to a range object, we can capture the exact color of cells, and then use that to color the series.
Sub Macro84()
‘Step 1: Declare your variables
Dim oChart As Chart
Dim MySeries As Series
Dim i As Integer
Dim dValues As Variant
Dim FormulaSplit As String
‘Step 2: Point to the active chart
On Error Resume Next
Set oChart = ActiveChart
‘Step 3: Exit no chart has been selected
If oChart Is Nothing Then
MsgBox “You must select a chart first.” Exit Sub
End If
‘Step 4: Loop through the chart series
For Each MySeries In oChart.SeriesCollection
‘Step 5: Get Source Data Range for the target series
FormulaSplit = Split(MySeries.Formula, “,”)(2)
‘Step 6: Capture Series Values dValues = MySeries.Values
‘Step 7: Loop through series values and set color For i = 1 To UBound(dValues) MySeries.Points(i).Interior.Color = _ Range(FormulaSplit).Cells(i).Interior.Color
Next i
‘Step 8: Move to the next series
Next MySeries
End Sub
1. Step 1 declares five variables. We use oChart as the memory container for our chart, MySeries as a memory container for each series in our chart, dValues in conjunction with i to loop through the values in the series, and FormulaSplit to capture and store the source data range.
2. This macro is designed so that we infer the target chart based on the chart selection. A chart must be selected for this macro to run. The assumption is that we want to perform the macro action on the chart we clicked on.
In Step 2, we set the oChart variable to the ActiveChart. If a chart is not selected, an error is thrown. This is why we use the On Error Resume Next statement. This tells Excel to continue with the macro if there is an error.
3. In Step 3, we check to see whether the oChart variable is filled with a chart object. If the
oChart variable is set to Nothing, no chart was selected before running the macro. If this is the
case, we tell the user in a message box, and then we exit the procedure.
4. Step 4 uses the For…Each statement to start looping through the series in the active charts
SeriesCollection.
5. Every chart series has a series formula. The series formula contains references back to the spreadsheet, pointing to the cells used to create it. A typical series formula looks something like this:
=SERIES(Sheet1!$F$6,Sheet1!$D$7:$D$10,Sheet1!$F$7:$F$10,2)
Note that there are three distinct ranges in the formula. The first range points to the series name,
the second range points to the series data labels, and the third range points to the series data values.
Step 5 uses the Split function to parse this formula in order to extract the range for the series data values.
6. Step 6 uses the dValues variant variable to capture the array of data values in the active series.
7. Step 7 starts the looping through the data points in the series. It does this by setting i to count from 1 to the number of data points in dValues. When the loop begins, i initiates with the number 1. As the macro loops, the variable increments up one number until it reaches a number equal to the maximum number of data points in the series.
As the macro loops, it uses i as the index number for the Points collection, effectively exposing the properties for each data point. We then set the color index of the data point to match the
color index for its corresponding source cell.
8. In the last step, the macro loops back around to get the next series. After we have gone through all the data series in the chart, the macro ends.
If you don't see personal.xlb in your project window, it 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, select the Personal Macro Workbook option in the Record Macro dialog box before you start recording. This option is in the Store Macro In drop-down list box. Simply record a couple of cell clicks and then stop recording. You
can discard the recorded macro and replace it with this one.
Part VIII: E-Mailing from Excel
This Part covers e-mailing from Excel: converting ranges or worksheets to attachments, saving attachments to a folder, and more.
In This Part
Macro 85 Mailing the Active Workbook as an Attachment
Macro 86 Mailing a Specific Range as Attachment
Macro 87 Mailing a Single Sheet as an Attachment
Macro 88 Send Mail with a Link to Our Workbook
Macro 89 Mailing All E-Mail Addresses in Our Contact List
Macro 90 Saving All Attachments to a Folder
Macro 91 Saving Certain Attachments to a Folder
Did you know that you probably integrate Excel and Outlook all the time? It's true. If you've sent or received an Excel workbook through Outlook, you've integrated the two programs; albeit
manually. In this Part, we show you a few examples of how you can integrate Excel and Outlook in a more automated fashion.
Note that the macros in this Part automate Microsoft Outlook. For these macros to work, you need to have Microsoft Outlook installed on your system.
The code for this Part can be found on this book's companion website. See this book's Introduction for more on the companion website.
Macro 85: Mailing the Active Workbook as an
Attachment
The most fundamental Outlook task you can perform through automation is sending an e-mail. In the sample code shown here, the active workbook is sent to two e-mail recipients as an attachment.
Some of you may notice that we are not using the SendMail command native to Excel. With the SendMail command, you can send simple e-mail messages directly from Excel. However, the SendMail command is not as robust as Outlook automation. SendMail does not allow you to attach files, or use the CC and BCC fields in the e-mail. This makes the technique used by this macro a superior method.
How it works
Because this code will be run from Excel, we need to set a reference to the Microsoft Outlook Object Library. We can set the reference by opening the Visual Basic Editor in Excel and selecting Tools⇒References. Scroll down until you find the entry Microsoft Outlook XX Object Library, where the XX is your version of Outlook. Select the check box next to the entry.
Sub Macro85()
‘Step 1: Declare our variables Dim OLApp As Outlook.Application Dim OLMail As Object
‘Step 2: Open Outlook start a new mail item
Set OLApp = New Outlook.Application Set OLMail = OLApp.CreateItem(0) OLApp.Session.Logon
‘Step 3: Build our mail item and send
With OLMail
.To = “admin@datapigtechnologies.com; mike@datapigtechnologies.com”
.CC = “”
.BCC = “”
.Subject = “This is the Subject line”
.Body = “Sample File Attached”
.Attachments.Add ActiveWorkbook.FullName
.Display
End With
‘Step 4: Memory cleanup
Set OLMail = Nothing Set OLApp = Nothing End Sub
1. Step 1 first declares two variables. OLApp is an object variable that exposes the Outlook
Application object. OLMail is an object variable that holds a mail item.
2. Step 2 activates Outlook and starts a new session. Note that we use OLApp.Session.Logon to log on to the current MAPI (Messaging Application Programming Interface) session with default credentials. It also creates a mail item. This is equivalent to selecting the New Message button in Outlook.
3. Step 3 builds the profile of our mail item. This includes the To recipients, the CC recipients, the BCC recipients, the Subject, the Body, and the Attachments. This step notes that the recipients are entered in quotes and separates recipients with a semicolon. The standard syntax for an attachment is .Attachments.Add “File Path”. Here in this code, we specify the current workbook's file path with the syntax ActiveWorkbook.Fullname. This sets the current workbook as the attachment for the e-mail. When the message has been built, we use the
.Display method to review the e-mail. We can replace .Display with .Send to automatically fire the e-mail without reviewing.
4. Releasing the objects assigned to our variables is generally good practice. This reduces the chance of any problems caused by rouge objects that may remain open in memory. As we can see in the code, we simply set variable to Nothing.
Macro 86: Mailing a Specific Range as
Attachment
You may not always want to send your entire workbook through e-mail. This macro demonstrates
how to send a specific range of data rather than the entire workbook.
How it works
Because this code is run from Excel, we need to set a reference to the Microsoft Outlook Object Library. We can set the reference by opening the Visual Basic Editor in Excel and selecting Tools⇒References. Scroll down until you find the entry Microsoft Outlook XX Object Library, where the XX is your version of Outlook. Select the check box next to the entry.
Sub Macro86()
‘Step 1: Declare our variables Dim OLApp As Outlook.Application Dim OLMail As Object
‘Step 2: Copy range, paste to new workbook, and save it Sheets(“Revenue Table”).Range(“A1:E7”).Copy Workbooks.Add
Range(“A1”).PasteSpecial xlPasteValues
Range(“A1”).PasteSpecial xlPasteFormats
ActiveWorkbook.SaveAs ThisWorkbook.Path & “\TempRangeForEmail.xlsx”
‘Step 3: Open Outlook start a new mail item
Set OLApp = New Outlook.Application Set OLMail = OLApp.CreateItem(0) OLApp.Session.Logon
‘Step 4: Build our mail item and send
With OLMail
.To = “admin@datapigtechnologies.com; mike@datapigtechnologies.com”
.CC = “”
.BCC = “”
.Subject = “This is the Subject line”
.Body = “Sample File Attached”
.Attachments.Add (ThisWorkbook.Path & “\TempRangeForEmail.xlsx”)
.Display
End With
‘Step 5: Delete the temporary Excel file
ActiveWorkbook.Close SaveChanges:=True
Kill ThisWorkbook.Path & “\TempRangeForEmail.xlsx”
‘Step 6: Memory cleanup
Set OLMail = Nothing Set OLApp = Nothing End Sub
1. Step 1 declares two variables. OLApp is an object variable that exposes the Outlook
Application object. OLMail is an object variable that holds a mail item.
2. Step 2 copies a specified range and pastes the values and formats to a temporary Excel file. The macro then saves that temporary file, giving it a file path and filename.
3. Step 3 activates Outlook and starts a new session. Note that we use OLApp.Session.Logon to log on to the current MAPI session with default credentials. We also create a mail item. This is equivalent to selecting the New Message button in Outlook.
4. Step 4 builds the profile of the mail item. This includes the To recipients, the CC recipients,the BCC recipients, the Subject, the Body, and the Attachments. This step notes that the recipients are entered in quotes and separates recipients by a semicolon.
Here in this code, we specify our newly created temporary Excel file path as the attachment for the e-mail. When the message has been built, we use the .Display method to review the e-mail. We can replace .Display with .Send to automatically fire the e-mail without reviewing.
5. We don't want to leave temporary files hanging out there, so after the e-mail has been sent, Step 5 deletes the temporary Excel file we created.
6. It is generally good practice to release the objects assigned to our variables. This reduces the chance of any problems caused by rouge objects that may remain open in memory. In Step 6, we simply set variable to Nothing.
Macro 87: Mailing a Single Sheet as an
Attachment
This example demonstrates how we would send a specific worksheet of data rather than the entire workbook.
How it works
Because this code is run from Excel, we need to set a reference to the Microsoft Outlook Object Library. We can set the reference by opening the Visual Basic Editor in Excel and selecting Tools⇒References. Scroll down until we find the entry Microsoft Outlook XX Object Library, where the XX is your version of Outlook. Place a check in the check box next to the entry.
Sub Macro87()
‘Step 1: Declare our variables Dim OLApp As Outlook.Application Dim OLMail As Object
‘Step 2: Copy Worksheet, paste to new workbook, and save it
Sheets(“Revenue Table”).Copy
ActiveWorkbook.SaveAs ThisWorkbook.Path & “\TempRangeForEmail.xlsx”
‘Step 3: Open Outlook start a new mail item
Set OLApp = New Outlook.Application Set OLMail = OLApp.CreateItem(0) OLApp.Session.Logon
‘Step 4: Build our mail item and send
With OLMail
.To = “admin@datapigtechnologies.com; mike@datapigtechnologies.com”
.CC = “”
.BCC = “”
.Subject = “This is the Subject line”
.Body = “Sample File Attached”
.Attachments.Add (ThisWorkbook.Path & “\TempRangeForEmail.xlsx”)
.Display
End With
‘Step 5: Delete the temporary Excel file
ActiveWorkbook.Close SaveChanges:=True
Kill ThisWorkbook.Path & “\TempRangeForEmail.xlsx”
‘Step 6: Memory cleanup
Set OLMail = Nothing Set OLApp = Nothing End Sub
1. Step 1 first declares two variables. OLApp is an object variable that exposes the Outlook
Application object. OLMail is an object variable that holds a mail item.
2. Step 2 copies a specified range and pastes the values and formats to a temporary Excel file. We then save that temporary file, giving it a file path and filename.
3. Step 3 activates Outlook and starts a new session. Note that we use OLApp.Session.Logon to log on to the current MAPI session with default credentials. We also create a mail item. This is equivalent to selecting the New Message button in Outlook.
4. Step 4 builds the profile of the mail item. This includes the To recipients, the CC recipients, the BCC recipients, the Subject, the Body, and the Attachments. The recipients are entered in quotes and separated by a semicolon.
In this code, we specify our newly created temporary Excel file path as the attachment for the e- mail. When the message has been built, we use the .Display method to review the e-mail. We can replace .Display with .Send to automatically fire the e-mail without reviewing.
5. We don't want to leave temporary files hanging out there, so after the e-mail has been sent, we delete the temporary Excel file we created.
6. It is generally good practice to release the objects assigned to our variables. This reduces the chance of any problems caused by rouge objects that may remain open in memory. As we can see in the code, we simply set variable to Nothing.
4. Type or paste the code into the newly created module.
Macro 88: Send Mail with a Link to Our Workbook
Sometimes, you don't need to send an attachment at all. Instead, you simply want to send an automated e-mail with a link to a file. This macro does just that.
Note that your users or customers will have to have at least read access to the network or location that is tied to the link.
How it works
Keep in mind that because this code will be run from Excel, we need to set a reference to the Microsoft Outlook Object Library. We can set the reference by opening the Visual Basic Editor in Excel and selecting Tools⇒References. Scroll down until we find the entry Microsoft Outlook XX Object Library, where the XX is your version of Outlook. Select the check box next to the entry.
Sub Macro88()
‘Step 1: Declare our variables Dim OLApp As Outlook.Application Dim OLMail As Object
‘Step 2: Open Outlook start a new mail item
Set OLApp = New Outlook.Application Set OLMail = OLApp.CreateItem(0) OLApp.Session.Logon
‘Step 3: Build our mail item and send
With OLMail
.To = “admin@datapigtechnologies.com; mike@datapigtechnologies.com”
.CC = “”
.BCC = “”
.Subject = “Monthly Report Email with Link”
.HTMLBody = _
“Monthly report is ready. Click to Link to get it.
” & _ “Download Now
”
.Display
End With
‘Step 4: Memory cleanup
Set OLMail = Nothing Set OLApp = Nothing End Sub
1. Step 1 declares two variables. OLApp is an object variable that exposes the Outlook
Application object. OLMail is an object variable that holds a mail item.
2. Step 2 activates Outlook and starts a new session. Note that we use OLApp.Session.Logon to log on to the current MAPI session with default credentials. This step also creates a mail item. This is equivalent to selecting the New Message button in Outlook.
3. Step 3 builds the profile of our mail item. This includes the To recipients, the CC recipients, the BCC recipients, the Subject, and the HTMLBody.
To create the hyperlink, we need to use the HTMLBody property to pass HTML tags. We can replace the file path address shown in the macro with the address for our file. Note this macro
is using the .Display method, which opens the e-mail for our review. We can replace .Display
with .Send to automatically fire the e-mail without reviewing.
4. It is generally good practice to release the objects assigned to our variables. This reduces the chance of any problems caused by rouge objects that may remain open in memory. In Step 4, we simply set variable to Nothing.
Macro 89: Mailing All E-Mail Addresses in Our
Contact List
Ever need to send out a mass mailing such as a newsletter or a memo? Instead of manually entering each of our contacts' e-mail address, we can run the following procedure. In this procedure, we send out one e-mail, automatically adding all the e-mail addresses in our contact list to our e-mail.
How it works
Because this code will be run from Excel, we need to set a reference to the Microsoft Outlook Object Library. We can set the reference by opening the Visual Basic Editor in Excel and selecting Tools⇒References. Scroll down until we find the entry Microsoft Outlook XX Object Library, where the XX is your version of Outlook. Select the check box next to the entry.
Sub Macro89()
‘Step 1: Declare our variables Dim OLApp As Outlook.Application Dim OLMail As Object
Dim MyCell As Range
Dim MyContacts As Range
‘Step 2: Define the range to loop through
Set MyContacts = Sheets(“Contact List”).Range(“H2:H21”)
‘Step 3: Open Outlook
Set OLApp = New Outlook.Application
Set OLMail = OLApp.CreateItem(0)
OLApp.Session.Logon
‘Step 4: Add each address in the contact list
With OLMail
For Each MyCell In MyContacts
.BCC = .BCC & Chr(59) & MyCell.Value
Next MyCell
.Subject = “Sample File Attached”
.Body = “Sample file is attached”
.Attachments.Add ActiveWorkbook.FullName
.Display
End With
‘Step 5: Memory cleanup
Set OLMail = Nothing Set OLApp = Nothing End Sub
1. Step 1 declares four variables: OLApp is an object variable that exposes the Outlook Application object. OLMail is an object variable that holds a mail item. MyCell is an object variable that holds an Excel range. MyContacts is an object variable that holds an Excel range.
2. Step 2 points to the MyContacts variable to the range of cells that contains our e-mail addresses. This is the range of cells we loop through to add e-mail addresses to our e-mail.
3. Step 3 activates Outlook and starts a new session. Note that we use OLApp.Session.Logon to log on to the current MAPI session with default credentials. We also create a mail item. This is equivalent to selecting the New Message button in Outlook.
4. Step 4 builds the profile of our mail item. We note that we are looping through each cell in the MyContacts range and adding the contents (which are e-mail addresses) to the BCC. Here, we are using the BCC property instead of To or CC so that each recipient gets an e-mail that looks as though it was sent only to him. Our recipients won't be able to see any of the other e- mail addresses because they have been sent with BCC (Blind Courtesy Copy). Note this macro is using the .Display method, which opens the e-mail for our review. We can replace .Display with .Send to automatically fire the e-mail without reviewing.
5. It is generally good practice to release the objects assigned to our variables. This reduces the chance of any problems caused by rouge objects that may remain open in memory. In Step 5, we simply set the variable to Nothing.
Macro 90: Saving All Attachments to a Folder
You may often find that certain processes lend themselves to the exchange of data via e-mail. For example, you may send a budget template out for each branch manager to fill out and send back to you via e-mail. Well, if there are 150 branch members, it could be a bit of a pain to bring down all those e-mail attachments.
The following procedure demonstrates one solution to this problem. In this procedure, we use automation to search for all attachments in the inbox and save them to a specified folder.
How it works
Because this code will be run from Excel, we need to set a reference to the Microsoft Outlook Object Library. You can set the reference by opening the Visual Basic Editor in Excel and selecting Tools⇒References. Scroll down until you find the entry Microsoft Outlook XX Object Library, where the XX is your version of Outlook. Select the check box next to the entry.
Sub Macro90()
‘Step 1: Declare our variables
Dim ns As Namespace
Dim MyInbox As MAPIFolder
Dim MItem As MailItem Dim Atmt As Attachment Dim FileName As String
‘Step 2: Set a reference to our inbox
Set ns = GetNamespace(“MAPI”)
Set MyInbox = ns.GetDefaultFolder(olFolderInbox)
‘Step 3: Check for messages in our inbox; exit if none
If MyInbox.Items.Count = 0 Then MsgBox “No messages in folder.” Exit Sub
End If
‘Step 4: Create directory to hold attachments
On Error Resume Next
MkDir “C:\Temp\MyAttachments\”
‘Step 5: Start to loop through each mail item
For Each MItem In MyInbox.Items
‘Step 6: Save each attachment then go to the next attachment
For Each Atmt In MItem.Attachments
FileName = “C:\Temp\MyAttachments\” & Atmt.FileName
Atmt.SaveAsFile FileName
Next Atmt
‘Step 7: Move to the next mail item
Next MItem
‘Step 8: Memory cleanup
Set ns = Nothing
Set MyInbox = Nothing
End Sub
1. Step 1 declares five variables. ns is an object used to expose the MAPI namespace. MyInbox
is used to expose the target mail folder. MItem is used to expose the properties of a mail item. Atmt is an object variable that holds an Attachment object. FileName is a string variable that holds the name of the attachment.
2. Step 2 sets the MyInbox variable to point to the inbox for the default mail client.
3. Step 3 performs a quick check to make sure there are actually messages in the inbox. If there are no messages, the macro exits the procedure with a message box stating that there are no messages.
4. Step 4 creates a directory to hold the attachments we find. Although you could use an existing directory, using a directory dedicated specifically for the attachments you bring down is usually best. Here, we are creating that directory on the fly. Note we are using On Error Resume
Next. This ensures that the code does not error out if the directory we are trying to create
already exists.
5. Step 5 starts the loop through each mail item in the target mail folder.
6. Step 6 ensures that each mail item we loop through gets checked for attachments. As we loop, we save each attachment we find into the specified directory we created.
7. Step 7 loops back to Step 5 until there are no more mail items to go through.
8. Releasing the objects assigned to our variables is good general practice. This reduces the chance of any problems caused by rogue objects that may remain open in memory. Step 8 simply sets the variable to Nothing.
Macro 91: Saving Certain Attachments to a Folder
In the previous procedure, we showed you how to use automation to search for all attachments in your inbox and save them to a specified folder. However, in most situations, you probably only want to save certain attachments; for example, those attachments attached to e-mails that contain a certain Subject. In this example, we get a demonstration of how to check for certain syntax and selectively bring down attachments.
How it works
Because this code will be run from Excel, we need to set a reference to the Microsoft Outlook
Object Library. We can set the reference by opening the Visual Basic Editor in Excel and selectingTools⇒References. Scroll down until we find the entry Microsoft Outlook XX Object Library, where the XX is your version of Outlook. Select the check box next to the entry.
Sub Macro91()
‘Step 1: Declare our variables
Dim ns As Namespace
Dim MyInbox As MAPIFolder
Dim MItem As Object
Dim Atmt As Attachment Dim FileName As String Dim i As Integer
‘Step 2: Set a reference to our inbox
Set ns = GetNamespace(“MAPI”)
Set MyInbox = ns.GetDefaultFolder(olFolderInbox)
‘Step 3: Check for messages in our inbox; exit if none
If MyInbox.Items.Count = 0 Then MsgBox “No messages in folder.” Exit Sub
End If
‘Step 4: Create directory to hold attachments
On Error Resume Next
MkDir “C:\OffTheGrid\MyAttachments\”
‘Step 5: Start to loop through each mail item
For Each MItem In MyInbox.Items
‘Step 6: Check for the words Data Submission in Subject line
If InStr(1, MItem.Subject, “Data Submission”) < 1 Then
GoTo SkipIt
End If
‘Step 7: Save each with a log number; go to the next attachment i = 0
For Each Atmt In MItem.Attachments
FileName = _
“C:\Temp\MyAttachments\Attachment-” & i & “-” & Atmt.FileName
Atmt.SaveAsFile FileName i = i + 1
Next Atmt
‘Step 8: Move to the next mail item
SkipIt: Next MItem
‘Step 9: Memory cleanup
Set ns = Nothing
Set MyInbox = Nothing
End Sub
1. Step 1 first declares six variables. ns is an object used to expose the MAPI namespace. MyInbox is used to expose the target mail folder. MItem is used to expose the properties of a mail item. Atmt is an object variable that holds an Attachment object. FileName is a string variable that holds the name of the attachment. i is an integer variable used to ensure each attachment is saved as a unique name.
2. Step 2 sets the MyInbox variable to point to the inbox for our default mail client.
3. Step 3 performs a quick check to make sure there are actually messages in our inbox. If there
are no messages, it exits the procedure with a message box stating that there are no messages.
4. Step 4 creates a directory to hold the attachments we find. Note that it uses On Error Resume Next. This ensures that the code does not error out if the directory we are trying to create already exists.
5. Step 5 starts the loop through each mail item in the target mail folder.
6. In Step 6, we use the Instr function to check whether the string Data Submission is in the Subject line of the e-mail. If that string does not exist, we don't care about any attachments to that message. Therefore, we force the code to go to the SkipIt reference (in Step 8). Because the line of code immediately following the SkipIt reference is essentially a Move Next command, this has the effect of telling the procedure to move to the next mail item.
7. Step 7 loops through and saves each attachment into the specified directory we created. Note that we are adding a running integer to the name of each attachment. This is to ensure that each attachment is saved as a unique name, helping us to avoid overwriting attachments.
8. Step 8 loops back to Step 5 until there are no more mail items to go through.
9. Releasing the objects assigned to our variables is generally good practice. This reduces the chance of any problems caused by rouge objects that may remain open in memory. In Step 9, we simply set variable to Nothing.
Part IX: Integrating Excel and Other Office
Applications
This Part contains macros for making Excel work with PowerPoint, Word, and Access.
In This Part
Macro 92 Running an Access Query from Excel
Macro 93 Running an Access Macro from Excel
Macro 94 Opening an Access Report from Excel
Macro 95 Opening an Access Form from Excel
Macro 96 Compacting an Access Database from Excel
Macro 97 Sending Excel Data to a Word Document
Macro 98 Simulating Mail Merge with a Word Document
Macro 99 Sending Excel Data to a PowerPoint Presentation
Macro 100 Sending All Excel Charts to a PowerPoint Presentation
Macro 101 Convert a Workbook into a PowerPoint Presentation
Every data-oriented process has an application flow — a succession of applications that take the data from creation to end-user. Sometimes a dataset is touched by only one application, such as when you're creating a report and presenting it in Excel. In many cases, however, data is moved from a database such as Microsoft Access, analyzed and aggregated in Excel, and then distributed via a Word document, PowerPoint presentation, or even e-mail. In this Part, we look at some of the useful macros you can implement to have Excel integrate with other Office applications.
The code for this Part can be found on this book's companion website. See this book's Introduction for more on the companion website.
Macro 92: Running an Access Query from Excel
Here's a nifty macro for those of you who often copy and paste the results of your Microsoft Access queries to Excel. In this macro, you use DAO (Data Access Object) to open and run an Access query in the background and output the results into Excel.
How it works
In this macro, you point Excel to an Access database and pull data from an existing Access query. You then store that query in a Recordset object, which you can use to populate your Excel spreadsheet.
Because you are automating Access, you need to set a reference to the Microsoft Access Object Library. To do so, open the VBE in Excel and select Tools⇒References. The Reference dialog box opens. Scroll down until you find the entry Microsoft Access XX Object Library, where the XX is your version of Access. Select the check box next to the entry.
In addition to the Access Object Library, you need to set a reference to Microsoft DAO XX Object Library, where the XX is the version number. Note that you may see multiple versions of this library in the Reference dialog box. You should generally select the latest version of the Microsoft DAO Library available. While still in the Reference dialog box, select the check box next to the entry.
Sub Macro92()
‘Step 1: Declare your variables Dim MyDatabase As DAO.Database Dim MyQueryDef As DAO.QueryDef Dim MyRecordset As DAO.Recordset Dim i As Integer
‘Step 2: Identify the database and query Set MyDatabase = DBEngine.OpenDatabase _ (“C:\Temp\YourAccessDatabse.accdb”)
Set MyQueryDef = MyDatabase.QueryDefs(“Your Query Name”)
‘Step 3: Open the query
Set MyRecordset = MyQueryDef.OpenRecordset
‘Step 4: Clear previous contents Sheets(“Sheet1”).Select ActiveSheet.Range(“A6:K10000”).ClearContents
‘Step 5: Copy the recordset to Excel
ActiveSheet.Range(“A7”).CopyFromRecordset MyRecordset
‘Step 6: Add column heading names to the spreadsheet
For i = 1 To MyRecordset.Fields.Count
ActiveSheet.Cells(6, i).Value = MyRecordset.Fields(i - 1).Name
Next i
End Sub
1. Step 1 declares the necessary variables. The MyDatabase object variable exposes your Access database application via the DAO Object Library. MyQueryDef is also an object variable that serves as a memory container for the target query. MyRecordset is a Recordset object that holds the results of the data pull. In addition to these, the i integer variable is used to add column headings.
2. Step 2 specifies the database that holds your target query as well as which query will be run. Assigning the query to a QueryDef object allows you to essentially open the query in memory.
3. Step 3 literally runs the query in memory. The results of the query are then stored into the
MyRecordset object. After the results are in a recordset, you can output the data to Excel.
4. Step 4 prepares for the recordset output by clearing the output area. This ensures no residual data is left from previous data pulls.
5. This step uses Excel's CopyFromRecordset method to get the returned dataset into the spreadsheet. In this example, the macro copies the data in the MyRecordset object onto Sheet1 at cell A7.
6. Finally, you enumerate through each field in the recordset to automatically get the name of each header and enter it into Excel.
Macro 93: Running an Access Macro from Excel
You can run Access macros from Excel, using automation to fire the macro without opening Access. This technique can be useful not only for running those epic macros that involve a multistep series of 20 queries, but can also come in handy for everyday tasks like outputting a
Access data to an Excel file.
How it works
The following macro is a simple way to trigger an Access macro programmatically.
Note that you will need to set a reference to the Microsoft Access Object Library. To do so, open the VBE in Excel and select Tools⇒References. The Reference dialog box opens. Scroll down until you find the entry Microsoft Access XX Object Library, where the XX is your version of Access. Select the check box next to the entry.
Sub Macro93()
‘Step 1: Declare your variables
Dim AC As Access.Application
‘Step 2: Start Access and open the target database Set AC = New Access.Application AC.OpenCurrentDatabase _ (“C:\Temp\YourAccessDatabse.accdb”)
‘Step 3: Open the target report and send to Word
With AC
.DoCmd.RunMacro “MyMacro”
.Quit
End With
End Sub
1. The first thing the macro does is declare the AC object variable. This variable exposes the
Access database application library.
2. Step 2 uses the AC variable to start a new instance of Microsoft Access and open the database that houses the target macro.
3. Step 3 runs the appropriate macro and closes the database.
Macro 94: Opening an Access Report from Excel
Access reports allow you to build professional looking reports that have a clean PDF-style look and feel. If you run and distribute a great deal of Access reports, the following macro can help automate your processes.
How it works
This macro demonstrates how you can open your Access reports right from Excel. The appealing thing about this technique is that you don't see Access at all; the report goes straight to a Word rich text file.
Note that you will need to set a reference to the Microsoft Access Object Library. To do so, open the VBE in Excel and select Tools⇒References. The Reference dialog box opens. Scroll down until you find the entry Microsoft Access XX Object Library, where XX is your version of Access. Select the check box next to the entry.
Sub Macro94()
‘Step 1: Declare your variables
Dim AC As Access.Application
‘Step 2: Start Access and open the target database Set AC = New Access.Application AC.OpenCurrentDatabase _ (“C:\Temp\YourAccessDatabase.accdb”)
‘Step 3: Open the target report as a Word rich text file
With AC
.DoCmd.OpenReport “Revenue Report”, acViewPreview
.DoCmd.RunCommand acCmdOutputToRTF
.Quit
End With
End Sub
1. Step 1 declares the AC object variable. This variable exposes the Access database application library.
2. In Step 2, the AC variable starts a new instance of Microsoft Access and opens the database that houses the target report.
3. Step 3 simply runs the appropriate report, sending the output to a Microsoft Word rich text file. After the file is output, the database closes.
Macro 95: Opening an Access Form from Excel
In some instances, you or your clients may need to switch focus to an Access form. This example demonstrates how you can open an Access form from Excel.
How it works
With this macro, you point Excel to an Access database and trigger a specific Access form to open.
Because you are automating Access, you need to set a reference to the Microsoft Access Object
Library. To do so, open the VBE in Excel and select Tools⇒References. The Reference dialog
box activates. Scroll down until you find the entry Microsoft Access XX Object Library, where the
XX is your version of Access. Select the check box next to the entry.
Sub Macro95()
‘Step 1: Declare your variables
Dim AC As Access.Application
‘Step 2: Start Access and open the target database Set AC = New Access.Application AC.OpenCurrentDatabase _ (“C:\Temp\YourAccessDatabase.accdb”)
‘Step 3: Open the target form and make Access visible
With AC
.DoCmd.OpenForm “MainForm”, acNormal
.Visible = True
End With
End Sub
1. Step 1 declares the AC object variable. This variable exposes the Access database application library.
2. Step 2 uses the AC variable to start a new instance of Microsoft Access and opens the database that houses the target form.
3. Step 3 opens the appropriate form. The Access form opens in a new Microsoft Access window. Note that you are not closing the database in the last line of Step 3 (as with the previous macros). Instead, you make the Access application visible.
.
Macro 96: Compacting an Access Database from
Excel
During your integrated processes, you may routinely increase or decrease the number of records
and tables in your database. As time goes on, you may notice that your Access database gets bigger. This is because Access does not release file space. All the space needed for the data you move in and out of your database is held by your Access file, regardless of whether the data is still there. In that light, it's critical that you run Compact and Repair on your Access database regularly. Among other things, running Compact and Repair defragments your database, releasing any unused space and ensuring your database does not grow to an unmanageable size. Office automation enables you to Compact and Repair your databases right from code.
How it works
When you compact and repair an Access database manually, it seems as though Access compresses your original database; this is not the case. Access is really doing nothing more than creating a copy of your Access database (minus the empty file space) and deleting the old file.
This macro essentially mimics those actions in order to programmatically Compact and Repair an
Access application.
Note that in order to use this code, you need to set a reference to the Microsoft Access Object Library. To do so, open the VBE in Excel and select Tools⇒References. The Reference dialog box opens. Scroll down until you find the entry Microsoft Access XX Object Library, where the XX is your version of Access. Select the check box next to the entry.
Sub Macro96()
‘Step 1: Declare your variables
Dim OriginalFile As String Dim BackupFile As String Dim TempFile As String
‘Step 2: Identify the target database assign file paths
OriginalFile = “C:\Temp\MyDatabase.accdb” BackupFile = “C:\Temp\MyDatabaseBackup.accdb” TempFile = “C:\Temp\MyDatabaseTemporary.accdb”
‘Step 3: Make a backup copy of database
FileCopy OriginalFile, BackupFile
‘Step 4: Perform the compact and repair
DBEngine.CompactDatabase OriginalFile, TempFile
‘Step 5: Delete the old database
Kill OriginalFile
‘Step 6: Rename the temporary database to the old database name
Name TempFile As OriginalFile
End Sub
1. Step 1 declares three string variables that hold filenames.
2. Step 2 then assigns each of the string variables a filename. The OriginalFile variable is assigned the file path and name of the target database. The BackupFile variable is assigned the file path and name of a backup file we will create during this procedure. The TempFile
variable is assigned the file path and name of a temporary file we create during this procedure.
3. Step 3 uses the FileCopy function to make a backup of the OriginalFile (the target
database). Although this step is not necessary for the Compact and Repair procedure, it's generally a good practice to make a backup of your database before running this level of VBA on it.
4. Step 4 executes the Compact and Repair, specifying the original database and specifying the file path of the temporary database.
5. At this point, you have two copies of your database: the original database and a second database, which is a copy of your original without the empty file space. Step 5 deletes the original database, leaving you with the copy.
6. Step 6 simply renames the temporary file, giving it the name of your original database. This leaves you with a database that is compact and optimized.
Macro 97: Sending Excel Data to a Word
Document
If you find that you are constantly copying and pasting data into Microsoft Word, you can use a macro to automate this task.
How it works
Before walking through the macro, it's important to go over a few set-up steps.
To get ready for a process like this, you must have a template Word document already created. In that document, create a bookmark tagging the location where you want your Excel data to be copied.
To create a bookmark in a Word document, place your cursor where you want the bookmark, select the Insert tab, and select Bookmark (found under the Links group). This activates the Bookmark dialog box where you assign a name for your bookmark. After the name has been assigned, click
the Add button.
One of the sample files for this Part is a document called PasteTable.docx. This document is a simple template that contains one bookmark called DataTableHere. In this sample code, you copy a range to that PasteTable.docx template, using the DataTableHere bookmark to specify where to paste the copied range.
You also need to set a reference to the Microsoft Word Object Library. To do so, open the Visual Basic Editor in Excel and select Tools⇒References. The Reference dialog box opens. Scroll down until you find the entry Microsoft Word XX Object Library, where the XX is your version of Word. Select the check box next to the entry.
Sub Macro97()
‘Step 1: Declare your variables
Dim MyRange As Excel.Range Dim wd As Word.Application Dim wdDoc As Word.Document Dim WdRange As Word.Range
‘Step 2: Copy the defined range
Sheets(“Revenue Table”).Range(“B4:F10”).Copy
‘Step 3: Open the target Word document
Set wd = New Word.Application Set wdDoc = wd.Documents.Open _ (ThisWorkbook.Path & “\” & “PasteTable.docx”) wd.Visible = True
‘Step 4: Set focus on the target bookmark
Set WdRange = wdDoc.Bookmarks(“DataTableHere”).Range
‘Step 5: Delete the old table and paste new On Error Resume Next WdRange.Tables(1).Delete
WdRange.Paste ‘paste in the table
‘Step 6: Adjust column widths
WdRange.Tables(1).Columns.SetWidth _
(MyRange.Width / MyRange.Columns.Count), wdAdjustSameWidth
‘Step 7: Reinsert the bookmark wdDoc.Bookmarks.Add “DataTableHere”, WdRange
‘Step 8: Memory cleanup
Set wd = Nothing
Set wdDoc = Nothing Set WdRange = Nothing End Sub
1. Step 1 declares four variables: MyRange contains the target Excel range you want copied; wd is an object variable that exposes the Word Application object; wdDoc is an object variable that exposes the Word Document object; and wdRange is an object variable that exposes the Word Range object.
2. Step 2 copies a range from the Revenue Table worksheet. In this example, the range is hard- coded, but we can always make this range into something more variable.
3. Step 3 opens an existing target Word document that serves as a template. Note that we are setting the Visible property of the Word application to True. This ensures that we can see the
action in Word as the code runs.
4. Step 4 uses Word's Range object to set focus on the target bookmark. This essentially selects the bookmark as a range, allowing you to take actions in that range.
5. Step 5 deletes any table that may exist within the bookmark, and then pastes the copied Excel range. If we don't delete any existing tables first, the copied range is appended to the existing data.
6. When you're pasting an Excel range into a Word document, the column widths don't always fit the content in the cells appropriately. Step 6 fixes this issue by adjusting the column widths. Here, each column's width is set to a number that equals the total width of the table divided by the number of columns in the table.
7. When we paste an Excel range to the target bookmark, we essentially overwrite the bookmark. Step 7 re-creates the bookmark to ensure that the next time you run this code, the bookmark is there.
8. Finally, the macro releases the objects assigned to the variables, reducing the chance of any problems caused by rogue objects that may remain open in memory.
Macro 98: Simulating Mail Merge with a Word
Document
One of the most requested forms of integration with Word is the mail merge. In most cases, mail merge refers to the process of creating one letter or document and then combining it with a separate document containing their names and addresses. For example, suppose you had a list of customers and you wanted to compose a letter to each customer. With mail merge, you can write the body of the letter one time, and then run the mail merge feature in Word to automatically create a letter for each customer, affixing the appropriate, address, name and other information to each letter.
For you automation buffs, you can use a macro to simulate the Word mail merge function from
Excel.
How it works
The idea is relatively simple. You start with a template that contains bookmarks identifying where each element of contact information should go. After the template is ready, the idea is to simply loop through each contact in your contact list, assigning the component pieces of their contact information to the respective bookmarks.
One of the sample files for this Part is a document called MailMerge.docx. This document has all the bookmarks needed to run the sample code shown here.
Note that you will need to set a reference to the Microsoft Word Object Library. To do so, open the Visual Basic Editor in Excel and select Tools⇒References. The Reference dialog box opens. Scroll down until you find the entry Microsoft Word XX Object Library, where the XX is your version of Word. Select the check box next to the entry.
Sub Macro98()
‘Step 1: Declare your variables
Dim wd As Word.Application Dim wdDoc As Word.Document Dim MyRange As Excel.Range Dim MyCell As Excel.Range Dim txtAddress As String Dim txtCity As String
Dim txtState As String
Dim txtPostalCode As String
Dim txtFname As String
Dim txtFullname As String
‘Step 2: Start Word and add a new document
Set wd = New Word.Application Set wdDoc = wd.Documents.Add wd.Visible = True
‘Step 3: Set the range of your contact list
Set MyRange = Sheets(“Contact List”).Range(“A5:A24”)
‘Step 4: Start the loop through each cell
For Each MyCell In MyRange.Cells
‘Step 5: Assign values to each component of the letter txtAddress = MyCell.Value
txtCity = MyCell.Offset(, 1).Value txtState = MyCell.Offset(, 2).Value txtPostalCode = MyCell.Offset(, 3).Value txtFname = MyCell.Offset(, 5).Value txtFullname = MyCell.Offset(, 6).Value
‘Step 6: Insert the structure of template document wd.Selection.InsertFile _
ThisWorkbook.Path & “\” & “MailMerge.docx”
‘Step 7: Fill each relevant bookmark with respective value wd.Selection.Goto What:=wdGoToBookmark, Name:=”Customer” wd.Selection.TypeText Text:=txtFullname
wd.Selection.Goto What:=wdGoToBookmark, Name:=”Address”
wd.Selection.TypeText Text:=txtAddress wd.Selection.Goto What:=wdGoToBookmark, Name:=”City”
wd.Selection.TypeText Text:=txtCity
wd.Selection.Goto What:=wdGoToBookmark, Name:=”State”
wd.Selection.TypeText Text:=txtState wd.Selection.Goto What:=wdGoToBookmark, Name:=”Zip” wd.Selection.TypeText Text:=txtPostalCode wd.Selection.Goto What:=wdGoToBookmark, Name:=”FirstName” wd.Selection.TypeText Text:=txtFname
‘Step 8: Clear any remaining bookmarks On Error Resume Next wdDoc.Bookmarks(“Address”).Delete wdDoc.Bookmarks(“Customer”).Delete wdDoc.Bookmarks(“City”).Delete wdDoc.Bookmarks(“State”).Delete wdDoc.Bookmarks(“FirstName”).Delete wdDoc.Bookmarks(“Zip”).Delete
‘Step 9: Go to the end, insert new page, and start with the next cell wd.Selection.EndKey Unit:=wdStory
wd.Selection.InsertBreak Type:=wdPageBreak
Next MyCell
‘Step 10: Set cursor to beginning and clean up memory wd.Selection.HomeKey Unit:=wdStory
wd.Activate
Set wd = Nothing
Set wdDoc = Nothing
End Sub
1. Step 1 declares four variables: wd is an object variable that exposes the Word Application object, wdDoc is an object variable that exposes the Word Document object, MyRange contains the range defining the contact list, and MyCell is used to pass cell values into the string variables. We also declare six string variables. Each of the string variables holds a component piece of information for each contact in the contact list.
2. This step opens Word with a blank document. Note that we set the Visible property of the
Word application to True. This ensures that we can see the action in Word as the code runs.
3. Step 3 defines each contact in the contact list. Note that this range only selects the first column in the contacts table. This is because each cell in the range must be passed individually to string variables. Selecting only the first column gives us one cell per row. From that one cell, we can easily adjust the cursor to the right or left to capture the cells around it. The idea is that if we move to the right one space, we get the value of the next field in that row. If we move to the right two spaces, we get the value of that field, and so on.
4. This step starts the loop through each contact as defined in the range set in Step 3.
5. Step 5 uses Excel's Offset method to capture the value of each field in a particular row. We start with the range defined in Step 3 (the first column in the list of contacts). We then use Offset to move the cursor a certain number of columns to the right to capture the data in each relevant field. As each field is covered, we assign their values to the appropriate string variable.
6. In Step 6, we insert the existing template into the empty document in Word. This is tantamount to copying the structure of our template and pasting it into a blank document.
7. Step 7 assigns the value of each string variable to its respective bookmark. As you can see in the code, this step selects the bookmark by name, and then changes the text to equal the value of the assigned string variable.
8. The goal in Step 8 is to remove any stray bookmarks. If any bookmarks linger, we get duplicate bookmarks as the procedure loops through each cell.
9. At this point in the code, we have created a document for one contact in our list of contacts. The idea now is to create a new blank document so that we can perform the same procedure for the next contact. Inserting a page break effectively creates the new blank document. We then loop back to Step 5, where we pick up the contact information for the next row in the list. Then at Step 6, we insert the blank template (complete with bookmarks) into the new page. Finally, we assign values to the bookmarks and clean up. The For…Next loop ensures that this cycle is repeated for each row in the contact list.
10. Step 10 releases the objects assigned to your variables, reducing the chance of any problems caused by rogue objects that may remain open in memory.
Macro 99: Sending Excel Data to a PowerPoint
Presentation
It's been said that up to 50 percent of PowerPoint presentations contain data that has been copied straight out of Excel. This is not difficult to believe. It's often much easier to analyze and create charts and data views in Excel than in PowerPoint. After you've created those charts and data views, why wouldn't you simply move them into PowerPoint? The macro in this section allows you to dynamically create PowerPoint slides that contain data from a range you specify.
How it works
In this example, you are copying a range from an Excel file and pasting that range to a slide in a newly created PowerPoint presentation.
Keep in mind that because this code is run from Excel, you need to set a reference to the Microsoft PowerPoint Object Library. Again, you can set the reference by opening the Visual Basic Editor in Excel and selecting Tools⇒References. Scroll down until you find the entry Microsoft
PowerPoint XX Object Library, where the XX is your version of PowerPoint. Select the check boxnext to the entry.
Sub CopyRangeToPresentation ()
‘Step 1: Declare your variables
Dim PP As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide
Dim SlideTitle As String
‘Step 2: Open PowerPoint and create new presentation
Set PP = New PowerPoint.Application Set PPPres = PP.Presentations.Add PP.Visible = True
‘Step 3: Add new slide as slide 1 and set focus to it Set PPSlide = PPPres.Slides.Add(1, ppLayoutTitleOnly) PPSlide.Select
‘Step 4: Copy the range as a picture
Sheets(“Slide Data”).Range(“A1:J28”).CopyPicture _ Appearance:=xlScreen, Format:=xlPicture
‘Step 5: Paste the picture and adjust its position PPSlide.Shapes.Paste.Select PP.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True PP.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True
‘Step 6: Add the title to the slide SlideTitle = “My First PowerPoint Slide” PPSlide.Shapes.Title.TextFrame.TextRange.Text = SlideTitle
‘Step 7: Memory Cleanup
PP.Activate
Set PPSlide = Nothing Set PPPres = Nothing Set PP = Nothing
End sub
1. Step 1 declares four variables: PP is an object variable that exposes the PowerPoint Application object; PPPres is an object variable that exposes the PowerPoint Presentation object; PPSlide is an object variable that exposes the PowerPoint Slide object; and SlideTitle is an string variable used to pass the text for the slide title.
2. Step 2 opens PowerPoint with an empty presentation. Note that we are setting the Visible property of the PowerPoint application to True. This ensures that you can see the action as the code runs.
3. Step 3 adds a new slide to the presentation using the Add method of Slide object. Note that we are using the ppLayoutTitleOnly, ensuring the slide is created with a title text frame. We then take an extra step here and actually set focus on the slide. That is to say, we explicitly tell PowerPoint to select this slide, making it active.
4. Step 4 uses the CopyPicture method to copy the target range as a picture. The range being copied here is range A1 to J28 in the Slide Data tab.
5. Step 5 pastes the picture into the active slide and centers the picture both horizontally and vertically.
6. Step 6 stores the text for the title in a string variable, and then passes that variable to
PowerPoint to apply text to the title text frame.
7. Step 7 releases the objects assigned to our variables, reducing the chance of any problems caused by rogue objects that may remain open in memory.
Macro 100: Sending All Excel Charts to a
PowerPoint Presentation
It's not uncommon to see multiple charts on one worksheet. Many of us occasionally need to copy our charts to PowerPoint presentations. The macro here assists in that task, effectively automating the process of copying each one of these charts into its own slide.
How it works
In this macro, we loop through the ActiveSheet.ChartObjects collection to copy each chart as a picture into its own page in a newly created PowerPoint presentation.
Keep in mind that because this code will be run from Excel, you need to set a reference to the Microsoft PowerPoint Object Library. Again, you can set the reference by opening the Visual Basic Editor in Excel and selecting Tools⇒References. Scroll down until you find the entry Microsoft PowerPoint XX Object Library, where the XX is your version of PowerPoint. Select the check box next to the entry.
Sub CopyAllChartsToPresentation()
‘Step 1: Declare your variables
Dim PP As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide
Dim i As Integer
‘Step 2: Check for charts; exit if no charts exist
Sheets(“Slide Data”).Select
If ActiveSheet.ChartObjects.Count < 1 Then MsgBox “No charts existing the active sheet” Exit Sub
End If
‘Step 3: Open PowerPoint and create new presentation
Set PP = New PowerPoint.Application
Set PPPres = PP.Presentations.Add
PP.Visible = True
‘Step 4: Start the loop based on chart count
For i = 1 To ActiveSheet.ChartObjects.Count
‘Step 5: Copy the chart as a picture ActiveSheet.ChartObjects(i).Chart.CopyPicture _ Size:=xlScreen, Format:=xlPicture Application.Wait (Now + TimeValue(“0:00:1”))
‘Step 6: Count slides and add new slide as next available slide number ppSlideCount = PPPres.Slides.Count
Set PPSlide = PPPres.Slides.Add(SlideCount + 1, ppLayoutBlank) PPSlide.Select
‘Step 7: Paste the picture and adjust its position; Go to next chart PPSlide.Shapes.Paste.Select PP.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True PP.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True
Next i
‘Step 8: Memory Cleanup Set PPSlide = Nothing Set PPPres = Nothing
Set PP = Nothing
End Sub
1. Step 1 declares four variables: PP is an object variable that exposes the PowerPoint Application object; PPPres is an object variable that exposes the PowerPoint Presentation object; PPSlide is an object variable that exposes the PowerPoint Slide object; and i is used as a counter to help loop through the charts in the worksheet.
2. Step 2 does an administrative check to ensure that there are actually charts in the specified worksheet. If no charts are found, the macro exits the procedure with no further action.
3. Step 3 opens PowerPoint with an empty presentation. Note that we are setting the Visible property of the PowerPoint application to True. This ensures that we can see the action as the code runs.
4. Step 4 establishes how many times the macro will go through the procedure by capturing the number of charts in the worksheet. In other words, if the worksheet contains five charts, the code will loop five times. The macro starts the loop with one and keeps looping through the procedure until we hit the number of charts in the worksheet. The variable i ultimately represents the chart number we are currently on.
5. Step 5 uses the CopyPicture method to copy the chart as a picture. The variable i passes the actual chart number we are currently working with. The Application.Wait method tells the macro to pause for a second, allowing the clipboard to catch up with all the copying going on.
6. Step 6 adds a new slide to the presentation using the Add method of the Slide object. Note that we are using SlideCount+1 to specify the index number of the added slide. Because we
are looping through an unknown number of charts, we can't hard-code the index number for each slide. Using SlideCount+1 allows us to dynamically assign the next available number as the slide index. Note that in Step 6, we are using ppLayoutBlank, ensuring that the newly created slides start with a blank layout. The macro then takes an extra step here and actually sets focus on the slide. In other words, the code explicitly tells PowerPoint to select this slide, making it
active.
7. Step 7 pastes the picture into the active slide, centers the picture both horizontally and vertically, and then moves to the next chart.
8. Step 8 releases the objects assigned to your variables, reducing the chance of any problems caused by rouge objects that may remain open in memory.
Macro 101: Convert a Workbook into a
PowerPoint Presentation
This macro takes the concept of using Excel data in PowerPoint to the extreme. Open the sample workbook called Macro 101 Convert a Workbook into a PowerPoint Presentation.xlsm. In this workbook, notice that each worksheet contains its own data about a region. It's almost like each worksheet is its own separate slide, providing information on a particular region.
The idea here is that you can build a workbook in such a way that it mimics a PowerPoint presentation; the workbook is the presentation itself and each worksheet becomes a slide in the presentation. After you do that, you can easily convert that workbook into an actual PowerPoint presentation using a bit of automation.
With this technique, you can build entire presentations in Excel, where you have better analytical and automation tools. Then you can simply convert the Excel version of your presentation to a PowerPoint presentation.
How it works
Before you implement this macro in your workbook, you need to set a reference to the Microsoft PowerPoint Object Library. Again, you can set the reference by opening the Visual Basic Editor in Excel and selecting Tools⇒References. Scroll down until you find the entry Microsoft
PowerPoint XX Object Library, where the XX is your version of PowerPoint. Select a check box next to the entry.
Sub Macro101()
‘Step 1: Declare your variables
Dim pp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide Dim xlwksht As Excel.Worksheet Dim MyRange As String
Dim MyTitle As String
‘Step 2: Open PowerPoint, add a new presentation and make visible
Set pp = New PowerPoint.Application Set PPPres = pp.Presentations.Add pp.Visible = True
‘Step 3: Set the ranges for your data and title
MyRange = “A1:I27”
‘Step 4: Start the loop through each worksheet For Each xlwksht In ActiveWorkbook.Worksheets xlwksht.Select
Application.Wait (Now + TimeValue(“0:00:1”)) MyTitle = xlwksht.Range(“C19”).Value
‘Step 5: Copy the range as picture xlwksht.Range(MyRange).CopyPicture _ Appearance:=xlScreen, Format:=xlPicture
‘Step 6: Count slides and add new slide as next available slide number
SlideCount = PPPres.Slides.Count
Set PPSlide = PPPres.Slides.Add(SlideCount + 1, ppLayoutTitleOnly) PPSlide.Select
‘Step 7: Paste the picture and adjust its position PPSlide.Shapes.Paste.Select pp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True pp.ActiveWindow.Selection.ShapeRange.Top = 100
‘Step 8: Add the title to the slide then move to next worksheet
PPSlide.Shapes.Title.TextFrame.TextRange.Text = MyTitle
Next xlwksht
‘Step 9: Memory Cleanup pp.Activate
Set PPSlide = Nothing Set PPPres = Nothing Set pp = Nothing
End Sub
1. Step 1 declares six variables: PP is an object variable that exposes the PowerPoint Application object; PPPres is an object variable that exposes the PowerPoint Presentation object; PPSlide is an object variable that exposes the PowerPoint Slide object; xlwksht is an object variable that exposes the Worksheet object; MyRange is a string variable used to store
and pass a range name as a string; and MyTitle is a string variable used to store and pass a title for each slide.
2. Step 2 opens PowerPoint with an empty presentation. Note that we are setting the Visible property of the PowerPoint application to True. This ensures that we can see the action as the code runs.
3. Step 3 fills the MyRange variable with a string representing the range we want to capture as the slide content. We also fill the MyTitle variable with the value of cell C19. The value here becomes the title for the slide.
4. Step 4 starts the loop through each worksheet in the workbook. The loop stops when all
worksheets have been looped through. Note that we are using the Application.Wait method, telling the macro to pause for a second. This allows the chart to render completely before the range is copied.
5. Step 5 uses the CopyPicture method to copy our specified range as a picture.
6. Step 6 adds a new slide to the presentation using the Add method of the Slide object. Note that we are using SlideCount+1 to specify the index number of the added slide. Using SlideCount+1 allows us to dynamically assign the next available number as the slide index. Also note that we are using ppLayoutTitleOnly, ensuring our slide is created with a title text frame.
7. Step 7 pastes the picture into the active slide, centers the picture horizontally, and adjusts the picture vertically 100 pixels from the top margin.
8. Step 8 passes the MyTitle variable to apply text to the title text frame.
9. Step 9 releases the objects assigned to the variables, reducing the chance of any problems caused by rogue objects that may remain open in memory.
No comments:
Post a Comment