Sorted data macro not working. Advice request...

[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

 

[joel]

You are filtering on Unique values which will reduce the number of rows in
your output. Your SUMIF is using CodeRane and SUmRange which are set prior
to you performing the Filter to get the Unique values. I thinnk you may need
to set the CodeRange and SumRange prior to performing the SUMIF. I didn't
find anyosrt in the code you posted. if you are doing sorting make sure you
don't include any blank cells/rows in your code. The blank cells in the sort
will end up on the bottom of your worksheet that may be causing the problem.

 

[joecrabtree]

You are filtering on Unique values which will reduce the number of rows in
your output.  Your SUMIF is using CodeRane and SUmRange which are set prior
to you performing the Filter to get the Unique values.  I thinnk you may need
to set the CodeRange and SumRange prior to performing the SUMIF.  I didn't
find anyosrt in the code you posted.  if you are doing sorting make sure you
don't include any blank cells/rows in your code.  The blank cells in the sort
will end up on the bottom of your worksheet that may be causing the problem.

Sorry when I said sort, i really meant filter using the following
code:

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

I will try what you have suggested.

Thanks

Joe

 

[joecrabtree]

Sorry when I said sort, i really meant filter using the following
code:

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

I will try what you have suggested.

Thanks

Joe

Im still not having any luck. Basically the macro SUMIFs all the data
still. It appears that the filter is working initilally, but then
someway through the code it is taken off, and the SUMIF function is
applied to ALL rows.

Any more ideas?

Thanks

Joe