Sheet codename not working

[Makelei]

Hi,
XP and Excel2003.

What might be the reason why this is not working (Sheet2 is codename):
Sheet2.Select

I have tried all possibilities that I can imaging, but no. I have another
file and selecting sheet with code name (Sheet4.Select) funtions just fine.

Now using code:
Workbooks.Open Filename:=Doc2
ActiveWorkbook.Sheet2.Select

Opens Doc2 excel file just fine, but does not open the sheet.

Thanks in advance
MakeLei

 

[Jacob Skaria]

Use the index number instead.
Activeworkbook.Sheets(2).select

OR the sheetname itself

Activeworkbook.Sheets("Sheetname").select

 

[Bob Phillips]

Just use

Workbooks.Open Filename:=Doc2
Sheet2.Select

the newly opened workbook will be the active book.

 

[Jacob Skaria]

Oops...I missed the boat completely...

Try (if trusted access to Visual Basic Projects is turned ON)

Activeworkbook.VBProject.VBComponents("Sheet1").Activate

OR

Dim ws As Worksheet, strSheetCodeName As String
strSheetCodeName = "Sheet1"
For Each ws In Worksheets
If ws.CodeName = strSheetCodeName Then ws.Select: Exit For
Next

 

[Peter T]

Dim ws As Worksheet, strSheetCodeName As String
strSheetCodeName = "Sheet1"
For Each ws In Worksheets
If ws.CodeName = strSheetCodeName Then ws.Select: Exit For
Next

Yep, that's the way I'd do it.

Regards,
Peter T

 

[GS]

Makelei explained on 6/1/2010 :

Hi,
XP and Excel2003.

What might be the reason why this is not working (Sheet2 is codename):
Sheet2.Select

I have tried all possibilities that I can imaging, but no. I have another
file and selecting sheet with code name (Sheet4.Select) funtions just fine.

Now using code:
Workbooks.Open Filename:=Doc2
ActiveWorkbook.Sheet2.Select

Opens Doc2 excel file just fine, but does not open the sheet.

Thanks in advance
MakeLei

Here's a reusable function I use that returns the Worksheet.Name from
any workbook given its Worksheet.CodeName.

Function Get_SheetTabName(CodeName As String, Optional Wkb As Workbook)
As String
Dim Wks As Worksheet
If Wkb Is Nothing Then Set Wkb = ActiveWorkbook
For Each Wks In Wkb.Worksheets
If Wks.CodeName = CodeName Then _
Get_SheetTabName = Wks.Name: Exit Function
Next
End Function

regards,