Help in printing a sorted list of customers purchases

[Jonco]

I'm keeping track of an auction. I have a worksheet with 4 columns,
Item Number, Item description, Buyer and Amount = Columns A thru D.
What I want to do is sort the list by buyer and then copy just the items
from each buyer and paste that buyers items (and related data) to a
place in the spreadsheet (the printable receipt area), lets call that
starting at cell L6.
Then I can print his receipt and then repeat the task for each
subsequent buyer.

I have it working manually, (I can copy the buyers data to the receipt
area and then print the receipt) but it would be nice to automate the
process.

Any help would be greatly appreciated.

Jonco

 

[Gary Keramidas]

use autofilter.

if using excel 2003, enter a formula in the last row of column D
=subtotal(109,D2:d100)
(or whatever your last row is)


select a1:d1
then
data/filter/autofilter
select the buyer name from the dropdown in column C

you should see the transactions for the buyer with the total

 

[Gary Keramidas]

here's some code to automate it, change the sheet name if yours is different:

Sub summarize()
Dim ws As Worksheet
Dim lastrow As Long
Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row

ws.Range("D" & lastrow).Offset(1).Formula = _
"=subtotal(109,D2" & lastrow & ")"

With ws.Range("A11")
.AutoFilter
End With
End Sub

 

[Jonco]

Thanks Gary, I'll give that a try.