macro - copying data in a row to another sheet

[gretti24]

Hi all,

I am a novice to vba and macros so I apologize in advance.

On one tab (data), I have a list of project numbers in column A ranging from A2:A12. Each project has a Construction date (column b), Construction cost (column C), Top phase date (column d), top phase cost (column e) so on and so forth thru column m.

On another tab (input), I have all the phases (construction, top hole etc) listed out in column A, associated dates in column b and costs in column c.

I am trying to run each project thru this input tab with their associated dates and costs which basically outputs a cashflow to another tab (capex tab).

Finally, I hope to copy each project number and their cashflow from the capex tab to a new sheet. So, it will have to go from row to row in the data tab pulling dates and costs into the input tab and then copying the cashflow from the capex tab into a new sheet.

Any help or advice on how to start this would be greatly appreciated!

Thanks!

 

[Akuini]

It is easier to find a solution when the real samples are provided.
Can you provide visual data (table) of your data sample (without sensitive data, off course):
first, the table of your data that you already have in each sheet
second, the table of the result you expect

 

[gretti24]

The first tab the "data tab" contains information that looks similar to this
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]2016 Cost[/TD]
[TD]Carryover cost[/TD]
[TD]Project[/TD]
[TD]Construction Date[/TD]
[TD]Construction Cost[/TD]
[TD]TopDate[/TD]
[TD]TopCost[/TD]
[TD]HorizontalDate [/TD]
[TD]HorizontalCost[/TD]
[TD]TILDate[/TD]
[TD]TILCost[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]873230[/TD]
[TD]1/2/2016[/TD]
[TD]5000[/TD]
[TD]2/3/2016[/TD]
[TD]500000[/TD]
[TD]4/1/2016[/TD]
[TD]2000000[/TD]
[TD]5/1/2016[/TD]
[TD]200000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]345678[/TD]
[TD]1/3/2016[/TD]
[TD]5000[/TD]
[TD]2/5/2016[/TD]
[TD]500000[/TD]
[TD]4/4/2016[/TD]
[TD]2000000[/TD]
[TD]5/5/2016[/TD]
[TD]200000[/TD]
[/TR]
</tbody>[/TABLE]




the second tab looks like the following. The goal being to go thru the list in the data tab and pull the information project by project (row by row) to the second tab, the "input tab".
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Construction[/TD]
[TD]1/2/2016[/TD]
[TD]5000[/TD]
[/TR]
[TR]
[TD]Top [/TD]
[TD]2/3/2016[/TD]
[TD]500000[/TD]
[/TR]
[TR]
[TD]Horizontal[/TD]
[TD]4/1/2016[/TD]
[TD]2000000[/TD]
[/TR]
[TR]
[TD]TIL[/TD]
[TD]5/1/2016[/TD]
[TD]200000[/TD]
[/TR]
</tbody>[/TABLE]







the third tab, the "output tab" contains data which is needed from two different cells in A2 and B2. The workbook is a bit more complex than what is presented here, but all i really need to accomplish is to pull the information from the first tab over to the second and then copy the output from the third tab in cells A2 and B2 back over to the first tab where it is noted as 2016 Cost (A2) and Carry Over Cost (B2).

I hope this helps.

 

[Akuini]

The goal being to go thru the list in the data tab and pull the information project by project (row by row) to the second tab, the "input tab".

Try this macro.
Asumming:
The header start in cell A1.
The sheets name are “Data” and “Input” (you may change this)

Sub copyData1()
Dim i As Long
Dim j As Long
Dim k As Long
Dim rc As Long
Dim rx As Long
Dim rm As Long
Dim ri As Long
Dim ws1 As Worksheet
Dim ws2 As Worksheet

Set ws1 = Sheets("[COLOR=#800000]data[/COLOR]")
Set ws2 = Sheets("[COLOR=#800000]input[/COLOR]")

 ws1.Activate
 rc = ws1.Cells(1, Columns.count).End(xlToLeft).Column
 rx = ws1.Cells(Rows.count, "D").End(xlUp).row
 rm = ws2.Cells(Rows.count, "A").End(xlUp).row + 1
 i = 1
 For k = 2 To rx
      ri = ws2.Cells(Rows.count, "A").End(xlUp).row + 1
        For j = 4 To rc Step 2
             a = Cells(1, j)
             b = Cells(i + 1, j)
             c = Cells(i + 1, j + 1)
             With ws2
              .Cells(ri, 1) = Cells(1, j)
              .Cells(ri, 2) = Cells(i + 1, j)
              .Cells(ri, 3) = Cells(i + 1, j + 1)
              ri = ri + 1
             End With
         Next j
     i = i + 1
 Next k
 
 ws2.Activate
 For i = rm To ri - 1
    Cells(i, 1) = Trim(Mid(Cells(i, 1), 1, Len(Cells(i, 1)) - 4))
 Next
End Sub

The result:

	A	B	C
Construction
Top
Horizontal
TIL
Construction
Top
Horizontal
TIL

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]01/02/2016[/TD]
[TD="align: right"]5000[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]02/03/2016[/TD]
[TD="align: right"]500000[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]04/01/2016[/TD]
[TD="align: right"]2000000[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]05/01/2016[/TD]
[TD="align: right"]200000[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]01/03/2016[/TD]
[TD="align: right"]5000[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]02/05/2016[/TD]
[TD="align: right"]500000[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]04/04/2016[/TD]
[TD="align: right"]2000000[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]05/05/2016[/TD]
[TD="align: right"]200000[/TD]

</tbody>

input​

and then copy the output from the third tab in cells A2 and B2 back over to the first tab where it is noted as 2016 Cost (A2) and Carry Over Cost (B2).

Do you mean you want to copy cells A2 & B2 in third sheet to cells A2 & B2 in sheet Data?

 

[gretti24]

So I don't want to copy all of the projects below one another like in the result above, but rather "run" each project for economics, we'll say, and then take the result of the economics and copy them to the first sheet, next to the project. So, when the first projects costs are inputted into the second tab, a calculation is run to project the projects outcome and the third tab gives the results of the project in cells A2 and B2, which I want to copy over to the respective project in the first tab so I can reference later. I'm not sure if maybe a data table is a better route to go with this scenario?

 

[Akuini]

Hm, sorry I don't understand what you want.
Maybe you can upload your workbook (without sensitive data) somewhere (maybe dropbox), showing the data you have and the result you want (the 'before & after') and explain the steps to get the result.