Working With Worksheets

The Copy method produces the same results when you use it with the Chart object, Charts collection object, Worksheet object, and Worksheets collection object instead of the Sheets object. You can use these other objects when you only want to work with a specific type of sheet. For example, to place a copy of a worksheet at the beginning of the workbook, you type: Worksheet(3).Copy Before:=Worksheets(1). This code places a copy of the third worksheet in front of the first worksheet. If the first sheet in the workbook is actually a chart, the copied sheet comes after the chart, but still before the first worksheet. The same process holds true for copying chart sheets, but you use the Charts collection object to specify the chart sheet to copy.

You can combine your object references with a Copy statement. For example, you can place a copy of the first workbook before the first chart sheet:

Example:

Worksheets(1). Copy Before:=Charts(1).

When you copy a sheet within a workbook, Excel indicates the copy of the sheet by placing a number in parentheses behind the sheet name. For example, for Sheet3, Excel indicates the copied sheet as Sheet3 (2), with the number in parentheses indicating this is the second version. Copying this worksheet again creates Sheet3 (3).

□ Type Sheets (LastSheet).Copy, replacing Sheets(LastSheet) with a reference to the sheet to copy.

L-0 Type Before:=Sheets(1), replacing Sheets(1) with the location for placing the copied sheet before a sheet.

■ Alternately, you can type After:=Sheets (1), replacing Sheets(1) with the location for placing the copied sheet after a sheet.

□ Switch to Excel and run the macro.

m

File Edit

View Insert Format

Tools Data Window

Help

^question For help

- . a

x

I □

% I «

& 2

èl IS

j} >: A

ial

. 10 . B

I a ==

m m

B18

w

ft

A

B

¡3

D

E

F

G

H

1

J

K

L Tl

1

Monthly Expenses

2

Rent

Groceries

Fuel

Electricity Child Car Misc.

3

January

Î750.00

$214.00

Î75.0C

$87.00

$110.00

$175.00

$1,411.00

4

February

750

311

88

89 110

145

1493

5

March

75Q

245

90

99 11G

123

1417

G

April

750

25G

79

103 110

211

1509

7

May

$750.00

$307.00

Î 100.00

t110.00

$110.00

$345.00

$1 722.00

8

June

75Q

350

85

120 110

149

9

July

750

310

94

145 110

190

10

August

750

299

110

130 110

256

11

Septemb

75Q

245

120

111 11G

298

12

October

750

280

99

98 110

245

13

No vein be

750

400

110

88 110

405

14

Decern be

750

415

130

84 110

605

15

16

17

18

19

20

21

22

23

24

25

26

► »1 Ksheet3 (2) / sheets / Sheet4 / Sheet 1

/ 5heet2 /(

5heet3 /|

<1

1 -I

y

-■ The specified sheet is copied and placed in front of or behind the indicated sheet in the workbook.

0 0

Post a comment