How to select the sheet by using internal sheet name

[ThrottleWorks]

Hi,

I am trying to define worksheet by using internal sheet name.

The reason is, the sheet names will get changed everyday so I have to use internal name.
I have defined activeworbook as ErRep, now I am trying to define sheet1 of ErRep workbook.

I tried various combinations but it is not working.

Can anyone help me in this please.

        Dim MyRng As Range
        Dim ErRep As Workbook
     
        Set ErRep = ActiveWorkbook
     
        'I want to define sheet1 of the ErRep in the following line
        'but this line considers sheet1 of the macro workbook.
     
        Dim Lr As Long
        Lr = Sheet1.Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).Row
     
        Dim Lc As Long
        Lc = Sheet1.Cells.Find("*", SearchOrder:=xlByColumns, _
        LookIn:=xlValues, SearchDirection:=xlPrevious).Column
     
        Set MyRng = Range(Cells(1, 1), Cells(Lr, Lc))

 

[shrivallabha]

Please go through this link.
http://www.ozgrid.com/VBA/excel-vba-sheet-names.htm

You are trying to do what the last line states as its drawback. However, this can be overcome by using approach like below.

First we write a function to get the name of worksheet using its codename for search:

Public Function strGetWkSheet(strWbName As String, strCodeName As String) As String
Dim wbSearch As Workbook
Dim wsSearch As Worksheet

Set wbSearch = Workbooks(strWbName)
strGetWkSheet = vbNullString

For Each wsSearch In wbSearch.Sheets
  '\\ Check for code name
  If wsSearch.CodeName = strCodeName Then
  strGetWkSheet = wsSearch.Name
  Exit For
  End If
Next

End Function

Following is example of how to use it in sub routine.

Public Sub Example()
Dim ErRep As Workbook
Dim Ws1 As Worksheet
Dim strCode As String

Set ErRep = ActiveWorkbook
'\\ Call our custom function
'\\ We pass workbook name as first argument and CodeName we are looking for as second argument
strCode = strGetWkSheet(ErRep.Name, "Sheet1")
'\\ Now that we have found out the Sheet we are looking for we set worksheet reference to that
'\\ particular sheet
If Len(strCode) > 0 Then
  Set Ws1 = ErRep.Worksheets(strCode)
Else
  MsgBox "Can't find the specified sheet!", vbExclamation
End If

End Sub

 

[ThrottleWorks]

Hi Shrivallabha, good aftenoon & thanks for the help.
I am checking the link you have provided.

I am trying your code, will share the results ASAP.

 

[ThrottleWorks]

Hi Shrivallabha, thanks for the effort, link and time you took for my problem.
You solution is really very good, but I was not able to understand it fully (my fault).

I changed my code from "Sheet1.Cells" to "ErRep.Sheets(1).Cells".
Tried various combinations, seems like working.

Have a nice day ahead.

           Dim MyRng As Range
           Dim ErRep As Workbook
   
           Set ErRep = ActiveWorkbook
   
           Dim Lr As Long
           Lr = ErRep.Sheets(1).Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).Row
   
           Dim Lc As Long
           Lc = ErRep.Sheets(1).Cells.Find("*", SearchOrder:=xlByColumns, _
           LookIn:=xlValues, SearchDirection:=xlPrevious).Column
   
          Set MyRng = Range(Cells(1, 1), Cells(Lr, Lc))

P.S. - Done !

 

[shrivallabha]

Following code means First sheet in the workbook by position:

ErRep.Sheets(1)

which means your code will work as long as you do not shuffle your sheets. You may want to protect workbook (for structure) so that users do not shuffle the sheets accidentally.

Now coming back to the function posted here's how it should work in your case. Copy and paste the function to the same module where your current code is. Then above code will be changed [post #4] as below:

  Dim MyRng As Range
  Dim ErRep As Workbook
  Dim WsRep As Worksheet '// Variable added
  Dim strCode As String  '// Variable added
   
  Set ErRep = ActiveWorkbook
  '// Suppose we want to search sheet which has CodeName Sheet1
  strCode = strGetWkSheet(ErRep.Name, "Sheet1")
   
  '// Function will return it's name in the variable strcode which we will use
  Set WsRep = ErRep.Worksheets(strCode)
   
  '// ErRep.Sheets(1) replaced by WsRep for Lr and Lc
  Dim Lr As Long
  Lr = WsRep.Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).Row
   
  Dim Lc As Long
  Lc = WsRep.Cells.Find("*", SearchOrder:=xlByColumns, _
  LookIn:=xlValues, SearchDirection:=xlPrevious).Column
   
  Set MyRng = WsRep.Range(Cells(1, 1), Cells(Lr, Lc))

Let me know should you find something unclear!

 

[ThrottleWorks]

Hi Shrivallabha, thanks for helping me again.

You are right, "code will work as long as you do not shuffle your sheets".
The report will have only 2 sheets, so do not think will face any problem.

I am trying the code you have provided, the reason I could not use it earlier was, I find it slightly difficult.
I thought if it gives me bug (in future) for any reason, I will not be able to solve it due to my lack of understanding.

Good night.