Tip

Always make sure that your data is saved, or that you have a backup file, before executing any new macros that you have created. Otherwise if your macro crashes you might not be able to recover it.

Figure 2.5 The Macro dialog box

E2 Microsoft EHcel - SALESMAN

-l.-.l

X »

ijP) File Edit View Insert Format □ ÍqÍÍJ II Arial

Tools

Data

Window Help Type a question for hdp _ -0 lo s B I u mm BP % tfc ■ _ - - A -

w&ek' sales !t f*

a

B

D

E

F «

l-l

¡

J' '

III II

12

Salesman Workbook Data

13

System Information

14

Workbook Name:

SALESMAN

15"

Wo rkhe e t Name:

weeklysales

16-

System Puipose:

To maintain weekly sales data for each représentative, and

17

to calculate sales to date.

he monthly b

Dnus for each salesperson & month total

1B

for each representative.

13.

.3]

I Formulae Descriptions:

:21

End of Month Sales

Each cell value in this is c

alculated by adding the Sales To Date to the Monthly Total

'2223

Monthly Bonus: Sales To Date:

Each c ell value in this is c alculate d by multiplying M onthly T otal by the B onus Rate

Each cell in this range is carried over from the End of Month Sales column for the beginning of each new month

24

Month No:

This cell value is

incremented at the start of each new month

■■2E

m

Sales Data:

27

"38?

Bonus Rate:

5

■29

Month No:

i

■ü:

Weekly Sales

31

Rep Name

Sales To Date

1

2

3

4

MonÜtlvTuial

.V.miïhh

End.1 f.Mon. il S:-Ic.

1

Bindi

Katie

Misba

Pete

Rhianaon Gareth

212:

11

£

212

■33

5 95

0

£

595

sJt

1Í9

U

£

129

35

720

0

£

720

■■3B

646

0

£

646

37

[_El

1 0| 0!

S

0

£

526

3B

459

0

£

459

:-33

1010

0

£

1010

40

432

0

£

452

41

TOTALS

53491

0

a

5349

ü ¡t

► Wbf Sheet 1 \weeklysales/ week sales test data / Summary 1 Sheet H j

.►.Ii

Ready Recording

À

Figure 2.6 Screenshot of weeklysales worksheet after the macro is executed

The week_sales_test_data worksheet

The tabs at the bottom of the screenshot (Figure 2.6) shows there is a sheet called week_sales_test_data. This holds test data so that you can rerun the macros without having to enter new data every time. To reload data into the weekly_sales range, click the week_sales_test_data sheet and select the range testdata. Copy and paste it into week_sales in the weeklysales sheet.

0 0

Post a comment