Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
1.Open workbook. 2 copy data. 3 replace data. 4 save workbook. | Excel Programming | |||
loop through a column on a workbook copying data on each row to another workbook, then copy data back to the original workbook | Excel Programming | |||
Copy Data from Workbook into specific Worksheet in other Workbook? | Excel Discussion (Misc queries) | |||
Import/Copy some data from one workbook to a similar workbook | Excel Programming | |||
Selecting data from 1 workbook to copy and paste to a 2nd workbook | Excel Programming |