Software

Save time in Excel with automated reports

You can use Excel programming to automate a lot of your employees' monthly reporting. For example, if your employees submit a monthly travel expense report, learn how you can develop an interactive application that does a lot of the work for them.

You can use Excel programming to automate a lot of your employees' monthly reporting. For example, if your employees submit a monthly travel expense report, you can develop an interactive application, such as the one that follows, that does a lot of the work for them. Follow these steps:

  1. Open a blank workbook.
  2. In A1, enter Category.
  3. In B1, enter Expense.
  4. In A2, enter Meals.
  5. In A3, enter Airfare.
  6. In A4, enter Ground Transportation.
  7. In A5, enter Tips.
  8. In A6, enter Lodging.
  9. In A7, enter Total.
  10. Press [Alt][F11].
  11. In the Project-VBAProject pane, double-click ThisWorkbook under VBAProject.
  12. Go to Insert | Procedure.
  13. Click in the Name text box and enter SumExpenses, then click OK.
  14. Enter the following code at the prompt:

    Range("B2").Select

    ActiveCell.Value = InputBox("Enter Total Meals including tips.")

    Range("B3").Select

    ActiveCell.Value = InputBox("Enter Round-trip Airfare")

    Range("B4").Select

    ActiveCell.Value = InputBox("Enter total for taxis, tolls, rented cars, etc")

    Range("B5").Select

    ActiveCell.Value = InputBox("Enter Other Tips")

    Range("B6").Select

    ActiveCell.Value = InputBox("Enter Hotel expense")

    Range("B7").Select

    ActiveCell.Value = "=Sum(B2..B6)"

    ActiveWorkbook.SaveAs Filename:="Travel Expense Report.xls"
  15. Press [Alt]Q.
  16. Press [Alt][F8].
  17. Click on SumExpenses in the Macro list.
  18. Click the Options button.
  19. In the Ctrl+ box, enter e, then click OK.

At the end of the month, you can send a copy of the worksheet to each employee. After opening it, they can press [Ctrl]E and then follow the prompts to fill in the report.

Miss an Excel tip?

Check out the Microsoft Excel archive, and catch up on other Excel tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

Editor's Picks