Recreating a pivot table using code

[Roger on Excel]

I have a table of items and their quantities.

In column A I have the item names, which are often repeated and in column K
I have their quantities. The table spans rows 90 through 120

Is there a way to consolidate the quantities in the list programatically
without using a pivot table?

The consolidated table needs to appear below the main table with the items
in column A and the totals for each item in column K

Can anyone help?

 

[joel]

In the code below I used Advancefilter method to get the unique value
from range A90:A120 and then used SumIF to tget the totals from colu
K.


Sub GetTotals()

StartRow = 90
Endrow = 120
Set NameRange = Range("A" & StartRow & ":A" & Endrow)
Set SumRange = Range("K" & StartRow & ":K" & Endrow)
Set DestCell = Range("A122")

Range("A" & StartRow & ":A" & Endrow).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=DestCell, _
Unique:=True

'Use Sumif formula to get the totals
'Put formula in first cell
DestCell.Offset(0, 1).FormulaR1C1 = _
"=Sumif(" & NameRange.Address(ReferenceStyle:=xlR1C1) & _
",RC[-1]," & SumRange.Address(ReferenceStyle:=xlR1C1) & _
")"


'get Last Row of Unique values
LastRow = DestCell.End(xlDown).Row
Set DestRange = Range(DestCell.Offset(0, 1), Range("B" & LastRow))

'copy formula dow the column
DestCell.Offset(0, 1).Copy Destination:=DestRange
End Su

 

[joel]

In the code below I used Advancefilter method to get the unique value
from range A90:A120 and then used SumIF to tget the totals from colu
K.


Sub GetTotals()

StartRow = 90
Endrow = 120
Set NameRange = Range("A" & StartRow & ":A" & Endrow)
Set SumRange = Range("K" & StartRow & ":K" & Endrow)
Set DestCell = Range("A122")

Range("A" & StartRow & ":A" & Endrow).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=DestCell, _
Unique:=True

'Use Sumif formula to get the totals
'Put formula in first cell
DestCell.Offset(0, 1).FormulaR1C1 = _
"=Sumif(" & NameRange.Address(ReferenceStyle:=xlR1C1) & _
",RC[-1]," & SumRange.Address(ReferenceStyle:=xlR1C1) & _
")"


'get Last Row of Unique values
LastRow = DestCell.End(xlDown).Row
Set DestRange = Range(DestCell.Offset(0, 1), Range("B" & LastRow))

'copy formula dow the column
DestCell.Offset(0, 1).Copy Destination:=DestRange
End Su

 

[Roger on Excel]

Dear Joel,

Many thanks for your prompt help on this one. The code is elegant.

However, I tried your code and found that it enters the first item into the
consolidated list twice?!

Is there a way around this?

Roger

 

[joel]

Itr must be the isue with the filter Method in excel. The filter
expects a header row and if it doesn't have one will duplicates the 1s
entry. I think if you change StartRow = 90 from 90 to 89 it may fix th
problem