activating correct worksheet

[Guest]

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

 

[Tom Ogilvy]

? activesheet.Name
Sheet2
Worksheets("Sheet2").Activate

in the immediate window did not raise any error. What you say is very
strange.

 

[Peter T]

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

 

[Guest]

Hi Peter

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

Regards Bob C.

 

[Guest]

Hi Tom & Peter

Just replaced the Line "Worksheets("Names").Activate" with
"Sheets("Names").Select" and it selects "Names" wether Names worksheet is
ActiveSheet or not.
Strange very strange.

Thank you both for your posts.

Regards Aussie Bob C.

 

[Peter T]

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

 

[Peter T]

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