Sheet codename not working
 

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

Sheet codename not working
 

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

OR the sheetname itself

Activeworkbook.Sheets("Sheetname").select

--
Jacob (MVP - Excel)


"Makelei" wrote:

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

Sheet codename not working
 

Just use

Workbooks.Open Filename:=Doc2
Sheet2.Select

the newly opened workbook will be the active book.

--

HTH

Bob

"Makelei" wrote in message
...
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

Sheet codename not working
 

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

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

Activeworkbook.VBProject.VBComponents("Sheet1").Ac tivate

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 (MVP - Excel)


"Jacob Skaria" wrote:

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

OR the sheetname itself

Activeworkbook.Sheets("Sheetname").select

--
Jacob (MVP - Excel)


"Makelei" wrote:

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

Sheet codename not working
 

On Tue, 1 Jun 2010 04:32:02 -0700, Makelei
wrote:

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

Probably because ActiveWorkbook<Doc2

Maybe something like:

Workbooks(Doc2).Worksheets("Sheet2").Activate

Sheet codename not working
 

"Jacob Skaria" wrote in message
news:1E2085E6-4398-

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 (MVP - Excel)

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

Regards,
Peter T

Sheet codename not working
 

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,

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc