Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro - Add the same CC results before creating a line of data
If anyone can help on this, I would be very grateful. I need a macro to
workout a summary result prior to creating a listing in another sheet (please use the example below) 1) Copy the following into sheet1; BOTH P&L BS ERROR Cost1 Cost2 Cost3 CC Emp No Emp Name Cost1 Cost2 Cost3 1100 A xxxxx 100 5000 1000 1100 B xxxxx 100 -10000 1000 2710 C xxxxx 100 -2000 2710 D xxxxx -200 5000 1000 8400 E xxxxx 100 5000 1000 2) Create a Sheet2; 3) Copy the following macro in a module and run; Sub Create_Journal() Start_Cell = "A1" With Sheets("Sheet1") Last_Row = .Range(Start_Cell).Offset(3, 1).End(xlDown).Row Last_Column = .Range(Start_Cell).Offset(0, 4).End(xlToRight).Column Set Cost_Info_Range = .Range(.Range(Start_Cell).Offset(0, 4), ..Cells(Last_Column)) End With Row_Count = 1 For Each Parent_Cell In Cost_Info_Range If Parent_Cell = "P&L" Then Cost_Group = Cells(2, Parent_Cell.Column) With Sheets("Sheet1") Set Cost_Amount_Range = .Range(.Cells(Parent_Cell.Row, Parent_Cell.Column).Offset(3, 0), .Cells(Last_Row, Parent_Cell.Column)) End With For Each First_Child_Cell In Cost_Amount_Range If First_Child_Cell < 0 Then Cost_Center = Cells(First_Child_Cell.Row, "B") Cost_Amount = Cells(First_Child_Cell.Row, First_Child_Cell.Column) With Sheets("Sheet2") .Range("A" & Row_Count) = Cost_Group .Range("B" & Row_Count) = Cost_Center If First_Child_Cell 0 Then .Range("C" & Row_Count) = Cost_Amount Else .Range("D" & Row_Count) = Cost_Amount End If Row_Count = Row_Count + 1 End With End If Next First_Child_Cell End If Next Parent_Cell End Sub 4) If done correctly Sheet2 will show the following; Cost2 1100 5000 Cost2 1100 -10000 Cost2 2710 5000 Cost2 8400 5000 5) However I would like the macro to first add all results for a CC before creating a line, so that the file data should look like this; Cost2 1100 -5000 Cost2 2710 5000 Cost2 8400 5000 6) I am lost on exactly how to approach this change? Many thanks for anyone who can help or point me in the right direction Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a macro to line up data in 2 worksheets. | Excel Worksheet Functions | |||
Creating a macro to line up data in 2 worksheets. | Excel Worksheet Functions | |||
Creating a macro to line up data in 2 worksheets. | Excel Worksheet Functions | |||
Creating a macro to line up data in 2 worksheets. | Excel Worksheet Functions | |||
Creating macro to lookup data | Excel Discussion (Misc queries) |