- At a new blank worksheet, in cell A1, create a macro and name it Font. Use an appropriate description. The macro should change the font to Bookman Old Style 12 points, and adjust the column width to AutoFit.. Assign the macro to the Ctrl + Shift + L shortcut.
- Import the EL2-U2-SA-Mileage-DataFile.txt file into a worksheet.
- Merge and center the worksheet title over the worksheet.
- Run the macro in cells A3, B3, C3, D3. Adjust the columns width to AutoFit..
- Use the Internet to determine the mileage between the home city (St. Louis) and each destination. Enter them in Column C.
- Save the workbook as a macro-enabled workbook with the name EL2-U2-SA2-Mileage. Add a header to the worksheet; it should include your name at the left and the sheet name at the right.
- In Cell E3, insert the heading Cost. Apply the Font macro. If the cost per mile is $0.30, calculate the cost for each location (E4:E19). Apply the Currency format and specify two digits past the decimal point.
- Use the Paste Special function and paste the values (not the formulas) in column E. Adjust the column width to AutoFit if necessary. Merge and center the title again.
- Apply formatting of your choice to the worksheet.
- Save and print EL2-U2-SA2-Mileage.xlsm.
- Open EL2-U2-SA2-TravelRoutesMemo-DataFile.docx. Add your instructor’s name and your name as indicated. Update the date to the current date. Save as EL2-U2-SA2-TravelRoutesMemo. Embed the data from the Sheet1 worksheet of EL2-U2-SA2-Mileage.xlsx at the end of the memo. Remember to go back and enable the macros.
- Save the memo.
milage txt. doc below
Gateway Trucking Travel Routes
Destination Delivery Day Mileage Truck
"Atlanta, GA" Monday A
"Detroit, MI" Monday B
"Philadelphia, PA" Monday D
"Kansas City, MO" Monday F
"Little Rock, AR" Tuesday E
"Milwaukee, WI" Tuesday B
"Denver, CO" Tuesday C
"Detroit, MI" Wednesday I
"Chicago, IL" Wednesday F
"Dallas, TX" Wednesday E
"San Diego, CA" Wednesday G
"Philadelphia, PA" Thursday H
"Little Rock, AR" Thursday A
"Denver, CO" Thursday D
"Louisville, KY" Friday F
"Milwaukee, WI" Friday B