Copy data from Workbook Alpha & reorganize it in Workbook Bravo

U

u473

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
 
J

Joel

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top