VBA to move and concatenate

[jlclyde]

I am looking to move data from one workbook to another and consolidate
data.

Here is what I have
A = Item #
B = Catalog

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

What I want
New Workbook
Row 1 is A = 1234, 1236 and B = 1
Row 2 is A = 1235 and B = 2

Thanks,
Jay

 

[Joel]

Sub combine()

folder = "C:\temp\test\"
Filename = "abc_1.xls"

Workbooks.Open Filename:=folder & Filename
Set oldbk = ActiveWorkbook

With ThisWorkbook
oldbk.ActiveSheet.Copy _
after:=.Sheets(.Sheets.Count)
Set newsht = .ActiveSheet

oldbk.Close

newsht.Cells.Sort _
Key1:=Range("B1"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

RowCount = 1
Do While Range("A" & RowCount) <> ""
If Range("B" & RowCount) = _
Range("B" & (RowCount + 1)) Then

data = Range("A" & (RowCount + 1))
If Range("A" & RowCount) = "" Then
Range("A" & RowCount) = data
Else
Range("A" & RowCount) = _
Range("A" & RowCount) & ", " & data
End If
Rows(RowCount + 1).Delete
Else
RowCount = RowCount + 1
End If
Loop

End With

End Sub