Found I could duplicate your original problem if trying to activate a Macro
sheet, ie one created with Ctrl-F11.

Sub Test()
Sheets.Add Type:=xlExcel4MacroSheet
For Each sh In ActiveWorkbook.Sheets
Debug.Print TypeName(sh), sh.Type, sh.Name
If sh.Type = xlExcel4MacroSheet Then s = sh.Name
Next
Worksheets(s).Activate 'fails
Sheets(s).Activate
End Sub

Regards,
Peter


"Robert Christie" wrote in message
...
Hi Peter

No ranges defined as "Names" in workbook.
Thanks for the post

Regards Bob C.

"Peter T" wrote:

Hi Robert,

Your code works fine for me also. If I define a range as "Names" I get a
"Type mismatch" message. Thereafter, surprisingly, the defined name
seems
fine and your code still works. But do you have such a name?

Regards,
Peter T

"Robert Christie" wrote in message
...
Hi

The code Worksheets("Names").Activate or Select brings up "error 9
Subscript
out of range" if sitting in the "Names" Worksheet, but will activate
if
sitting in any other sheet, why?
What am I missing?
I just want to be sure the Formatting sub procedure called up runs on
the
correct sheet.

TIA

--
Thank you

Regards
Aussie Bob C.
Using Windows XP Home + Office 2003 Pro