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

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

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

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

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

Thanks anyway
John H