Windows 7 Autofill error in macro

[bk77]

We have recently upgraded to Windows 7 and a previously written macro is now encountering an error.

The macro determines the unique values in column AG and then totals them at the bottom (note that the values in AG can be repeated numerous times). However, if there is only 1 unique value in AG, the macro is getting confused and returning a 1004 error. As I have this macro cycling through a book with nearly 100 pages, this is proving to be a pain.

Please see code below and thank you in advance.

Dim LastRow As Long
    Dim newLR As Long
    LastRow = Cells(Rows.Count, "af").End(xlUp).Row
    Range("AF1:AF" & LastRow).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
            "AF" & LastRow + 5), Unique:=True
    newLR = Cells(Rows.Count, "af").End(xlUp).Row
    Range("AG" & LastRow + 6).FormulaR1C1 = "=SUMIF(R2C[-1]:R[-4]C[-1],RC[-1],R2C[3]:R[-4]C[3])"
    Range("AG" & LastRow + 6).NumberFormat = "$#,##0.00"
    Range("AG" & LastRow + 6).AutoFill Destination:=Range("AG" & LastRow + 6 & ":" & "AG" & newLR), Type:=xlFillDefault
End Sub

[bk77]

bump - I have done a few searches for solutions and haven't been able to implement successfully.

[shg]

    Dim LastRow     As Long
    Dim newLR       As Long

    LastRow = Cells(Rows.Count, "AF").End(xlUp).Row
    Range("AF1:AF" & LastRow).AdvancedFilter Action:=xlFilterCopy, _
                                             CopyToRange:=Cells(LastRow + 5, "AF"), _
                                             Unique:=True
    newLR = Cells(Rows.Count, "AF").End(xlUp).Row
    With Range("AG" & LastRow + 6 & ":" & "AG" & newLR)
        .FormulaR1C1 = "=SUMIF(R2C[-1]:R[-4]C[-1],RC[-1],R2C[3]:R[-4]C[3])"
        .NumberFormat = "$#,##0.00"
    End With

[bk77]

works like a charm. Thank you!