Please please help, I have been trying to figure out this code all week and can not get the IF statement to work. It works without the If statement (highlighted in red) that is selecting only the rows that say "Jane Doe". Does anyone have any suggestions?
I attached a workbook of what I want it to look like. The Full tab has the raw data and the new tabs has the data filtered out how I want it to be.
This is the code I have right now.
Sub UseAutoFilterDead()
Dim ctrl As Long
ctrl = Sheet1.Range("E2:E1000").Value
If ctrl = "Jane DOe ext 44723" Then
Sheet1.AutoFilterMode = False
With Sheet1.Range("A1:R10000")
.AutoFilter Field:=10, Criteria1:="10", Operator:=xlTop10Items
.SpecialCells(xlCellTypeVisible).Copy Sheet3.Range("A1")
End With
Sheet1.AutoFilterMode = False
Sheet1.AutoFilterMode = False
With Sheet1.Range("A1:R10000")
.AutoFilter Field:=12, Criteria1:="10", Operator:=xlTop10Items
.SpecialCells(xlCellTypeVisible).Copy Sheet4.Range("A1")
End With
Sheet1.AutoFilterMode = False
Sheet1.AutoFilterMode = False
With Sheet1.Range("A1:R10000")
.AutoFilter Field:=14, Criteria1:="10", Operator:=xlTop10Items
.SpecialCells(xlCellTypeVisible).Copy Sheet5.Range("A1")
End With
Sheet1.AutoFilterMode = False
End If
End Sub
Bookmarks