Application.Match only working on Lookup tab active

I'm using Userforms and have 2 main worksheets. 1 (shE) collates the information selected, and 2 (shL) is where all available options are stored - these are my lookups.

shL has a list of teams on Row 2, every 4 columns, and on Row 3, a list of attributes for each team. So the structure is AB2 = Team1, AF2 = Team2, AJ2 = Team3. AB3 = Attribute1, AC3 = Attribute2, AD3 = Attribute3, AE3 = Attribute4, AF3 = Attribute1 etc..

Dim shE, shL as Worksheet
Dim FoundTeam as range
Dim c, cc, y, mtc as long
    Set shE = Sheets("EnteredData")
    Set shL = Sheets("Lookups")
    Set FoundTeam = shL.Range("AB2:BX2").Find(what:=shE.Range("D10"))
    c = FoundTeam.Column
    cc = c + 3
    y = shL.Cells(shL.Rows.Count, c).End(xlUp).Row
    ' the next line of code causes the run-time error 1004 if shL is not the last-active worksheet
    mtc = Application.Match(shE.Range("J3"), shL.Range(Cells(4, cc), Cells(y, cc)), 0)
        shE.Range("Q3").Value = WorksheetFunction.Index(shL.Range(Cells(4, c), Cells(y, c)), mtc) 'this is actually part of a loop so the shE.Range("Q3") is more like ("Q" & i)

For clarity, shE.Range("J3") stores the Attribute4 (selectable by the user) (found in shL 4th column of each Team) and shE.Range("D10") is the Team Name.

The issue I'm having is that the code works perfectly if shL is the last-active worksheet on Excel. However, if the last-active worksheet is shE (or any other worksheet), I will get the Run-time error 1004: Method 'Range' of object '_Worksheet' failed.

I don't understand why this code only works if shL is last-active. This will be used by several different people in my organization and they will not have shL visible to them.

What can I do to fix the run-time error, or is there a way to lookup/find/pull the information better?

Solution

This is the workaround I found. Not ideal, but it seems to work. In every attempt I made (Match and Find were both exhausted), the code worked great if I was on shL but not if I was elsewhere; the Match just wouldn't work consistently. So I decided against doing it in VBA!

'Variables
Dim shE As Worksheet, shL As Worksheet
Dim ClmnLtrc As String, ClmnLtrcc As String
Dim c As Long, cc As Long, y As Long, Q As Long
Dim FoundTeam As Range, FoundRange As Range

'Define variables
Set shE = Sheets("EnteredData")
Set shL = Sheets("Lookups")
Set FoundTeam = shL.Range("AB2:BX2").Find(what:=shE.Range("D10"))
    c = FoundTeam.Column
    cc = c + 3
    y = shL.Cells(shL.Rows.Count, c).End(xlUp).Row
    Q = shE.Range("Q" & Rows.Count).End(xlUp).Row
    ClmnLtrc = Split(Cells(1, c).Address, "$")(1)
    ClmnLtrcc = Split(Cells(1, cc).Address, "$")(1)

'Write the Index Match into the cell
shE.Range("Q3").Value = "=INDEX(Lookups!$" & ClmnLtrc & "4:$" & ClmnLtrc & y & ",MATCH(EnteredData!$P$" & i & ",Lookups!$" & ClmnLtrcc & "$4:$" & ClmnLtrcc & y & ",0))"

'Convert the result to text
    shE.Range("Q3:Q" & Q).Copy
    shE.Range("Q3").PasteSpecial Paste:=xlPasteValues

I hope this can help someone else one day. Just because we write in VBA, doesn't mean everything has to be in VBA.