19. Automating Repetitive Functions Using VBA Macros
Every copy of Excel shipped since 1995 has included the powerful Visual Basic for Applications (VBA) lurking behind the grid. With VBA, you can do anything that you can do in the regular interface, and you can do it much faster. VBA shines when you have many repetitive tasks to undertake.
Learning to use macros is a good news/bad news proposition. The good news is that Microsoft Office provides a macro recorder that can write a macro as you work. The bad news is that it is not easy to record a macro that works consistently with any data set. To unleash the power of macros, you need to understand how to edit recorded macro code. You can then record a macro that is close to what you want and edit that macro to create something that runs the way you want it to work.
Checking Security Settings Before Using Macros
Before you can use macros, you have to take some positive steps to affirm that you want to record or run a macro.
To enable VBA security, follow these steps:
1. Select File, Options to open the Excel Options dialog.
2. Select the Customize Ribbon category. In the right-side list box, select the Developer tab check box.
3. Click OK to exit the Excel Settings dialog. You now have a Developer tab on the ribbon.
4. On the Developer tab, click Macro Security in the Code group. The Security dialog appears.
Note
In step 5, with is the operative word. You are choosing to disable macros and to display a notification to let you decide whether or not the macros should be enabled. Before choosing to enable macros, you can switch over to VBA and see what macros are in the workbook.
5. In the Security dialog, change the Macro Settings option to Disable All Macros with Notification. With this setting, Excel alerts you whenever you open a workbook that has macros attached.
6. When you open a document and get the warning that the document has macros attached, if this is a document that you wrote and you expect macros to be there, click Enable Content to enable the macros. Otherwise, make sure the workbook came from someone you trust.
Recording a Macro
Plan your macro before recording it by thinking through the steps you need to perform. If you need to fix many items in a worksheet, you might want to select the first item first. This way, the macro can perform an action on cells relative to the original selection.
To record a macro, follow these steps:
1. On the Developer tab, select Record Macro.
2. In the Record Macro dialog box, type a name for the macro. The name cannot contain spaces. For example, instead of using Macro Name, you need to use MacroName.
3. Choose whether you want to store the macro in the current workbook, a new workbook, or a special Personal Macro Workbook. The personal macro workbook is a special workbook designed to hold general-purpose macros that might apply to any workbook. If you are unsure, select to store the recorded macro in the current workbook.
4. Assign a shortcut key for the macro. Ctrl+J is a safe key because nothing is currently assigned to Ctrl+J. This shortcut key enables you to run the macro again.
5. Click OK to close the Record Macro dialog.
Caution
The alternative is an absolute recording. This method is extremely literal. The action of moving down three cells from A1 is recorded as “Select cell A4.” That action is extremely limited—it would work only when the macro is played back with the active cell in A1.
6. Turn on relative recording by clicking the Use Relative References icon in the Code group of the Developer tab. Relative recording records the action of moving a certain number of cells from the active cell.
7. Perform the actions you want to store in the macro
8. Click the Stop Recording button on the left side of the status bar at the bottom of the Excel window.
9. Save the workbook before testing the macro.
10. Test the macro playback by typing the shortcut key assigned in step 4.
Case Study: Macro for Formatting for a Mail Merge
Suppose that your co-worker has some names and addresses in Excel and she needs to do a mail merge in Word. Instead of teaching her how to do a mail merge, you offer to do the mail merge for her. In theory, this should take you a couple minutes. However, when the list of names arrives in the Excel worksheet, you realize the data is in the wrong format. In the Excel worksheet, the names are going down column A, as shown in Figure 19.1.
Figure 19.1 A simple task, such as doing a mail merge, is incredibly difficult when the data is in the wrong format.
To complete a mail merge successfully, the Excel worksheet should have fields for name, street address, and city+state+ZIP Code, as shown in Figure 19.2.
Before you start recording a macro, you need to think about how to break the task into easily repeatable steps.
It would be good to record a macro that can fix one name in the list. Assume that you start with the cell pointer on a person’s name at the beginning of the macro, as shown in Figure 19.1. The macro would need to perform these steps to fix one record and end up on the name of the second person in the list:
1. Press the down-arrow key to move to the address cell.
2. Press Ctrl+X to cut the address.
3. Press the up-arrow key and then the right-arrow key to move next to the name.
4. Press Ctrl+V to paste the address.
5. Press the left-arrow key once and the down-arrow key twice to move to the cell for city, state, and ZIP Code.
6. Press Ctrl+X to cut the city.
7. Press the up-arrow key twice and the right-arrow key twice to move to the right of the street cell.
8. Press Ctrl+V to paste the city.
9. Press the left-arrow key twice and the down-arrow key once to move to the now blank row just below the name.
10. Hold down the Shift key while pressing the down-arrow key twice to select the three blank rows.
11. Press Ctrl+- to invoke the Delete command. Press R and then Enter to delete the row.
When you run a macro that goes through these steps, Excel deletes the three blank rows, but the selection now contains the three cells that encompass the next record, as shown in Figure 19.3. Ideally, the macro should end with only the name selected. Press Shift+Backspace to reduce the selection to the active cell.
If the macro correctly performs all these steps, the first name and address are properly formatted. The blank rows left between the first and second names are deleted.
By making sure that the macro starts on a name and ends up on the next name, you allow the macro to be run repeatedly. If you assign this macro to the keyboard shortcut Ctrl+J, you can then hold down Ctrl+J and quickly fix records, one after the other.
How Not to Record a Macro: The Default State of the Macro Recorder
The default state of the macro recorder is a stupid state. If you recorded the preceding steps in the macro recorder, the macro recorder would take your actions literally. The English pseudocode for recording these steps would say this:
1. Move to cell A2.
2. Cut cell A2 and paste to cell B1.
4. Cut cell A3 and paste to cell C1.
5. Delete rows 2 through 4.
6. Select cell A2.
This macro works, but it works for only one record. After you’ve recorded this macro, your worksheet looks like the one shown previously in Figure 19.3.
When the default macro runs, it moves the name Vincent Alexander from cell A2 and pastes it on top of the address in cell B1. It then takes the address in cell A3 and pastes it on top of the city in cell C1. After that, it deletes rows 2, 3, and 4, removing the city and state. As shown in Figure 19.4, the macro provides the wrong result.
If you blindly ran this macro 100 times to convert 100 addresses, the macro would happily “eat” all 100 records, leaving you with just one record (and not even a correct record). To overcome this problem, use relative references, as discussed in the next section.
Relative References in Macro Recording
The key to recording a successful macro is to enable the Use Relative References setting in Excel. Had Microsoft made this the default, far more people would have success with their recorded macros. Locate the icon in the Code group on the Developer tab on the ribbon called Use Relative References. If you performed the steps described in the preceding section in relative recording mode, Excel would write code that does this:
1. Move down one cell.
2. Cut that cell.
3. Move up and over one cell and paste.
4. Move left and down two cells.
5. Cut that cell.
6. Move up and over two cells and paste.
7. Move left two cells, move down one cell, and delete three rows.
8. Move up and down one cell to select a single cell.
These steps are far more generic than those recorded using the default state of the macro recorder. These steps work for any record, provided that you started the macro with the cell pointer on the first cell that contains a name.
For this example, you need to record the entire macro with relative recording turned on.
Tip
Ninety-eight percent of the time you are recording macros, you should have Use Relative Reference turned on.
Starting the Macro Recorder
At this point, you have rehearsed the steps needed for a macro that puts data records into a format that is usable for a mail merge. After you make sure that the cell pointer is starting on the name in cell A1, you are ready to turn on the macro recorder.
You should not be nervous, but you need to perform the steps correctly. If you move the cell pointer in the wrong direction, the macro recorder happily records that for you and plays it back. It is annoying to watch the macro recorder play back your mistakes 100 times a day for the next 5 years. Therefore, follow these steps to create the macro correctly:
1. On the Developer tab, click the Record Macro icon from the Code group. The Record Macro dialog appears, as shown in Figure 19.5.
2. Excel suggests giving this macro the unimaginative name Macro1. Use any name you want, up to 64 characters and without spaces. For this example, name the macro FixOneRecord. Choose a shortcut key for the macro. The shortcut key is important. Because you have to run this macro once for each record in the present example, you might choose something like Ctrl+A, which is easy to press.
3. Make a selection from the Store Macro In drop-down. You have the option of storing the macro in this workbook, in a new workbook, or in the personal macro workbook. If this is a general-purpose macro that you will use every day on every file, it makes sense to store the macro in the personal macro workbook. However, because this macro will be used just to solve a current problem, store it in the current workbook.
4. Fill in a description if you think you will be using this macro long enough to forget what it does. When you are done making selections on the Record Macro dialog (refer to Figure 19.5), click OK. The Record Macro icon changes to a Stop Recording icon.
Note
Keep in mind that assigning a macro to Ctrl+A overwrites the usual action of that keystroke (selecting all cells). If you are writing a macro that will be used all day, every day, you should use a shortcut key that is not assigned to existing shortcuts, such as Ctrl+J. Although most of the letter keys are already assigned to a shortcut, you can always use the shifted shortcut keys. To assign a macro to Ctrl+Shift+A, press Shift+A into the shortcut field.
5. Click the Use Relative References icon in the Developer tab. The icon is highlighted.
6. Press the down-arrow key to move to the address cell.
7. Press Ctrl+X to cut the address.
8. Press the up-arrow key and then the right-arrow key to move next to the name.
9. Press Ctrl+V to paste the address.
10. Press the left-arrow key once and the down-arrow key twice to move to the cell for city, state, and ZIP Code.
11. Press Ctrl+X to cut the city.
12. Press the up-arrow key twice and the right-arrow key twice to move to the right of the street cell.
13. Press Ctrl+V to paste the city.
14. Press the left-arrow key twice and the down-arrow key once to move to the now-blank row just below the name.
15. Hold down the Shift key while pressing the down-arrow key twice to select the three blank rows.
16. Press Ctrl+- to invoke the Delete command. Press R and then Enter to delete the row.
Press the up-arrow key and the down-arrow key. Moving the cell pointer up a cell and then back to the name causes only a single cell to be selected.
17. When you are done, click the Stop Recording button.
This macro successfully fixes any record in the database, provided the cell pointer is on the cell containing the name when you run the macro. Try playing back the macro by pressing Ctrl+A to fix one record. To fix all records, hold down Ctrl+A until all records are fixed.
1. Click the Macros icon in the Code group of the Developer tab. The Macro dialog appears, as shown in Figure 19.6.
2. Select your macro and click the Run button. The macro fixes the first record.
Tip
Displaying the Macro dialog every time you want to run a macro is not efficient. Try running the macro with a shortcut key.
3. Press Ctrl+A to run the FixOneRecord macro. The second record is fixed.
4. Hold down Ctrl+A to repeatedly run the macro. In a matter of seconds, all 100 names are in a format ready to use in a mail merge.
This example represents an ideal use of a one-time macro. The process of fixing the data someone gave you involved mindless repetition. If there had just been four records, you could have mindlessly fixed them. However, because there were 100 records in this example, it made sense to record a macro and then run the macro repeatedly to solve the problem. You recorded the entire macro in relative mode, and you did not have to edit the macro. You probably run into a few situations a week in which a quick one-time-use macro would make your job easier.
Caution
When you run a macro, there is no undo. Therefore, you should save a file before running a new macro on it. It is easy to have accidentally recorded the macro in default mode instead of relative mode. You need to save the macro so that you can easily go back to the current state in case something does not work right.
Everyday-Use Macro Example: Formatting an Invoice Register
The macro recorder does not solve all tasks perfectly, however. Many times, you need to record a macro and then edit the recorded code to make the macro a bit more general. This example demonstrates how to do that.
In this example, a system writes out a file every day. This file contains a list of invoices generated on the previous day. The file predictably contains six columns—NAME, DATE, INVOICE, REVENUE, SALES TAX, and TOTAL—as shown in Figure 19.7. The file also looks horrible: The columns are the wrong width, there is no title, and there isn’t a Total row at the bottom. You would like a macro that opens this file, makes the columns wider, adds a total row, adds a title, makes the headings bold, and saves the file with a new name. The following sections describe how to create this macro.
Using the Ctrl+Down-Arrow Key to Handle a Variable Number of Rows
One of the inherent problems with this example is that your file will have a different number of rows every day. If you record a macro for this today to add totals in row 16, it will not work tomorrow, when you might have 22 invoices. The solution is to use the Ctrl+Down-Arrow key to navigate to the last row of your data.
You use Ctrl and any arrow key to move to the edge of a contiguous range of data. In Figure 19.7, if you press Ctrl+Down Arrow, you would move to cell A15. From cell A15, press Ctrl+Up Arrow to move back to cell A1. You can press the Ctrl+Right Arrow to move to cell F1.
You can also use the Ctrl+arrow key to jump over an abyss of empty cells. If you are currently at the edge of a range (for example, cell F1) and press Ctrl+Right Arrow, Excel jumps over all the blank cells and stops either at the next nonblank cell in row 1 or at the right edge of the worksheet, cell XFD1.
Making Sure You Find the Last Record
You might be tempted to start in cell A1, press Ctrl+Down Arrow, and then press the down-arrow key again to move to the first blank row in the data. However, that is not the safest method. This data file is coming from another system. Undoubtedly, one day a cashier will find a way to enter an order without a customer name. She will happen upon the accidental keystroke combination that causes the cash register to allow an order without a customer name. On that day, the Ctrl+Down-Arrow combination will stop at the wrong row and add totals in the middle of your data set. Thus, it is safer to use the Go To dialog to move to A1048576 and use Ctrl+Up Arrow to find the last record.
Recording the Macro in a Blank Workbook
Open a blank workbook, and save it with a filename such as MacroToImportInvoices.xlsm. You can record your macro in this blank workbook and save it. Then, each day, you can open the macro workbook. The macro will handle opening the data file and formatting it. Go through these steps while the macro recorder is running:
1. Open the file.
2. Press the F5 key to display the Go To dialog.
3. Go to cell A1048576 (the last cell in column A).
4. Turn on relative recording by clicking Use Relative References in the Developer tab. You use relative recording because you want to record the action of jumping to the last row, and that row will be in a different location each day.
5. Press End+Up Arrow to move to the last row that contains data.
6. Press the down-arrow key to move to the blank row below the last row for data.
7. Type the word Total.
8. Move right three cells.
9. Type the formula =SUM(D$2:D15). Press Ctrl+Enter to stay in the current cell. Be sure to include a single dollar sign to lock the start of the range to row 2. Do not use the AutoSum icon to add this formula!
10. Drag the fill handle to the right two cells to copy the formula from D to E and F.
11. Select all cells with Ctrl+Shift+Home.
12. Select Home, Format, AutoFit Column Width to make all the columns wide.
13. Turn off relative recording by clicking the Use Relative References icon in the Developer tab. At this point, you always want to return to row 1 to format the headings. You don’t want the recorder to record “Move up 15 rows.” You always want to go to row 1.
14. Select row 1.
15. Open the Cell Styles gallery on the Home tab and choose Heading 4. Insert two rows using your favorite method. One method is to press Alt+I+R twice.
17. Type the formula ="Invoices for "&TEXT(B4,"mmmm d, yyyy"). Press Ctrl+Enter to accept the formula and stay in the cell.
18. Open the Cell Styles gallery and choose Title.
19. Use Save As to save the file with a new name to reflect today’s date.
20. Click the Stop Recording button.
In this macro example, you use a mix of relative and absolute recording to produce a macro that handles any number of rows of data. The macro will be somewhat useful, with two annoying limitations:
If you saved the file as 2018-Feb-17Invoices.xls, the macro will attempt to overwrite that file every day.
The macro will always want to open the same file. This is great if your cash register system always produces a file with the same name in the same folder. However, you might want the option to browse for a different file each day.
Both of these changes require you to edit the recorded macro. Before editing the macro, here is a look at how to open the Visual Basic Editor and at the syntax of VBA. To see the code to finish this example, refer to “Customizing the Everyday-Use Macro Example: GetOpenFileName and GetSaveAsFileName,” later in this chapter.
Editing a Macro
To edit a macro, follow these steps:
1. Open the Macros dialog by pressing Alt+F8. The Macro dialog appears.
2. In the Macro dialog, select your macro and click Edit (refer to Figure 19.6). The Visual Basic Editor (VBE) is launched.
A number of panes are available in the VBE, but it is common to have three particular panes displayed, as shown in Figure 19.8:
Code pane—The actual lines of the macro code are in the Code pane, which is usually on the right side of the screen.
Project Explorer pane—This pane, which is in the upper left, shows every open workbook. Within the workbooks, you can see objects for each worksheet, an object for this workbook, and one or more code modules. If you cannot see the Project pane, press Ctrl+R or select View, Project Explorer to open it.
Properties pane—This pane, in the lower left, is useful if you design custom dialogs. You can Press F4 to display the Properties pane.
Understanding VBA Code—An Analogy
In the 1980s and early 1990s, many people going through school were exposed to an introductory class in a programming language called BASIC. Although Excel macros are written in Visual Basic for Applications, the fact that both languages contain the word basic does not mean that BASIC and VBA are the same or even similar. BASIC is a procedural language. VBA is an object-oriented language. In VBA, the focus is on objects. This can make VBA confusing to someone who has learned to program in BASIC.
The syntax of VBA consists of objects, methods, collections, arguments, and properties. If you have never programmed in an object-oriented language, these terms, and the VBA code itself, might seem foreign to you. The following sections compare these five elements to parts of speech:
An object is similar to a noun.
A method is similar to a verb.
A collection is similar to a plural noun.
An argument is similar to an adverb.
A property is similar to an adjective.
Each of the following sections describes the similarity between the VBA element and a part of speech. These sections also describe how to recognize the various elements when you examine VBA code.
Comparing Object.Method to Nouns and Verbs
As an object-oriented language, the objects in VBA are of primary importance. Think of an object as any noun in Excel. Examples of objects are a cell, a row, a column, a worksheet, and a workbook.
A method is any action that you can perform on an object. This is similar to a verb. You can add a worksheet. You can delete a row. You can clear a cell. In Excel VBA, words such as Add, Delete, and Clear are methods.
Objects and methods are joined by a period, although in VBA, people pronounce the period as dot. The object is first, followed by a dot, followed by the method. For example, object.method, which is pronounced “object-dot-method,” indicates that the method performs on the object. This is confusing because it is backward from the way English is spoken. If everyone spoke VBA instead of English, we would use sentences such as “car.drive” and “dinner.eat.” When you see a period in VBA, it usually means that the word after the period is acting upon the word to the left of the period.
Comparing Collections to Plural Nouns
In an Excel workbook, there is not a single cell but rather a collection of many cells. Many workbooks contain several worksheets. Anytime you have multiple instances of a certain object, VBA refers to this as a collection.
The s at the end of an object may seem subtle, but it indicates you are dealing with a collection instead of a single object. Whereas ThisWorkbook refers to a single workbook, Workbooks refers to a collection of all the open workbooks. This is an important distinction to understand.
You have two main ways to refer to a single worksheet in a collection of worksheets:
By its number, such as Worksheets(1)
By its name, such as Worksheets("Jan")
Comparing Parameters to Adverbs
When you invoke a command such as the Save As command, a dialog pops up, and you have the opportunity to specify several options that change how the command is carried out. If the Save As command is a method, the options for it are parameters. Just as an adverb modifies a verb, a parameter modifies a method.
Most of the time, parameters are recorded by using the syntax ParameterName:=ParameterValue.
One of the reasons that recorded code gets to be so long is that the macro recorder makes note of every option on the dialog, whether you select it or not.
Consider this line of code for SaveAs:
ActiveWorkbook.SaveAs Filename:="C:Something.xlsx", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
In this recorded macro for SaveAs, the recorder noted parameter values for Filename, FileFormat, and CreateBackup. Figure 19.9 shows the Save As dialog. Filename and FileFormat are evident on the form. However, where is the option for Create Backup?
In the bottom center of the dialog is a Tools drop-down. If you select Tools, General Options, you see a dialog with four additional options, as shown in Figure 19.10. Even though you did not touch this Save Options dialog, Excel recorded the Backup value from the dialog for you.
Figure 19.10 Even though you did not touch the Save Options dialog, the macro recorder recorded the values from it.
Parameters have some potentially confusing aspects. Most of the time, there is a space following the method and then a list of one or more ParameterName:=ParameterValue constructs, separated by a comma and a space. However, there are a few exceptions:
If the result of the method is acted upon by another method, the list of parameters is enclosed in parentheses, and there is no space after the method name. One example is when you add a shape to a worksheet and then Excel selects the shape. The code to insert the shape uses the AddShape method and five named parameters:
ActiveSheet.Shapes.AddShape Type:=msoShapeRectangle, _
Left:=60, Top:=120, _
Width:=100, Height:=100
The macro recorder will record the process of adding the shape and then selecting the shape. Because the .Select method is acting upon the result of the .AddShape method, you see the parameters for the AddShape method surrounded by parentheses:
ActiveSheet.Shapes.AddShape(Type:=msoShapeRectangle, _
Left:=60, Top:=120, _
Width:=100, Height:=100).Select
When you use the parameter name, you can specify the parameters in any sequence you like. The Help topic for the method reveals the official default order for the parameters. If you specify the parameters in the exact sequence specified in Help, you are allowed to leave off the parameter names. However, this is a poor coding practice. Even if you have memorized the default order for the parameters, you cannot assume that everyone else reading your code will know the default order. The problem is that sometimes the macro recorder will record code in this style. For example, here is the actual line of code that was recorded when I added a shape to a worksheet:
ActiveSheet.Shapes.AddShape(msoShapeRectangle, 60, 120, 100, 100).Select
It would be difficult to figure out this line of code without looking at the Help topic. To access Help, click anywhere in the method of AddShape and then press the F1 key. The Help topic reveals that the correct parameter order is Type, Left, Top, Width, Height.
Note
Keep in mind that parameters are like adverbs. They generally appear with a Parameter Name:=Parameter Value construct. However, there are times when the macro recorder lists the parameter values in their default order, without the parameter names or the :=.
Comparing Adjectives to Properties
The final construct in VBA is the adjective used to describe an object. In VBA, adjectives are called properties. Think about a cell in Excel with a formula in it. The cell has many properties. These are some of the most popular properties:
Value (the value shown in the cell)
Formula (the formula used to calculate Value)
Font Name
Font Size
Font Color
Cell Interior Color
In VBA, you can check on the value of a property, or you can set the property to a new value. To change several cells to be bold, for example, you would change their Bold property to True:
Selection.Font.Bold = True
You can also check to see whether a property equals a certain value:
If Selection.Value = 100 then Selection.Font.Bold = True
Properties are generally used with the dot construct, and they are almost always followed by =, as contrasted with the := used with parameters (for example, PropertyName = value).
Using the Analogy While Examining Recorded Code
When you understand that a period generally separates an object from a method or a property, you can start to make sense of the recorded code.
For example, the following line performs the Open method:
Workbooks.Open Filename:="C:Invoices.xls"
In this example, the Filename parameter is shown with := after the parameter name. This first line in the following example performs the Select method on one particular member of the Rows collection:
Rows("1:1").Select
Selection.Font.Bold = True
Tip
In the Excel user interface, you generally have to select a cell before you can change something in it. In a macro, there is no need to select something first. For example, you can replace the two lines in the preceding example with this single line of code: Rows("1:1").Font.Bold = True.
The second line then sets the Bold property of the Font property of the selection to True. Using these two lines of code is equivalent to selecting row 1 and clicking the Bold icon. You notice that one property, such as Font, can have subproperties, such as Bold and Italic.
Using Simple Variables and Object Variables
The macro recorder never records a variable, but you can add variables to a macro when you edit the code. Suppose that you need to do a number of operations to the row where the totals will be located. Instead of repeatedly going to the last row in the spreadsheet and pressing End+Up Arrow, you can assign the row number to a variable:
FinalRow = Range("A1048576").End(xlup).Row
TotalRow = FinalRow + 1
The words FinalRow and TotalRow are variables that each hold a single value. If you have data in rows 2 through 25 today, FinalRow will hold the value 25, and TotalRow will hold the value 26. This enables you to use efficient code, such as the following:
Range("A" & TotalRow).Value = "Total"
Range("C" & TotalRow).Formula = "=SUM(C2:C"& TotalRow & ")"
Range("D" & TotalRow).Formula = "=SUM(D2:D"& TotalRow & ")"
Range("E" & TotalRow).Formula = "=SUM(E2:E"& TotalRow & ")"
VBA also offers a powerful variable called an object variable. An object variable can be used to represent any object such as a worksheet, chart, or cell. Whereas a simple variable holds one value, an object variable holds values for every property associated with the object.
Object variables are declared using the Dim statement and then assigned using the Set statement:
Dim WSD as worksheet
Set WSD = Worksheets("Sheet1")
Using object variables offers the following advantages:
It is easier to refer to WSD than to ActiveWorkbook.Worksheets("Sheet1").
If you define the object variable with a DIM statement at the beginning of the macro, as you type new lines of code, the VBE’s AutoComplete feature shows a list of valid methods and properties for the object.
Using R1C1-Style Formulas
If you are a history buff of technology, you might know that VisiCalc was the first spreadsheet program for PCs. When Dan Bricklin and Bob Frankston invented VisiCalc, they used the A1 style for naming cells. In those early days, VisiCalc had competitors such as SuperCalc and a Microsoft program called MultiPlan. This early Microsoft spreadsheet used the notation of R1C1 to refer to cell A1. The cell that we know today as E17 would have been called R17C5, for row 17, column 5.
In 1985, Microsoft launched Excel version 1.0 for the Macintosh. Excel originally continued to use the R1C1 style of notation. During the next 10 years, Excel and Lotus 1-2-3 were locked in a bitter battle for market share. Lotus was the early leader, and it had adopted the A1 notation style familiar to VisiCalc customers. To capture more market share, Microsoft allowed Excel to use either A1-style notation or R1C1-style notation. Even today, in Excel 2016, you can turn on R1C1-style notation by selecting File, Options, Formulas, R1C1 Reference Style. In R1C1 reference style, column letters A, B, C are replaced with column numbers 1, 2, 3. Hardly anyone uses R1C1 reference style today; however, the macro recorder always records formulas in R1C1 style.
In R1C1 notation, the reference RC refers to the current cell. You can modify RC by adding a particular row number or column number. For example, R2C refers to the cell in row 2 of the current column. RC1 refers to the cell in this row that is in column 1.
If you put a row number or column number in square brackets, it refers to a relative number of cells from the current cell. If you have a formula in cell D16 and use the reference R[1]C[–2], you are referring to the cell one row below D16 and two columns to the left of D16, which would be cell B17.
You are probably wondering why the macro recorder uses this arcane notation style when recording formulas. It turns out that this style is fantastic for formulas. Whereas a column of formulas in A1 style will have a different formula in each cell, the same column of formulas in R1C1 style will be identical down the column. For example, enter a formula of =D2+E2 in cell F2. When you copy F2 to F3, Excel changes the references of E2 and D2 to be E3 and D3.
Now look at these same formulas in R1C1 style, as shown in Figure 19.11. Every formula in E2:E15 is identical. This makes sense because the formula is saying, “Add the sales tax one cell to the left of me to the merchandise amount that is two cells to the left of me.” Every formula in F2:F15 is identical; even the total formulas in D16:F16 are identical.
If you are forced to use A1-style formulas in a macro, you might try to enter the formula in cell F2 and then copy the formula from F2 to the remaining cells:
Range("F2").Formula = "=D2+E2"
Range("F2").Copy Destination:=Range("F3:F15")
On the other hand, you can enter all the formulas in one line of code when using R1C1-style formulas:
Range("F2:F15").FormulaR1C1 = "=RC[-2]+RC[-1]"
Tip
Although the macro recorder always records formulas in R1C1 style, you are allowed to write the macros using regular formulas. Change the FormulaR1C1 property to Formula. The following two lines of code are equivalent:
Range("F2:F15").FormulaR1C1 = "=RC[-2]+RC[-1]"
Range("F2:F15").Formula = "=D2-E2"
Fixing AutoSum Errors in Macros
Probably the most important reason to understand R1C1 formulas is to make sure that the macro recorder recorded the proper formula. This is important because the macro recorder does not do a good job of recording the intent of the AutoSum button. If your data set has numbers in D2:D15 today, pressing AutoSum from cell D16 will record the following line of macro code:
Selection.FormulaR1C1 = "=SUM(R[-14]C:R[-1]C)"
This formula adds a range from 14 rows above the selection to the cell just above the selection. This works only on days when you have exactly 14 rows of data. This is one of the most annoying bugs in a macro.
It is annoying because this type of logic error will not cause an actual error. If you run this macro on the invoice file you receive tomorrow that contains 20 invoices, the macro will happily total only the last 14 invoices instead of all 20. This means that you could distribute this report with a wrong total for several days before someone realizes that something is amiss.
However, you can correct this formula. You know that you have headings in row 1 and that the first invoice will appear in row 2. You need the macro to sum from row 2 to the row just above the current cell. Therefore, you need to change the formula to this:
Selection.FormulaR1C1 = "=SUM(R2C:R[-1]C)"
Customizing the Everyday-Use Macro Example: GETOPENFILENAME and GETSAVEASFILENAME
The everyday-use macro you recorded earlier in this chapter for formatting an invoice register is hard-coded to always open the same file and always save with the same filename. To make the macro more general, you can allow the person running the macro to browse for the file each morning and to specify a new filename during the Save As. Excel offers a straightforward way to display the File Open or File Save As dialog. Here is the code you need to use:
FileToOpen = Application.GetOpenFileName( _
FileFilter:="Excel Files,*.xl*", _
Title:="Select Today's Invoice File")
Note that this code displays the File Open dialog and allows a file to be selected. When you click Open, the dialog assigns the filename to the variable. It does not actually open the file. You then need to open the file specified in the variable:
Workbooks.Open Filename:=FileToOpen
When you want to ask for the filename to use in saving the file, use this code:
NewFileName = Application.GetSaveAsFilename( _
Title:="Select File Name for Today")
ActiveWorkbook.SaveAs Filename:=NewFileName, _
FileFormat:=xlOpenXMLWorkbookMacroEnabled
The following macro is the final macro to use each day:
Sub ImportInvoicesFixed()
' ImportInvoices Macro
' With Changes
FileToOpen = Application.GetOpenFileName( _
FileFilter:= _
"Excel files (*.xls;*.xlsb;*.xlsx;*.xlsm)" & _
",*.xls;*.xlsb;*.xlsx;*.xlsm)", _
Title:="Select Today's Invoice File")
Workbooks.Open Filename:=FileToOpen
Application.Goto Reference:="R1048576C1"
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "Total"
ActiveCell.Offset(0, 3).Range("A1").Select
Selection.FormulaR1C1 = "=SUM(R2C:R[-1]C)"
Selection.AutoFill Destination:=ActiveCell.Range("A1:C1"), Type:= _
xlFillDefault
ActiveCell.Range("A1:C1").Select
Range(Selection, Cells(1)).Select
Selection.Columns.AutoFit
Rows("1:1").Select
Selection.Style = "Heading 4"
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Range("A1").Select
Selection.FormulaR1C1 = "=""Invoices for ""&TEXT(R[3]C[1],""mmmm d, yyyy"")"
Selection.Style = "Title"
NewFileName = Application.GetSaveAsFilename( _
Title:="Select File Name for Today")
ActiveWorkbook.SaveAs Filename:=NewFileName, _
FileFormat:=xlOpenXMLWorkbookMacroEnabled
End Sub
Of the 22 lines in the macro, you added two lines and corrected two lines. This is typical because between 10% and 20% of a recorded macro generally needs to be adjusted.
From-Scratch Macro Example: Loops, Flow Control, and Referring to Ranges
Suppose you work for a company that sells printers and scanners to commercial accounts. When you sell a piece of hardware, you also try to sell a service plan for that hardware. Customers in your state are taxed. Your accounting software provides a daily download that looks like columns A:D in Figure 19.12.
Figure 19.12 Your accounting software groups all hardware, service, and tax amounts into a single column.
You want to create a macro that examines each row in the data set and carries out a different action, based on the value in column C. You will probably want to write this macro from scratch. The following sections describe how to do this.
Finding the Last Row with Data
The recorded macro examples discussed earlier in this chapter suggested going to the last cell in column A and then pressing End followed by the up-arrow key to find the last row with data in column A.
In legacy versions of Excel, this would be accomplished with this code:
FinalRow = Range("A65536").End(xlUp).Row
Tip
Use XLUP as the argument for END. The lowercase “l” used in the code is often seen as the numeral 1. Using x1Up instead of xlUp causes an error.
This command became more complicated in Excel 2007. The last row in the worksheet is either 1048576 or 65536, depending on whether the workbook is in compatibility mode. The solution is to use Rows.Count, which is shorthand for Application.Rows.Count. This solution returns the total number of rows available in the current worksheet. Note that this property returns 65,536 in compatibility mode and 1,048,576 in regular mode.
The following line of code finds the last row in column A with a nonblank value:
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
Looping Through All Rows
The loop most commonly used in VBA is a For-Next loop. This is identical to the loop that you might have learned about in a BASIC class.
In this example, the loop starts with a For statement. You specify that on each pass through the loop, a certain variable will change from a low value to a high value. This simple macro will run through the loop 10 times. On the first pass through the loop, the variable x will be equal to 1. The two lines inside the loop will assign the value 1 to cells A1 and B1. When the macro encounters the Next x line, it returns to the start of the loop, increments x by 1, and runs through the loop again. The next time through the loop, the value of x is 2. Cell A2 is assigned the number 2, and cell B2 shows 4, which is the square of 2. Eventually, x will be equal to 10. At the Next x line, the macro will allow the loop to finish. The following is the code for this macro:
Sub WriteSquares()
For x = 1 To 10
Cells(x, 1).Value = x
Cells(x, 2).Value = x * x
Next x
End Sub
After you run this macro, you have a simple table that shows the numbers 1 through 10 and their squares, as shown in Figure 19.13.
After a loop is written, it can be adjusted easily. For example, if you want a table showing all the squares from 1 to 100, you can adjust the For x = 1 to 10 line to be For x = 1 to 100.
There is an optional clause in the For statement called the step value. If no step value is shown, the program moves through the loop by incrementing the variable by one each time through the loop. If you wanted to check only the even-numbered rows, you could change the loop to be For x = 2 to 100 in step 2.
If you are going to be optionally deleting rows from a range of data, it is important to start at the bottom and proceed to the top of the range. You would use –1 as the step value:
For x = 100 to 1 step –1
Referring to Ranges
The macro recorder uses the Range property to refer to a particular range. You might see the macro recorder refer to ranges such as Range("B3") and Range("W1:Z100").
The loop code shown in the preceding section emulates this style of referring to ranges. On the third time through the loop, this line of code would refer to cell B3:
Range("B" & x).value = x * x
However, how would you handle looping through each column? If you want to continue using the Range property, you need to jump through some hoops to figure out the letter associated with column 5:
For y = 1 to 26
ThisCol = Chr(64+y)
Range(ThisCol & 1).value = ThisCol
Next y
This method works fine if you are using 26 or fewer columns. However, if you need to loop through all the columns out to column XFD, you will spend all day trying to write the logic to assign the column label WMJ to column 15896.
The solution is to use the Cells property instead of the Range property. Cells requires you to specify a numeric row number and a numeric column number. For example, cell B3 is specified as follows:
Cells(3, 2)
If you need to refer to a rectangular range, you can use the Resize property. Resize requires you to specify the number of rows and the number of columns. For example, to refer to W1:Z100, use this:
Cells(1, 23).Resize(100, 4)
It is difficult to figure out that this refers to W1:Z100, but it enables you to loop through rows or columns.
You can use the following code to make every other column bold:
For y = 1 to 100 step 2
Cells(1, y).Resize(200, 1).Font.Bold = True
Next y
Combining a Loop with FinalRow
Earlier in this chapter, you learned how to use the Ctrl+Up Arrow to find the final row in a data set. After finding the final row in the data set and assigning it to a variable, you can specify that the loop should run through FinalRow:
FinalRow = Cells(Rows.Count, 1).End(xlUp).row
For x = 2 to FinalRow
' Perform some action
Next x
Making Decisions by Using Flow Control
Flow control is the capability to make decisions within a macro. The following sections describe two commonly used flow control constructs: If-End If and Select Case.
Using the If-End If Construct
Suppose you need a macro to delete any records that say Sales Tax. You could accomplish this with a simple If-End If construct:
If Cells(x, 4).Value = "Sales Tax" Then
Rows(x).Delete
End If
This construct always starts with the word If, followed by a logical test, followed by the word Then. Every line between the first line and the End If line is executed only if the logical test is True.
Now suppose that you want to enhance the macro so that any other amounts that contain service plan revenue are moved to column F. To do this, you use the ElseIf line to enter a second condition and block of lines to be used in that condition:
If Cells(x, 4).Value = "Sales Tax" Then
Cells(x, 1).EntireRow.Delete
ElseIf Cells(x, 4).Value = "Service Plan" Then
Cells(x, 5).Cut Destination:=Cells(x, 6)
End If
You could continue adding ElseIf statements to handle other situations. Eventually, just before the End If, you could add an Else block to handle any other condition you have not thought about.
Using the Select Case Construct
If you reach a point where you have many ElseIf statements all testing the same value, it might make sense to switch to a Select Case construct. For example, suppose you want to loop through all the records to examine the product in column C. If column C contains a printer, you want to move the amount in column D to a new column E. Scanner revenue should go to a new column F. Service plans go to a new column H. Sales tax goes to a new column I. You should also handle the situation when something is sold that contains none of those products. In that case, you would move the revenue to a new column G.
The construct begins with Select Case and then the value to check. The construct ends with End Select, which is similar to End If.
Each subblock of code starts with the word Case and one or more possible values. If you needed to check for Printer or Printers, you would enclose each in quotes and separate them with a comma.
After checking for all the possible values you can think of, you might add a Case Else subblock to handle any other stray values that might be entered in column C.
The following code checks to see what product is in column C. Depending on the product, the program copies the revenue from column D to a specific column.
Select Case Cells(x, 3).Value
Case "Printer", "Printers"
Cells(x, 4).Copy Destination:=Cells(x, 5)
Case "Scanner", "Scanners"
Cells(x, 4).Copy Destination:=Cells(x, 6)
Case "Service Plan"
Cells(x, 4).Copy Destination:=Cells(x, 8)
Case "Sales Tax"
Cells(x, 4).Copy Destination:=Cells(x, 9)
Case Else
' Something unexpected was sold
Cells(x, 4).Copy Destination:=Cells(x, 7)
End Select
Putting Together the From-Scratch Example: Testing Each Record in a Loop
Using the building blocks described in the preceding sections, you can now write the code for a macro that finds the last row, loops through the records, and copies the total revenue to the appropriate column. Now you need to add new headings for the additional columns.
The macro should use the End property to locate the final row and prefill columns E through I with zeros. Next, it should loop from row 2 down to the final row. For each record, the revenue column should be moved to one of the columns. At the end of the loop, the program alerts you that the program is complete, using a MsgBox command. The following is the complete code of this macro:
Sub MoveRevenue2()
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
Range("E2", Cells(FinalRow, 9)).Value = 0
For x = 2 To FinalRow
Select Case Cells(x, 3).Value
Case "Printer", "Printers"
Cells(x, 4).Copy Destination:=Cells(x, 5)
Case "Scanner", "Scanners"
Cells(x, 4).Copy Destination:=Cells(x, 6)
Case "Service Plan"
Cells(x, 4).Copy Destination:=Cells(x, 8)
Case "Sales Tax"
Cells(x, 4).Copy Destination:=Cells(x, 9)
Case Else
' Something unexpected was sold – Accessory?
Cells(x, 4).Copy Destination:=Cells(x, 7)
End Select
Next x
MsgBox "Macro complete"
End Sub
Tip
An alternative syntax of the Range property is to specify the top-left and bottom-right cells in the range, separated by a comma. In the macro described here, for example, you know you want to fill from cell E2 to the last row in column I. You can describe this range as follows:
Range("E2", Cells(FinalRow, 9))
This syntax is sometimes simpler than using Cells() and Resize().
After you run this macro, you see that the revenue amounts have been copied to the appropriate columns, as shown in Figure 19.14.
A Special Case: Deleting Some Records
If a loop is conditionally deleting records, you will run into trouble if it is a typical For-Next loop. Suppose you want to delete all the sales tax records, as follows:
Sub ThisWontWork()
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
For x = 2 To FinalRow
If Cells(x, 3).Value = "Sales Tax" Then
Cells(x, 1).EntireRow.Delete
Else
Cells(x, 5).Value = "Checked"
End If
Next x
End Sub
Consider the data in Figure 19.15. The first time through the loop, x is equal to 2. Cell C2 does not contain sales tax, so cell E2 has the word checked. A similar result occurs for rows 3 and 4. The fourth time through the loop, cell C5 contains sales tax. The macro deletes the tax in row 5. However, Excel then moves the old row 6 up to row 5, as shown in Figure 19.16. The next time through the loop, the program inspects row 6, and the data that is now in row 5 will never be checked.
Figure 19.16 The old row 6 data moves up to occupy the deleted row 5. However, the macro blindly moves on to check row 6 next. The printer that moved to row 5 never gets checked.
The macro succeeds in deleting tax. However, several rows were not checked, and several extra blank rows at the bottom were checked needlessly, as shown in Figure 19.17.
The solution is to have the loop run backward. You need to start at the final row and proceed up through the sheet to row 2. When the macro deletes tax in row 31, it can then proceed to checking row 30, knowing that nothing has been destroyed (yet) in row 30 and above.
To reverse the flow of the loop, you have to tell the loop to start at the final row, but you also have to tell the loop to use a step value of -1. The start of the loop would use this line of code:
For x = FinalRow to 2 Step -1
The macro you need here represents a fairly common task: looping through all the records to do something conditionally to each record.
The following macro correctly deletes all the sales tax records:
Sub DeleteTaxOK()
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
For x = FinalRow To 2 Step -1
If Cells(x, 3).Value = "Sales Tax" Then
Cells(x, 1).EntireRow.Delete
Else
Cells(x, 5).Value = "Checked"
End If
Next x
End Sub
For the example described here, the macro recorder would be almost no help. You would have to write this simple macro from scratch. However, it is a powerful macro that can simplify tasks when you have hundreds of thousands of rows of data.
Combination Macro Example: Creating a Report for Each Customer
Many real-life scenarios require you to use a combination of recorded code and code written from scratch. For example, Figure 19.18 shows a data set with all your invoices for the year. In this case, suppose you would like to produce a workbook for each customer that you can mail to the customer.
One way to handle this task would be to use an advanced filter to get a list of all unique customers in column A. You would then loop through these customers, applying an AutoFilter to the data set to see only the customers that match the selected customer. After the data set is filtered, you can select the visible cells only and copy them to a new workbook. Then you can save the workbook with the name of the customer and return to the original workbook.
You can start by creating a blank procedure with comments to spell out the steps in the preceding paragraphs. Then you add code for the loop and other simple tasks, such as copying the selection to a new workbook. Whenever you encounter a step for which you have never written code, you can leave a comment with question marks. This enables you to go back and record parts of the process to finish the macro.
It is common to indent each line of code with four spaces. Any lines of code inside an If-EndIf block or inside a For-Next loop are indented an additional four spaces. If you have typed a line of code that is indented eight spaces and then press Enter at the end of the line of code, the VBE automatically indents the next line to eight spaces. Each press of the Tab key indents by an additional four spaces. Pressing Shift+Tab removes four spaces of indentation. Although four is the default number of spaces for a tab, you can change this to any number of spaces using Tools, Options in the Visual Basic Editor.
Your first pass at a well-commented macro might look like this:
Sub ProduceReportForEachCustomer()
' Define object variables for new workbook
' Suffix of N means New
Dim WBN As Workbook
Dim WSN As Worksheet
' Define object variables for the current workbook
' Suffix of O means Old
Dim WBO As Workbook
Dim WSO As Worksheet
Set WBO = ActiveWorkbook
Set WSO = ActiveSheet
' Find the FinalRow in today's dataset
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
' Use an Advanced filter to copy unique customers
' from column A to column H
' ???
'Find the final customer in column H
FinalCust = Cells(Rows.Count, 8).End(xlUp).Row
' Loop through each customer
For x = 2 To FinalCust
' Turn on the AutoFilter for this customer
' ???
' Create a new workbook
Set WBN = Workbooks.Add
Set WSN = WBN.Worksheets(1)
' In the original workbook, select visible cells
' ???
' Copy the selection to the new workbook
Selection.Copy Destination:=WSN.Cells(3, 1)
' AutoFit columns in the new workbook
WSN.Columns.AutoFit
' Add a title to the new workbook
WSN.Range("A1").Value = _
"Recap of Purchases for " & WSN.Cells(4, 1).Value
' Save the new book
WBN.SaveAs Filename:="C:" & WSN.Cells(4, 1).Value & ".xlsx"
WBN.Close SaveChanges:=False
'Return to the original workbook
WBO.Activate
WSN.Select
Next x
End Sub
The following sections explain that to create this macro, you need to figure out how to code the advanced filter to copy a unique list of customers to column H. You then need to figure out how to apply a filter to column A. Finally, you need to figure out how to select only the visible cells from the filter.
Using the Advanced Filter for Unique Records
You need to figure out how to use an advanced filter to finish the following section of code:
' Find the FinalRow in today's dataset
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
' Use an Advanced filter to copy unique customers
' from column A to column H
' ???
To use an advanced filter on this section of code, follow these steps:
1. Turn on the macro recorder.
2. On the Data tab, in the Sort & Filter group, click the Advanced icon to open the Advanced Filter dialog.
3. Select the option Copy to Another Location.
4. Adjust the list range to refer only to column A. The copy-to range will be cell H1.
5. Check the Unique Records Only box.
6. When the dialog looks as shown in Figure 19.19, click OK. The result is a new range of data in column H, with each customer listed just once, as shown in Figure 19.20.
7. On the Developer tab, click Stop Recording.
8. Use the Macros button to select Macro1 and then select Edit.
Even though the Advanced Filter dialog is still one of the most complicated facets of Excel 2016, the recorded macro is remarkably simple:
Sub Macro1()
'
' Macro1 Macro
'
'
Range("A1:A1001").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"H1"), Unique:=True
Range("H1").Select
End Sub
In your macro, there is no reason to select cell H1, so delete that line of code. The remaining problem is that the macro recorder hard-coded that today’s data set contains 1,001 rows. You might want to generalize this to handle any number of rows. The following code reflects these changes:
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
Range("A1:A" & FinalRow) .AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("H1"), Unique:=True
Using AutoFilter
When you have a list of customers, the macro loops through each customer. The goal is to use an AutoFilter to display only the records for each particular customer. Next, finish this section of code as follows:
' Loop through each customer
For x = 2 To FinalCust
' Turn on the AutoFilter for this customer
' ???
To apply an AutoFilter to this section of code, follow these steps:
1. On the Developer tab, select Record Macro.
2. On the Home tab, select the icon Sort & Filter–Filter. Drop-down arrows are turned on for each field.
3. In the drop-down in cell A1, clear Select All and then select Hip Lawn Corporation.
4. Back on the Developer tab, stop recording the macro.
5. Use the Macros button to locate and edit Macro2 as follows:
Sub Macro2()
'
' Macro2 Macro
'
'
Range("A2").Select
Application.CutCopyMode = False
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="Hip Lawn Corporation"
End Sub
The macro recorder always does too much selecting. You rarely have to select something before you can operate on it. You can theorize that the only line of this macro that matters is the Selection.AutoFilter line. Because you will always be looking at the AutoFilter drop-down in cell A1, you can replace Selection with Range("A1"). Rather than continually ask for one specific customer, you can replace the end of the line with a reference to a cell in column H:
Range("A1").AutoFilter Field:=1, _
Criteria1:=Cells(x,8).Value
Tip
Even though you have an existing Module1 with your code, Excel chooses to record the new macro into a new module. Therefore, you need to copy recorded code from Module2 and then use the Project Explorer to switch to Module1 to paste the code into your macro.
Selecting Visible Cells Only
After you use the AutoFilter in the macro, you see records for only one customer. However, the other records are still there, but they are hidden. If you copied the range to a new worksheet, all the hidden rows would come along, and you would end up with 20 copies of your entire data set.
The long way to select only visible cells is to press F5 to display the Go To dialog. In the Go To dialog, click the Special button and then click Visible Cells Only. However, the shortcut is to press Alt+;.
To learn how to select only visible cells in VBA, record the macro by following these steps:
1. Select the data in columns A through F.
2. Turn on the macro recorder and press Alt+;.
3. Stop the macro recorder. You should see that the recorded macro has just one line of code:
Sub Macro5()
'
' Macro5 Macro
'
'
Selection.SpecialCells(xlCellTypeVisible).Select
End Sub
In your original outline of the macro, you had contemplated selecting only visible cells and then doing the copy in another statement, like this:
' In the original workbook, select visible cells
' ???
' Copy the selection to the new workbook
Selection.Copy Destination:=WSN.Cells(3, 1)
Instead, copy the visible cells in one statement:
' In the original workbook, select visible cells
WSO.Range("A1:F" & FinalRow).SpecialCells(xlCellTypeVisible).Copy _
Destination:=WSN.Cells(3, 1)
Combination Macro Example: Putting It All Together
The following macro started as a bunch of comments and a skeleton of a loop:
Sub ProduceReportForEachCustomerFinished()
' Define object variables for new workbook
Dim WBN As Workbook
Dim WSN As Worksheet
' Define object variables for the current workbook
Dim WBO As Workbook
Dim WSO As Worksheet
Set WBO = ActiveWorkbook
Set WSO = ActiveSheet
' Find the FinalRow in today's dataset
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
' Use an Advanced filter to copy unique customers
' from column A to column H
Range("A1:A" & FinalRow).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("H1"), Unique:=True
'Find the final customer in column H
FinalCust = Range("H1").End(xlDown).Row
' Loop through each customer
For x = 2 To FinalCust
' Turn on the AutoFilter for this customer
Range("A1").AutoFilter Field:=1, Criteria1:=Cells(x, 8).Value
' Create a new workbook
Set WBN = Workbooks.Add
Set WSN = WBN.Worksheets(1)
' In the original workbook, select visible cells
WSO.Range("A1:F" & FinalRow).SpecialCells(xlCellTypeVisible).Copy _
Destination:=WSN.Cells(3, 1)
' AutoFit columns in the new workbook
WSN.Columns.AutoFit
' Add a title to the new workbook
WSN.Range("A1").Value = "Recap of Purchases for " & WSN.Cells(4, 1).Value
' Save the new book
WBN.SaveAs Filename:="C:" & WSN.Cells(4, 1).Value & ".xls"
WBN.Close SaveChanges:=False
'Return to the original workbook
WBO.Activate
WSO.Select
Next x
End Sub
After doing three small tests with the macro recorder, you were able to fill in the sections to copy the customer records to a new workbook. After running this macro, you should have a new workbook for each customer on your hard drive, ready to be distributed via email.
VBA macros open up a wide possibility of automation for Excel worksheets. Anytime you are faced with a daunting, mindless task, you can turn it into a challenging exercise by trying to design a macro to perform the task instead. It usually takes less time to design a macro than it does to complete the task. You should save every macro you write. Soon you will have a library of macros that handle many common tasks, and they will enable you to develop macros faster. The next time you need to perform a similar task, you can roll out the macro and perform the steps in seconds instead of hours.