I am attempting to copy all unique values from a specific column on a worksheet titled "Raw_Data" to a a worksheet titled "temp". This was working for a bit, but due to raw data input columns varying for multiple reports, I can't place a static column such as "I" in my code for the advanced filter. I've successfully managed to identify the needed column by header name(which will not change), but can't pass that variable into my advanced filter. I get a type mismatch. Any help would be appreciated.
Code:
Sub Sample()
Dim ws As Worksheet
Dim lRow As Long, aCol As Long
Dim aCell As Range
Dim fRange As Range
Set ws = Sheets("Raw_Data")
With ws
Set aCell = .Rows(1).Find("Current Lead Case Manager")
aCol = aCell.Column
.Range(Cells(1, "aCol")).AdvancedFilter xlFilterCopy, , Sheets("Temp").Range("A1"), True
End With
End Sub