A relatively easy macro

Q: I have written an Excel macro that copies a column of data on Sheet1 and pastes that data as a row on Sheet2. However, I want the paste location to drop down to the next blank row each time I paste, but the macro keeps pasting the data to the same row each time, overwriting my previous data. How do I get my macro to move the cursor to the next blank row before pasting?

A: Before discussing the solution, it is important to understand the difference between Excel’s absolute and relative macros. By default, Excel records an absolute macro in which the macro stores your exact cell locations as you record each macro. As an option, you can also record a relative macro in which Excel stores your cursor movements (instead of exact cell locations) as you record each macro. In your case, the macro needs to be half absolute and half relative, as described in the following example.

In this example, column B contains a range of data (highlighted in yellow) related to a sales order, and row 2 (beginning in column D) contains headings for a data collection area (highlighted in blue).

The objective is to record a macro that will copy the vertical data from the yellow cells and paste the results horizontally to the first available row under the blue cells. To accomplish this task:

1. From the View tab, select Macros, Record Macro.
2. In the Macro name box, enter a name such as PostSalesOrder (macro names cannot contain spaces), and click OK. (You are now recording a macro as indicated by the blue, square stop recording icon located in the bottom-left corner, as pictured.)

   

3. Highlight the yellow cells and press Ctrl+C to copy the data.
4. From the View tab, select Macros, Use Relative References. (You are now recording a relative macro from this point forward. The only indication that you are recording a relative macro (as pictured below) is that the Use Relative References icon has a small orange border highlight, and you must select Macros from the View tab to see this indication.)

   

   

5. Press the F5 key to launch the Go To dialog box, enter D1 in the Reference box, and click OK. (This action will position your cursor at the top of the data collection area.)
6. Press the End key, then the down arrow key. (This action will move your cursor to the last completed row in the data collection range—cell D2 in this example). Next, press the down arrow key again to position the cursor at the first available blank row—cell D3 in the example.
7. From the Home tab, select Paste, Paste Special, and place a check in the box next to Transpose, then click OK.
8. Next, to prepare the sales order form to receive the next sales order, press the F5 key to launch the Go To dialog box, enter B3:B19 in the Reference box, click OK, then press the delete key. (This erases the existing data from the sales order form and positions the cursor in cell B3, making it easier for the sales order clerk to enter the next sales order.)
9. From the View tab, select Macros, Stop Recording. You have now created a macro that will copy a given column of data and paste it to the next available blank row in the data collection area.

To make your macro easier to access and execute, create a macro button. A macro button is really just a text box with a macro assigned to it, so that clicking the text box executes (or runs) the macro. Steps for creating a macro button are as follows:

1. Insert a text box in the upper-left corner of the worksheet. From the Insert tab, select Shapes and click the Text Box icon (the first icon under the Basic Shapes section), then click and drag a range using your mouse to highlight the desired position and size of the text box.
2. Click the middle of the text box and enter your desired text, such as Post Sales Order, and format the text box as desired. (In this example, I increased the font size, centered the text, bolded the text, and applied the Intense Effect – Blue, Accent 1 shape style from the Drawing Tools, Format tab, Shape Styles group. The Drawing Tools become visible at the top of the screen when you click the text box.)
3. Right-click the edge of the text box and select Assign Macro from the popup menu, select your newly recorded macro, and click OK. These three steps are depicted below.

Your resulting workbook should appear as follows:

Thereafter, clicking the Post Sales Order macro button runs the macro that copies your columnar data and pastes it horizontally to the next available blank row in your data collection area, example results of which are pictured below.

You can download the example Excel workbook described above at carltoncollins.com/macro.xlsm.

Notes:

1. Remember to save the workbook as an Excel Macro-Enabled Workbook; otherwise the macro will not be saved with the file.
2. The example was created on a single worksheet for simplicity, but I normally would locate the data collection area and sales order form on different worksheets.