sheet referencing

[soce]

I am simply puzzled why this one piece of code works for me but not the other. Can anyone shed some light on this? Thanks.

For a default workbook this works on Sheet1 which is no surprise

Please Login or Register  to view this content.

But when I change the name of the sheet from "Sheet1" to "Area" the following does not work.

Please Login or Register  to view this content.

But if I do this, it works again....

Please Login or Register  to view this content.

Is there something special about using the default name that allows me to make the first one work but not the second? I don't get this. This does kind of matter to me because I will have lots of sheets called Area with a number behind it, example: Area19, so I need to understand what is going on here.

Thanks

[Leith Ross]

Hello Soce,

Area is a VBA keyword. It is a collection object that contains cell group ranges. Placing it inside quotes as a string value is fine.

Sincerely,
Leith Ross

[shg]

I know of the Areas collection, but no Area keyword ...

When you reference a sheet as

Please Login or Register  to view this content.

, you are referencing the sheet by its codename. Codenames start out the same as sheet names, but diverge when you rename a sheet from the user interface. So after you change it,

Please Login or Register  to view this content.

You have to change the codename of the sheet in the VBE Properties window; then you can reference the sheet as

Please Login or Register  to view this content.

So the Properties window could look like any of these; the codename is shown on the left, sheet name on the right in parens:

Sheet1(Sheet1) ' new workbook
Sheet1(Area) ' name change via user interface
Area(Sheet1) ' name change in VBE Properties window
Area(Area) ' name changed both ways

[Leith Ross]

Hello Shg,

In computer programming, a keyword is a word or identifier that has a particular meaning to the programming language. What is defined as keyword varies with the language used. In this case, Areas refers to a Range object collection, which is actually a Function, which makes it a method of the Range Object, and therefore a reserved word.

Sincerely,
Leith Ross

[shg]

Leith: My point was that Areas is a VBA keyword, and Area (the word used by the OP) is not.