COUNTA AND COUNTIF in a macro

[itsnick]

What I'm trying to do is add a line where it counts the number of cells with either the word "NEW" in a certain row (Countif) or the number of cells with a value in a range(Counta). CountA is not working because if I run the macro twice it adds a second total line and messes both the count and total up. COUNTIF I just cant get to work because im a VBA SUPER NOOB and im just pieceing code together from this forum and the net.

Heres an example of what id like to do.

'NEW sheet
    Range("A4").AutoFilter Field:=8, Criteria1:="=NEW*"
    LR = Range("H" & Rows.Count).End(xlUp).Row
        If LR > 1 Then
            Range("A1:Q" & LR).Copy
            Sheets("NEW").Select
            Range("A1").PasteSpecial xlPasteAll
            LR = Range("G" & Rows.Count).End(xlUp).Row + 1
            Range("B" & LR) = "TOTAL"
            Range("E" & LR).FormulaR1C1 = "=SUM(R2C:R" & LR - 1 & "C)"
            Range("F" & LR).FormulaR1C1 = "=SUM(R2C:R" & LR - 1 & "C)"
            Range("H" & LR).FormulaR1C1 = "=COUNTIF DATA,*NEW)"
            Range("A1", "Q" & LR).Borders.Weight = xlThin
            Range("A" & LR, "Q" & LR).Font.Bold = True
            Cells.Columns.AutoFit
            ActiveWindow.DisplayGridlines = False
            Range("A5:Q5").Select
            ActiveWindow.FreezePanes = True
            ws.Activate
        End If

Current version of this macro.

Sub LARGEGROUPUPDATE()
'NBayon (10/21/2009)
Dim LR As Long, ws As Worksheet
Application.ScreenUpdating = False
Set ws = Sheets("ALL")

'Setup
    ws.Activate
    LR = Range("A" & Rows.Count).End(xlUp).Row
    
'NEW sheet
    Range("A4").AutoFilter Field:=8, Criteria1:="=NEW*"
    LR = Range("H" & Rows.Count).End(xlUp).Row
        If LR > 1 Then
            Range("A1:Q" & LR).Copy
            Sheets("NEW").Select
            Range("A1").PasteSpecial xlPasteAll
            LR = Range("G" & Rows.Count).End(xlUp).Row + 1
            Range("B" & LR) = "TOTAL"
            Range("E" & LR).FormulaR1C1 = "=SUM(R2C:R" & LR - 1 & "C)"
            Range("F" & LR).FormulaR1C1 = "=SUM(R2C:R" & LR - 1 & "C)"
            Range("A1", "Q" & LR).Borders.Weight = xlThin
            Range("A" & LR, "Q" & LR).Font.Bold = True
            Cells.Columns.AutoFit
            ActiveWindow.DisplayGridlines = False
            Range("A5:Q5").Select
            ActiveWindow.FreezePanes = True
            ws.Activate
        End If

 'RENEWALS sheet
    Range("A4").AutoFilter Field:=8, Criteria1:="=RWNC*", Operator:=xlOr, Criteria2:="=RWC*"
    LR = Range("H" & Rows.Count).End(xlUp).Row
        If LR > 1 Then
            Range("A1:Q" & LR).Copy
            Sheets("RENEWALS").Select
            Range("A1").PasteSpecial xlPasteAll
            LR = Range("G" & Rows.Count).End(xlUp).Row + 1
            Range("B" & LR) = "TOTAL"
            Range("E" & LR).FormulaR1C1 = "=SUM(R2C:R" & LR - 1 & "C)"
            Range("F" & LR).FormulaR1C1 = "=SUM(R2C:R" & LR - 1 & "C)"
            Range("A1", "Q" & LR).Borders.Weight = xlThin
            Range("A" & LR, "Q" & LR).Font.Bold = True
            Cells.Columns.AutoFit
            ActiveWindow.DisplayGridlines = False
            Range("A5:Q5").Select
            ActiveWindow.FreezePanes = True
            ws.Activate
        End If

 'TERMS sheet
    Range("A4").AutoFilter Field:=8, Criteria1:="=TERM*"
    LR = Range("H" & Rows.Count).End(xlUp).Row
        If LR > 1 Then
            Range("A1:Q" & LR).Copy
            Sheets("TERMS").Select
            Range("A1").PasteSpecial xlPasteAll
            LR = Range("G" & Rows.Count).End(xlUp).Row + 1
            Range("B" & LR) = "TOTAL"
            Range("E" & LR).FormulaR1C1 = "=SUM(R2C:R" & LR - 1 & "C)"
            Range("F" & LR).FormulaR1C1 = "=SUM(R2C:R" & LR - 1 & "C)"
            Range("A1", "Q" & LR).Borders.Weight = xlThin
            Range("A" & LR, "Q" & LR).Font.Bold = True
            Cells.Columns.AutoFit
            ActiveWindow.DisplayGridlines = False
            Range("A5:Q5").Select
            ActiveWindow.FreezePanes = True
            ws.Activate
        End If

