Header function in VBA

I have a function in Excel VBA that searches for headers and uses them to define certain ranges. It looks like it would work perfectly fine but when I call it, it breaks on this line: Set rngHeaders = Intersect(Worksheets(sheetName).UsedRange, Worksheets(sheetName).Rows(ROW_HEADERS)) and I am not sure why. It gives me a subscript out of range error. The sheetName variable is a string and the sheet Sheet8 (that I am passing into it) does exist. I have posted the entire function below in addition to the line where I call it in the Sub. Any help would be greatly appreciated.

Function FindHeader(HEADER_NAME As String, sheetName As String) As Range

    Dim rngHeaders As Range
    Dim rngHdrFound As Range

    Const ROW_HEADERS As Integer = 1

    Set rngHeaders = Intersect(Worksheets(sheetName).UsedRange, Worksheets(sheetName).Rows(ROW_HEADERS))
    Set rngHdrFound = rngHeaders.Find(HEADER_NAME)

    If rngHdrFound Is Nothing Then
       MsgBox ("ERROR: Cannot find appropriate header.")
        Exit Function
    End If

  Set FindHeader = Range(rngHdrFound.Offset(1), rngHdrFound.End(xlDown))

End Function

Line that calls it:

Sheet8.Activate
sheetName = "Sheet8"
Set rng1 = FindHeader("Client Exclusion List", sheetName)

Solution

VBA distinguishes between a Worksheets .Name property and the .CodeName property. .CodeName refers to the name visible in the VBA Project Tree View (usually Sheet1, Sheet2, etc). The .Name is found in parenthesis right after the .CodeName. Additionally, the .Name is the value in the Worksheet Tab on the Excel front end.

If you pass the .CodeName to the Worksheets Collection, it will generate an error (unless the .CodeName and .Name are identical).

Hopefully this helps.