Recognizating worksheet name

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 said:
I have one Workbook that has a unique problem. When I attempt to access
Click to expand...
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 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 said:
The sheet has been named the same for at least five years. I
Click to expand...
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.
 
Using 'With ThisWorkbook.Worksheets("Sheet1")' nor its actual sheet name in place of "Sheet1" worked

Thanks anyway
John H
 
Back
Top