Excel VBA Type Mismatch Worksheet Object

Viewed 658

I am getting a compile error:

Type Mismatch

and cannot determine the source of the problem. I am sure it is something simple. The error shows when I call CreateUniqueEmpList with the ws objects as arguments (VBE specifically highlights the recordsWS argument.

Sub Main()

    Dim recordsWS As Worksheet
    Dim empsByJobTitles As Worksheet

    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False

    Set recordsWS = Sheets("Student&GroupTransDetails")
    Set empsByJobTitles = Sheets("Employees")

    Call CreateUniqueEmpList(ByVal recordsWS, ByVal empsByJobTitles)

End Sub

Sub CreateUniqueEmpList(tranWS As Worksheet, empWS As Worksheet)

    empWS.Cells.Clear

    tranWS.UsedRange.AdvancedFilter Action:=xlFilterCopy, _
        CopyToRange:=empsWS.Range("A1"), unique:=True

    empWS.Columns.AutoFit

End Sub

What I have done:

  1. Commented out call to CreateUniqueEmpList, set a break point, and checked to make sure the worksheet object was set. It was.
  2. Removed the parentheses from the method call. The whole line then looked like this: CreateUNiqueEmpList ByVal recordsWS, ByVal empsByJobTitles
  3. Changed the way in which I Set both worksheet objects by using Worksheets() instead of Sheets().

Any help would be greatly appreciated.

0 Answers
Related