Copy data from Workbook Alpha & reorganize it in Workbook Bravo
 

Copy data from Workbook Alpha,
and summarize / reorganize it in Workbook Bravo.

Data Source :
Workbook Alpha Sheet1
Code Desc Cost
A1 Equipt. x 500
A2 Freight 200
B1 Equipt. y 700
B2 Freight 100

How do I group data on Left(Code,1), and,
If the Code suffix is 1, assign Cost to the Equipt. Cost Column
If the Code suffix is 2, assign Cost to the Freight Cost Column

Desired Result in Workbook Bravo Sheet1
Code Desc Equipt.Cost Freight Total
A Equipt. x 500 200 700
B Equipt. y 700 100 800

Thank you for your help,

Wayne

Copy data from Workbook Alpha & reorganize it in Workbook Bravo
 

Sub summarize()

With Workbooks("Alpha.xls").Sheets("Sheet1")
LastRowAlpha = .Cells(Rows.Count, "A").End(xlUp).Row
For AlphaRowCount = 2 To LastRowAlpha
code = Left(.Cells(AlphaRowCount, "A"), 1)
suffix = Mid(.Cells(AlphaRowCount, "A"), 2)
Desc = .Cells(AlphaRowCount, "B")
Cost = .Cells(AlphaRowCount, "C")

With Workbooks("Bravo.xls").Sheets("Sheet1")
LastRowBravo = .Cells(Rows.Count, "A").End(xlUp).Row

Set SearchRange = .Range("A2:A" & LastRowBravo)
Set c = SearchRange.Find(what:=code, LookIn:=xlValues)
If c Is Nothing Then
DataRow = LastRowBravo + 1
.Range("A" & DataRow) = code
Else
DataRow = c.Row
End If

If suffix = 1 Then
.Range("A" & DataRow) = code
.Range("B" & DataRow) = Desc
.Range("C" & DataRow) = Cost
.Range("E" & DataRow).Formula = _
"=C" & DataRow & _
"+D" & DataRow
Else
.Range("D" & DataRow) = Cost
End If
End With
Next AlphaRowCount
End With

End Sub

"u473" wrote:

Copy data from Workbook Alpha,
and summarize / reorganize it in Workbook Bravo.

Data Source :
Workbook Alpha Sheet1
Code Desc Cost
A1 Equipt. x 500
A2 Freight 200
B1 Equipt. y 700
B2 Freight 100

How do I group data on Left(Code,1), and,
If the Code suffix is 1, assign Cost to the Equipt. Cost Column
If the Code suffix is 2, assign Cost to the Freight Cost Column

Desired Result in Workbook Bravo Sheet1
Code Desc Equipt.Cost Freight Total
A Equipt. x 500 200 700
B Equipt. y 700 100 800

Thank you for your help,

Wayne