vba_ancheck04

Sub ANfailed()
Dim i%, h%
Dim afax As String
Dim myws As Worksheet
Application.ScreenUpdating = False
For i = Sheets.Count To 1 Step -1
    Application.DisplayAlerts = False
    If Sheets(i).Name = "ANfail" Then
        Sheets(i).Delete
    End If
    Application.DisplayAlerts = True
Next
    
Set myws = Sheets.Add(Count:=1)
myws.Name = "ANfail"

i = Sheets("Page1_1").[C666666].End(3).ROW
Sheets("Page1_1").Activate
Range("A13:R" & i).Copy
Sheets("ANfail").Activate
[a1].Select
ActiveSheet.Paste
[s1] = "Total Count"
[t1] = "Carrier"
[u1] = "Fax"
[v1] = "Fax V"
[W1] = "Total S"
i = Sheets("ANfail").[C666666].End(3).ROW

With Range("I1:I" & i)
    .AutoFilter Field:=1, Criteria1:="GN"
    .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    .AutoFilter
    '.Clear
End With

i = Sheets("ANfail").[C666666].End(3).ROW
For h = 2 To i
    Cells(h, 19) = Application.WorksheetFunction.CountIfs(Range("B1:B" & i), Cells(h, 2), Range("K1:K" & i), "<>")
    Cells(h, 20) = Application.WorksheetFunction.CountIfs(Range("B1:B" & i), Cells(h, 2), Range("K1:K" & i), "Carrier Website")
    If InStr(UCase(Cells(h, 11)), "@FAX") > 0 Then Cells(h, 21) = "Y"
    Cells(h, 22) = Application.WorksheetFunction.CountIfs(Range("B1:B" & i), Cells(h, 2), Range("U1:U" & i), "Y")
    If Range("s" & h) - Range("t" & h) - Range("v" & h) > 0 Then Cells(h, 23) = "D"
Next

With Range("W1:W" & i)
    .AutoFilter Field:=1, Criteria1:="D"
    .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    .AutoFilter
    '.Clear
End With

i = Sheets("ANfail").[C666666].End(3).ROW

With Range("A1:W" & i)
    .Borders.LineStyle = xlContinuous
    .Font.Name = "Arial"
    .Font.Size = 10
End With
Columns("A:W").AutoFit
[a1].Select

MsgBox "Done"

Application.ScreenUpdating = True
End Sub