Recognizating worksheet name
 

I have one Workbook that has a unique problem. When I attempt to access it from code in a module with the name of the sheet or "Sheet1", I get a <subscript out of range error. I can access when I use the number (provided it is the first sheet). All the other worksheets in the workbook can be accessed by name

The code is

With Worksheets("Sheet1") ' return <subscript out of range erro

With Worksheets(1) ' work

NOTE: This same worksheet will not run a function attached to that sheet or from a module

Is there some switch that needs to be changed

Recognizating worksheet name
 

John,

Maybe stating the obvious, but worksheet 1 hasn't been renamed by any
chance?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"John H" wrote in message
...
I have one Workbook that has a unique problem. When I attempt to access
it from code in a module with the name of the sheet or "Sheet1", I get a
<subscript out of range error. I can access when I use the number
(provided it is the first sheet). All the other worksheets in the workbook
can be accessed by name.

The code is:

With Worksheets("Sheet1") ' return <subscript out of range error

With Worksheets(1) ' works

NOTE: This same worksheet will not run a function attached to that sheet
or from a module.

Is there some switch that needs to be changed

Recognizating worksheet name
 

The sheet has been named the same for at least five years. I have not tried changing its name since it is used in over 500 formulas using Excel functions. The name works in Excel functions, just not in VBA functions, while other worksheet names work in both. Also, the worksheet is NOT hidden

John H

Recognizating worksheet name
 

John,

It might be a long shot, but try using

With ThisWorkbook.Worksheets("Sheet1")


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"John H" wrote in message
...
The sheet has been named the same for at least five years. I
have not tried changing its name since it is used in over 500
formulas using Excel functions. The name works in Excel
functions, just not in VBA functions, while other worksheet names
work in both. Also, the worksheet is NOT hidden.

John H

Recognizating worksheet name
 

Using 'With ThisWorkbook.Worksheets("Sheet1")' nor its actual sheet name in place of "Sheet1" worked

Thanks anyway
John H