Using Worksheet CodeName and Avoiding .Select & .A

Question

In my workbook I frequently need to activate some sheets by using their CodeName then search for some texts within that sheet and use the row or column number of the cell that contains the text I'm looking for.

In that situations, I am using below kind of codes:

Sheet16.Select '(Using codename)
Cells.Find(What:="FIRST TEXT I'M LOOKING FOR", After:= _
        ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate

   FirstRow= ActiveCell.Row

    Cells.Find(What:="SECOND TEXT I'M LOOKING FOR", After:= _
        ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate

SecondRow = ActiveCell.Row

Rows(FirstRow & ":" & SecondRow + 1).EntireRow.Hidden = False

Eveything works well, but nowadays I am trying to improve my codes and I'd like to run my codes faster.

Now,

1- How can I reference my WorkSheets' CodeName easily?

(I'm looking for answer like ThisWorkbook.Worksheets("Sheet1") - not a function

Dim wb as Workbook, ws as Worksheet
set wb = ThisWorkbook
set ws = wb.CodeName(Sheet16) or wb.Sheet16 or sheet16
'then 
ws.Cells.Find(What ..........   rest of the code  ...... )

none of them working for CodeName property. Fully reference a worksheet by codename or Referencing sheets in another workbook by codename using VBA didn't answer my question.

2- How can I avoid using .Activate to use the result cell of Cells.Find() formula.

Again in that example I firstly search for specific text, which is :="FIRST TEXT I'M LOOKING FOR" in the first part of my code, and then I need to use that cell to get it's row number or use offset or anything , and because of that I feel myself obligated to use .Activate because,

FirstRow =  Cells.Find(What:="FIRST TEXT I'M LOOKING FOR", After:= _
        ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Row

kind of codes do not work as well. How to avoid using Select in Excel VBA macros here in that answer there are couple of suggestions but none of them helps me in this case. I tried to get an answer from the owner of this answer to avoid any duplicate question but he suggested me to ask a new question. (And as long as both of my questions belong to my example code and I'll connect them, I asked them together in one question.)

Answer 1

While setting a sheet variable to the codename, the limitation is you can use the codename in ThisWorkbook only i.e. the workbook which contains the code.

Consider this code...

Dim ws As Worksheet
Set ws = wsData 'where wsData is the CodeName of a sheet.

Now in your code you can manipulate or perform actions on ws sheet without activating or selecting it. Actually in case of CodeNames, you don't need to declare a sheet variable, you can directly refer to the sheet by using it's codename irrespective of which sheet is currently active.

like...

wsData.Cells.Clear
Set Rng = wsData.Range("A1").CurrentRegion

e.g. with your another example code

Dim ws As Worksheet
Set ws = wsData 'where wsData is the CodeName of a sheet.

FirstRow = ws.Cells.Find(What:="FIRST TEXT I'M LOOKING FOR", After:= _
        ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Row

'Or just (without declaring the ws sheet variable where wsData is the sheet code name)
FirstRow = wsData.Cells.Find(What:="FIRST TEXT I'M LOOKING FOR", After:= _
        ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Row