Case Problems

Capriati and Sons, Inc.

Joseph has been "wowing" his father and brothers with his knowledge of Excel and its use in automating the workplace. His father (and boss!) has given him a few special assignments. In the following exercises, assume the role of Joseph and perform the steps that he identifies. You may want to re-read the chapter opening before proceeding.

Every month, Capriati and Sons receives a worksheet from Bailey's Bookworld that details the revenue for various book types. Every quarter (three months), one of the Capriati accountants compiles this into a new quarterly workbook by adding and retyping all of the figures. Joseph decides to automate this process using macros.
He starts by downloading the last three months' workbooks (called EX11XTRA04a, EX11XTRA04b, EX11XTRA04c) and saving them in his personal storage location. Next, he creates a new blank workbook and saves it as "Bailey Quarterly". He then starts the macro recorder and names the macro ImportQuarterly, to be stored in this workbook. He assigns the keyboard shortcut Ctrl key+q and clicks the OK command button. He ensures that relative references are turned off.

First, he opens the EX11XTRA04a workbook and copies the contents of sheet 1. He then returns to the "Bailey Quarterly" workbook, selects cell A1, and pastes the information. He then opens the EX11XTRA04b workbook and copies the contents of sheet 1. He returns to the "Bailey Quarterly" workbook, selects cell A1, and does a paste special. In the paste special dialog box, he chooses to use the "add" operation. The information from the second workbook is added to the first. He then opens the EX11XTRA04c workbook and copies the contents of sheet 1. He returns to the "Bailey Quarterly" workbook, selects cell A1, and does a paste special, again choosing to add the values. He then closes the EX11XTRA04a, EX11XTRA04b, and EX11XTRA04c workbooks. After adjusting his column widths, he stops the macro recorder. His workbook now looks like figure 11.7.

Figure 11.7
Bailey Quarterly workbook

Joseph deletes all of the information on sheet 1 and plays his macro to test it. Seeing that it is a quick and easy way to import and consolidate this data, he saves the workbook and leaves it open for the next exercise.
Every quarter, the accountants fax this consolidated sheet back to Bailey's Bookworld. Usually this involves retyping the information in a fax cover sheet, although one brother has figured out copy and paste. Joseph decides to create another macro to generate the fax.
He starts the macro recorder and creates a new macro called "FaxQuarterly" and assigns it the keyboard shortcut of Ctrl key+w. Again, he stores the macro in the current workbook.

He starts by going to sheet 2 and creating a fax heading as shown in figure 11.7.

(Hint: Use the =today() function for the date.)

He then returns to sheet 1, copies the table there and pastes it under the fax heading on sheet 2. He stops the macro recorder and saves the workbook. He keeps the workbook open for the next exercise. The finished fax sheet is shown in figure 11.7.

Figure 11.7
Internet Orders worksheet

Joseph wants to make his macros easy for his brothers to use. So, he creates a new toolbar called "Joeseph" that is tied to the current workbook. He adds a button to execute the ImportQuarterly macro and changes the button image to a spade. He adds another button to execute the FaxQuarterly macro and formats it to look like a club. His custom toolbar now appears similar to figure 11.8.

Figure 11.8
Joseph custom toolbar

He saves the workbook and closes Excel. It's cappuccino time!

Data File: ex11xtra04a (13.0K)
Data File: ex11xtra04b (13.0K)
Data File: ex11xtra04c (13.0K)

2002 McGraw-Hill Higher Education
Any use is subject to the Terms of Use and Privacy Policy.
McGraw-Hill Higher Education is one of the many fine businesses of The McGraw-Hill Companies.