All,

I have the following code, which I want to first sort the data as per
the criteria, and then SUMIF and summarize on an output sheet. The
SUMIF part works fine on its own, and the sort part works fine on its
own. However when I put them together, the SUMIF works on ALL of the
data rather than just the sorted selection. Any ideas where I could be
going wrong?

Thanks in advance for your help,

Regards

Joseph Crabtree

Sub QTY()
With Sheets("Data")
LastRow = Sheets("Data").Range("R" & Rows.Count).End(xlUp).Row
Set CodeRange = .Range("R2:R" & LastRow)
Set SumRange = .Range("U2:U" & LastRow)

End With

Sheets("Data").Select
Rows("1:1").Select
Range("K1").Activate
Selection.AutoFilter
Selection.AutoFilter Field:=11, Criteria1:="Kovácsolás"

Sheets("data").Activate
Range("R1", "R" & LastRow).Select
Selection.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Selection.Copy Sheets("output").Range("A20")
ActiveSheet.ShowAllData

Set CriteriaRange = Sheets("Output").Range("A21")
For r = 2 To Sheets("Output").Range("A21").End(xlDown).Row
Total = WorksheetFunction.SumIf(CodeRange, CriteriaRange,
SumRange)
CriteriaRange.Offset(0, 1) = Total
Set CriteriaRange = CriteriaRange.Offset(1, 0)
Next



With Sheets("Data")
LastRow = Sheets("Data").Range("R" & Rows.Count).End(xlUp).Row
Set CodeRange = .Range("R2:R" & LastRow)
Set SumRange = .Range("AC2:AC" & LastRow)

End With

Sheets("data").Activate
Range("R1", "R" & LastRow).Select
Selection.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Selection.Copy Sheets("output").Range("A1")
ActiveSheet.ShowAllData

Sheets("data").Activate
Range("AC1").Select
Selection.Copy Sheets("output").Range("C20")

Sheets("data").Activate
Range("U1").Select
Selection.Copy Sheets("output").Range("B20")


Set CriteriaRange = Sheets("Output").Range("A21")
For r = 2 To Sheets("Output").Range("A21").End(xlDown).Row
Total = WorksheetFunction.SumIf(CodeRange, CriteriaRange,
SumRange)
CriteriaRange.Offset(0, 2) = Total
Set CriteriaRange = CriteriaRange.Offset(1, 0)
Next


End Sub