'REINSTATEMENTS sheet
    Range("A4").AutoFilter Field:=8, Criteria1:="=REINS*"
    LR = Range("H" & Rows.Count).End(xlUp).Row
        If LR > 1 Then
            Range("A1:Q" & LR).Copy
            Sheets("REINSTATEMENTS").Select
            Range("A1").PasteSpecial xlPasteAll
            LR = Range("G" & Rows.Count).End(xlUp).Row + 1
            Range("B" & LR) = "TOTAL"
            Range("E" & LR).FormulaR1C1 = "=SUM(R2C:R" & LR - 1 & "C)"
            Range("F" & LR).FormulaR1C1 = "=SUM(R2C:R" & LR - 1 & "C)"
            Range("A1", "Q" & LR).Borders.Weight = xlThin
            Range("A" & LR, "Q" & LR).Font.Bold = True
            Cells.Columns.AutoFit
            ActiveWindow.DisplayGridlines = False
            Range("A5:Q5").Select
            ActiveWindow.FreezePanes = True
            ws.Activate
        End If

'CHANGE sheet
    Range("A4").AutoFilter Field:=8, Criteria1:="=CHANGE*"
    LR = Range("H" & Rows.Count).End(xlUp).Row
        If LR > 1 Then
            Range("A1:Q" & LR).Copy
            Sheets("CHANGE").Select
            Range("A1").PasteSpecial xlPasteAll
            LR = Range("G" & Rows.Count).End(xlUp).Row + 1
            Range("B" & LR) = "TOTAL"
            Range("D" & LR).FormulaR1C1 = "=SUM(R2C:R" & LR - 1 & "C)"
            Range("A1", "Q" & LR).Borders.Weight = xlThin
            Range("A" & LR, "Q" & LR).Font.Bold = True
            Cells.Columns.AutoFit
            ActiveWindow.DisplayGridlines = False
            Range("A5:Q5").Select
            ActiveWindow.FreezePanes = True
            ws.Activate
        End If
        
Range("A1").AutoFilter
LR = Range("B" & Rows.Count).End(xlUp).Row + 1
        
 'CERIDIAN sheet
    Range("A4").AutoFilter Field:=15, Criteria1:="=YES*"
    LR = Range("O" & Rows.Count).End(xlUp).Row
        If LR > 1 Then
            Range("A1:Q" & LR).Copy
            Sheets("CERIDIAN").Select
            Range("A1").PasteSpecial xlPasteAll
            Range("A1", "Q" & LR).Borders.Weight = xlThin
            Range("A" & LR, "Q" & LR).Font.Bold = True
            Cells.Columns.AutoFit
            ActiveWindow.DisplayGridlines = False
            Range("A5:Q5").Select
            ActiveWindow.FreezePanes = True
            ws.Activate
        End If
        
Range("A1").AutoFilter
LR = Range("B" & Rows.Count).End(xlUp).Row + 1

Cells.Columns.AutoFit
ActiveWindow.DisplayGridlines = False
Range("A5:Q5").Select
ActiveWindow.FreezePanes = True
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub