Excel – Macros Lesson  3

From the Tools menu, Customize submenu, Options tab, select all options except “large icons.”

 

In this exercise we want to create a table to store purchasing data and we would like to create a macro to store the data.

Some of the rows in our invoice will be blank, so we also need to find a way to automatically delete rows that contain no data. 

We can use the data to generate management reports.

 

Data used www.cob.sjsu.edu/BUS91L/Textbook/DeliveryFormv2.xls

DO NOT OPEN THE FILE. Save the file on your desktop. Then open the file.

 

If the security setting on your browser, or in Excel, is set to high, you may not be able to download or open the file. You can view those settings on most browsers from the Tools menu, Internet Options submenu, on the Security tab. Depending on your user privileges, you may or may not be able to change those settings. In Excel you can view the settings from the Tools menu, Macro Submenu, Security submenu. Lower the security levels and then try again.

 

Background

 

A customer order will typically consist of several rows of date. Our macro will have to simultaneously store several rows of data, matching each row of the Invoice. We will store it in a table like this:

 

Quantity

Product

Price

Revenue

 

 

 

 

 

 

 

 

 

 

 

 

 

Because this design mimics the layout of the Invoice worksheet, we can write a macro to simply copy and paste directly from the Invoice worksheet into the data table worksheet.  Since the data in the latter two fields is generated by formulas, we will use “paste special” to convert the formulas into values.

 

Here’s what this will look like

 

Quantity

Product

Price

Revenue

3

Sandwich

Dollar Amount

Dollar Amount

1

Coffee

Dollar Amount

Dollar Amount

2

Beer

Dollar Amount

Dollar Amount

 

 

Dollar Amount

Dollar Amount

 

 

Dollar Amount

Dollar Amount

 

Some rows in Columns A and B will be blank, but  the formulas we copied will still appear in columns C and D. To clean up the table we need to delete these rows.   

We will build a second macro to handle this task. It’s easier to build and test several small macros then one large macro.

 

NOTE: We could create one table that would only record the quantities and product names. We could use a VLOOKUP formula to find the price. A formula would generate the revenue totals. It would look like this:

   

Quantity

Product

 

Price

Revenue

 

 

 

=VLookup()

=A2*D2

 

 

 

=VLookup()

=A3*D3

 

 

 

=VLookup()

=A4*D4

 

This design would work if we always charged the same amount for the items on our menu. But, because prices are constantly changing, we need to store the price at which the item is sold.

 

Tasks:

 

Prepare the data storage area.

 

  1. Right click on the Invoice tab. Insert a new worksheet.
  2. Click on the tab of the new Worksheet. Rename it as PurchaseData
  3. In Cell A1 type: Quantity
  4. In Cell B1 type: Product
  5. In Cell C1 type: Price
  6. In Cell D1 type: Revenue
  7. Select columns A-D. From the format menu, select columns. Change the width to 14.
  8. Format cells A1 to D1 as Bold and Centered, using a 12 point font size. 
  9. Click on the tab to select the Invoice Worksheet.
  10. Fill out the invoice form with imaginary data.

Create the Macro

 

Macro 1 – copy the information into the Purchase Data table

  1. From the Tools Menu, Macro Submenu, select Record New Macro.
  2. In the Macro Name box, type: SavePurchaseDetails.
  3. In the Description Box, Click at the end of the description. Press Enter. Type: This macro saves order details.
  4. Click OK. The Record Macro Toolbar opens. The square blue icon is the stop button.
  5. Click on the Invoice worksheet tab. Click in cell A3.
  6. Use the mouse to select cells A13 to D21.
  7. From the Edit menu, select Copy.
  8. Click on the PurchaseData worksheet tab. Click in cell A1.
  9. On your keyboard, press End. Release End and press the down arrow.
  10. On your keyboard, press End. Release End and press the down arrow.

Explanation: If your table has no data in it, the first End + Down will put the cursor at the bottom of the worksheet. But, if your table contains one or more rows of data, the first End + Down will only put the cursor into the bottom row of data. The extra End + Down makes sure you actually reached the bottom of the worksheet.

  1. On your keyboard, press End. Release End and press the up arrow.
  2. Press the down arrow.

Your cursor should now be located directly below the last cell in the table that contains data.

  1. From the Edit Menu, Select “Paste Special.”
  2. In the dialog box, select “values and number formats.” Click Ok.

Your new data should now be displayed in the table. 

Macro 2 - Delete the blank rows

  1. From the Tools Menu, Macro Submenu, select Record New Macro.
  2. In the Macro Name box, type: Delete Blanks.
  3. In the Description Box, Click at the end of the description. Press Enter. Type: This macro removes blank rows from the purchase data table.
  4. Click OK. The Record Macro Toolbar opens. The square blue icon is the stop button.
  5. Click in cell A2 in the PurchaseData worksheet.
  6. On your keyboard, press End.
  7. Press the down arrow. (This moves you to the last row of cells containing data in column A)
  8. Press the down arrow. (This moves you into the first row in column A containing blank data.)
  9. Press the right arrow twice. (This moves the cursor below the third column of data.)
  10. Hold down shift. On your keyboard, press the right arrow key. (This selects both cells in column C and D. These contain formulas that will not be wanted in the table.)
  11. Continue holding shift. Simultaneously press End with the down arrow. (This selects all of the cells in column C and D that have formulas, bit have no data in columns A and B)
  12. Release the shift key.
  13. Your cells containing zeros and N/A should now be selected.
  14. On the keyboard, press Delete.
  15. Click in cell B2. Click on the A-Z (sort ascending) icon.
  16. Click on the A2 cell.
  17. Click on the square blue icon to stop recording.

Save the file on your desktop. Use the filename InvoiceWithMacros2

Test the Macros

 

  1. From the Tools Menu, Macro Submenu, select Macros.
  2. A dialog Box opens. Select SavePurchaseDetails .
  3. Click on Run. Check to see if the macro worked the way you wanted.
  4. Repeat for the DeleteBlanks Macro.

Macros can be edited using Visual Basic. This was covered in Macros Lesson 1.

Save the file on your desktop. Use the filename InvoiceWithMacros2.

That’s it. You’re done! Go here for  Macros Lesson 1 and Macros Lesson 2