Working With Worksheets

TYPE THIS: '

If Sheets(SheetName)

<> Sheets(N) Then

Sheets(SheetName)

. Move Before:=Sheets(N)

End If

This code checks that the sheet you are moving and the sheet before which you intend to move it are not the same sheet. If the sheets are the same, Excel ignores the move statement and continues on with the looping statements.

] Type Next. Type

Sheets(SheetName).Move Before:=Sheets(N).

m

File Edit

View Insert Format

Tools Data Windol"

Help

Type a question For help

- . fl> x

I □

00É

QI m a v * ^ I «

1 2

zi m

5 J

ial

g B

/ D m

m m

A1

-

Â

A

B

C

D

E

F

G

H

1

J

K

L T|

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

-

M -1

h m[\ Chartl / Sheetl /sheet2

Sheet3 l S

eeM \sheets/

Ready

] Type Next. Type

Sheets(SheetName).Move Before:=Sheets(N).

■ The sheets are sorted alphabetically within the workbook.

USING THE RANGE PROPERTY

You can use the Range property to define a range of cells within a worksheet. When you define a range, you create a Range object which you can make a single cell, an entire column, a row, or a selection of multiple cells. Typically when working with the contents of a worksheet, you need to define a range in order to make any modifications to it.

You can use the Range property with an Application, Worksheet, or Range object. Therefore, the statements Application.Range and ActiveSheet.Range return the same results. If you use the Range property without an object, Excel assumes that the object you reference is the ActiveSheet.

There are two different syntaxes that you can use with the Range property. The first version requires two different parameters, Cell1 and Cell2. With this form of the

Range object, you reference the upper left corner of the desired range with the Cell1 parameter and the lower-right corner of the range with the Cell2 parameter. For example, to specify a range of cells between A1 and E15 you use the code: Range("A1", "E15").

The other form of the Range property requires the use of a Name parameter. This required parameter indicates a range using the A1-style reference. You use a colon between two cells to specify a range. For example, Range( "A3:F5" ) specifies the range of cells from A3 to F5. You can specify the union between two ranges by placing a comma between the range definitions. You can also specify the location where two ranges intersect by leaving a space between the two range definitions. For example, Range( "A3:F3 D2:G5" ) specifies a range where the range of cells A3 to F3 intersect with the range of cells D2 to G5.

USING THE RANGE PROPERTY

USING THE RANGE PROPERTY

USING THE RANGE PROPERTY WITH CELL REFERENCES

< Switch to Excel and run the macro.

■ The specified range of cells is selected.

USING THE RANGE PROPERTY WITH CELL REFERENCES

-D Create a new subroutine.

Note: See Chapter 3 for information on creating subroutines.

0 Type Range("A1", "B3").Select, replacing A1 and B3 with the upper-right and lower-left corners of the selection.

■ Alternately, you can place a space or a colon (:) between the ranges to specify an intersection or union.

< Switch to Excel and run the macro.

Note: To learn how to run a macro, see Chapter 1.

■ The specified range of cells is selected.

0 0

Post a comment