Sheet codename not working

  • Thread starter Thread starter Makelei
  • Start date Start date
M

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
 
Use the index number instead.
Activeworkbook.Sheets(2).select

OR the sheetname itself

Activeworkbook.Sheets("Sheetname").select
 
Just use

Workbooks.Open Filename:=Doc2
Sheet2.Select

the newly opened workbook will be the active book.
 
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
 
Jacob Skaria said:
Dim ws As Worksheet, strSheetCodeName As String
strSheetCodeName = "Sheet1"
For Each ws In Worksheets
If ws.CodeName = strSheetCodeName Then ws.Select: Exit For
Next
Click to expand...

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

Regards,
Peter T
 
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
Click to expand...

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,
 
Back
Top