Working With Worksheets

When you use the Before and After parameters to specify the location in the workbook where you want to place the new sheets, you can use multiple methods. If you know you want Excel to add the sheets before the first sheet in the workbook, or after the last sheet, you can easily do so by referencing an element of the Worksheets Collection. Because Excel adds sheets to the Worksheets Collection in the order they exist in the workbook, Excel always makes the first sheet in the workbook the first element of the Worksheets Collection, and references it as Worksheets(1). Because you do not always know how many sheets are in a workbook, you can use the Count method with the Worksheets object to determine the last sheet in the workbook by typing Worksheets.Count.

You can also reference a specific sheet by name. For example, by default Excel names all worksheets as Sheet1, Sheet2, and so on. If you want to place the new sheets before Sheet1, you can type the following for the Before parameter: Before:=Sheet1.

Alternately, you can add the sheet before the active sheet in the workbook. To do this you use the ActiveSheet property. This option is useful because no matter what sheet you select, Excel adds the new sheets before or after that specific sheet.

< Type Before: = Worksheets(1), replacing Before with either Before or After, and Worksheets(l) with the sheet in front of which you want to place the new sheets.

—Q Type Count:=2, replacing 2 with the number of sheet you want to add.

S Type Type:=xlWorksheet, replacing xlWorksheet with the constant indicating the type of sheet to create.

□ Switch to Excel and run the macro.

~[§P] File Edit

View Insert Format

Tools Data Windov-

Help.

Type <a question for help

-.Sx

I □

& S

a

Hi | -T

1. Z

it I m w :

- 10 - B

A1

-

fx

A

B C

D

E

F

G

H 1 J

K

L Tj

1

2

3

4

5

6

7

8

9

10

11

12

13

14

16

17

18

19

20

21

22

23

24

25

1

■2b

27

M

► H Ksheets / Sheet4 / Sheet 1

Sheet! / Sheet3 A

Ready

-■ Excel adds the specified number of sheets to the workbook.

0 0

Post a comment