Working With Worksheets

You can manually change the name of a sheet within Excel by clicking Format O Sheet O Rename. Excel highlights the sheet name tab. Click the tab and type the new name. After you modify the name, click elsewhere on the sheet and Excel updates the sheet name.

Because users can easily modify the name of a worksheet within Excel, be careful when referencing sheet names with your macros. If you attempt to reference the name of a sheet that has a changed name, Excel returns an error message.

No matter what its name, Excel still keeps track of the sheets based upon the order in which they exist within the Sheet Collection. If you use the Project Explorer to view the list of sheets in the workbook, you see listings of Sheet1, Sheet2, and so on, with the corresponding sheet name in parentheses.

You can also use the Name property in conjunction with the Parent property to determine the name of the workbook that contains the current sheet. You can use this Name property function to ensure that you are in the appropriate workbook before executing the contents of a macro. You determine the name of the corresponding workbook using the code CurrentWB = ActiveSheet.Parent. Name.

S Switch to Excel and run the macro.

0 Type the desired sheet name in the input box.

S Switch to Excel and run the macro.

0 Type the desired sheet name in the input box.

-■ The name of the active sheet changes to the name specified by the user.

Pile Edit

View Insert Format

Tools Data Window

Help

Type a question for help

- . »

! □

^Hiîi m

fe I «n

& E

Él m " ! Ara

g 10 , B

A17

-

Ä

A

B

c

D

E

F

G

H

1

J

K

L 11

1

Monthly Expenses

2

Rent

Groceries

$87.00

Child Car

Misc.

January

$750.00

$214.00

$75.00

$110.00

$175.00

$1,411.00

4

February

750

311

88

89

110

145

1493

5

March

750

245

90

99

110

123

1417

6

April

750

256

79

103

110

211

1509

7

June

$750.00

$307.00

$100.00

$110.00

$110.00

$345.00

II ,722.00

8

750

350

85

120

110

149

9

July

750

310

94

145

110

190

10

Au g ust

750

299

110

130

110

256

11

Septembt

750

245

120

111

110

298

12

October

750

280

99

90

110

245

13

Novemhe

750

400

110

88

110

405

14

Decembe

750

415

130

84

110

605

15

1G

17

18

19

20

21

22

23

24

25

26

-

► M Expenses ¿Ishe

X Sheet4 /, sheets /

<

>

Ready

SAVE A SHEET TO ANOTHER FILE

You can save a specific sheet in your workbook with the SaveAs method. You can use this property with a Sheets Collection object, which enables you to save any sheet. You can also use the property with a specific Worksheet or Chart object to indicate a specific workbook or chart that you want to save.

With the SaveAs method, you have eight different optional parameters that specify how Excel saves the sheet:

FileName, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AddToMru, and Local.

You use the FileName parameter to specify the file name and location where you want to save the selected sheet. If you omit this parameter value, Excel utilizes the filename of the corresponding workbook as the value for the FileName parameter. If you do not specify a path as part of the filename, Excel saves the file containing the sheet in the current folder.

You use the FileFormat parameter to specify the file format for saving the sheet to a file. You save the sheet to any of the file formats supported by Excel with one of the xlFileFormat constant values. See Appendix A for a list of the xlFileFormat constant values. If you do not specify a FileFormat parameter value, Excel uses the default value. The default value consists of the last specified file format you used to save a sheet as well as the version of Excel you use for new files. For example, to save a sheet to a Text file, you use the xlTextMSDOS XLFileFormat constant value.

With the Password parameter you can specify up to a 15-character password for opening the file. You use the WriteResPassword parameter to restrict the file to open as read-only without the password. The other parameters accept Boolean values of either True or False.

SAVE A SHEET TO ANOTHER FILE

SAVE A SHEET TO ANOTHER FILE

^ Create a new subroutine.

—0 Type ActiveSheet.SaveAs, replacing ActiveSheet with a reference to the sheet you want to save.

^ Create a new subroutine.

—0 Type ActiveSheet.SaveAs, replacing ActiveSheet with a reference to the sheet you want to save.

0 0

Post a comment