Range only works when Sheet Name is "Sheet1"

  • Thread starter Thread starter paul reed
  • Start date Start date
P

paul reed

Hi,

I have a single worksheet spreadsheet whose properties show the value of the
"(Name)" to be "Sheet1"...however the "Name" property is "financial".

When I do this:

Worksheets("Sheet1").Range("A6:O106")

I get a subscript error. If I do this:

Worksheets("financial").Range("A6:O106")

I also get subscript error.

....however...whenever I change the "Name" property to be "Sheet1" instead of
"financial" and do this:

Worksheets("Sheet1").Range("A6:O106")

I get access to the range I want and all is well.

....any ideas.

Thanks
 
Paul,

When you say the name property, what exactly are you referring to? Did you
change the (Name) at the top of the properties window? If so, this is the
codename of the sheet, and you use this like so

financials.Range("A6:O106")

You can double check this by seeing if the worksheet tab name changes, if
not it is the codename.
 
Just to clarify what Bob is saying:

Since you said your code name [ (Name) ] property is Sheet1 then usage
would be

Sheet1.Range("A6:A106")

If you get a subscript out of range when you do

Worksheets("financial").Range("A6:A106")

then the name "financial" does not match the tab name. Whatever the
difference was in your first test, when you retyped it to Sheet1 and then
used Worksheets("Sheet1").Range("A6:A106") you then matched the argument
to a Worksheet with an existing worksheet name (the worksheet name has
nothing to do with the codename - just when the sheets are created, the
sheetname and codename will match for the English version of Excel at
least). When you look at properties, the is a property Name without the
parentheses - this is the name you see on the tab and the argument to the
Worksheets collection.

I suspect when the sheet was named financial, it had a space at the end
which caused the mismatch.

Try renaming the sheet to financial and reference you range again using
Worksheets("financial").Range("A6:A106"). Also, the name is not case
sensitive.
 
When you're in the VBE and looking at the project explorer, you see something
like:

VBAProject (book1.xls)
Microsoft Excel Objects
Sheet1 (Financial)
or do you see
Financial (Sheet1)

Each worksheet has a .name property (that you can see on the worksheet tab in
Excel). It's the name in parentheses in the project explorer.

And each worksheet has a .codename property. It's the "name" not in
parentheses.

If you click on that item in the project explorer and hit F4 so you can view the
properties, you'll see the top entry (in the alphabetic tab) is (Name). That
represents the .codename.

A little lower in the list is the worksheet name.

======
Now my point <bg>.

If you spelled one of those examples correctly, it should work.

You can refer to a worksheet like:
worksheets("mySheetName").range(....)
(mysheetname is the name you can see on the worksheet's tab in excel.)

But if your user renames the sheet, then this code breaks.

If the code name were Sheet1, you could also refer to that sheet as:
sheet1.range(...)

Most users don't know how to change this name, so it's a lot safer.

In your case, I'd check the spelling (and maybe look for leading/trailing
spaces).
 
That could be a problem if the macro isn't working against the activesheet. But
if it is, why not just:

activesheet.range(...)



Ed said:
A better way of doing this is avaid hardcoding the sheet
name. That way, even if the User changes the sheet name
the code will still run.

Example:
'get the sheet name
mySheetName = ActiveCell.Worksheet.Name
'pass the name to your code
Worksheets(mySheetName).Range("A6:O106")
Click to expand...

<<snipped>>
 
Back
Top