Jay,

Try this - I wasn't sure what to do with columns 3 through 6, so I just summed them....

HTH,
Bernie
MS Excel MVP


Sub TryNow()
Dim myR As Range
Dim myC1 As Range
Dim myC2 As Range
Dim myV As String
Dim myRow As Long

myRow = Cells(Rows.Count, 1).End(xlUp)(4).Row

On Error GoTo Done
While True
myV = ""
Set myR = Range("A1").CurrentRegion
myR.AutoFilter Field:=2, Criteria1:=Range("B2").Value
Set myC1 = myR.Offset(1, 0).Resize(myR.Rows.Count - 1)

For Each myC2 In myC1.Columns(1).SpecialCells(xlCellTypeVisible)
If myV = "" Then
myV = myC2.Value
ElseIf myC2.Value < "" Then
myV = myV & ", " & myC2.Value
End If
Next myC2
Cells(myRow, 1).Value = myV
Cells(myRow, 2).Value = Range("B2").Value
With Range("C" & myRow).Resize(, 4)
.FormulaR1C1 = "=SUBTOTAL(9,R2C:R[-4]C)"
.Value = .Value
End With
myC1.EntireRow.Delete
myRow = Cells(Rows.Count, 1).End(xlUp)(2).Row
ActiveSheet.ShowAllData
Wend

Done:
Range("A:A").SpecialCells(xlCellTypeBlanks).Entire Row.Delete
Range("A1").CurrentRegion.AutoFilter
End Sub




***********************************************88 88

I do nto want to make a new sheet for each catalog number. I want to
copy all catalog types from the data base to another workbook and
concatenate the item numbers.

A = Item#
B= Catalog

Row 1 is A = 1234 and B = 2
Row 2 is A = 1235 and B = 3
Row 3 is A = 1236 and B = 2

I need the code to find each of the different catalog numbers and move
it to another workbook so the new workbook woudl look like this
Row 1 is A = 1234, 1236 and B = 2
Row 2 is A = 1235 and B = 3

I hope this paints a better picture of what I am trying to accomplish.

thanks,
Jay