J
joecrabtree
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
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