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:
- Commented out call to
CreateUniqueEmpList, set a break point, and checked to make sure the worksheet object was set. It was. - Removed the parentheses from the method call. The whole line then looked like this:
CreateUNiqueEmpList ByVal recordsWS, ByVal empsByJobTitles - Changed the way in which I
Setboth worksheet objects by usingWorksheets()instead ofSheets().
Any help would be greatly appreciated.