macro - copying data in a row to another sheet

gretti24

New Member
Joined
Feb 12, 2013
Messages
17
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

Well-known Member
Joined
Feb 1, 2016
Messages
2,339
Office Version
2013
Platform
Windows
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

New Member
Joined
Feb 12, 2013
Messages
17
The first tab the "data tab" contains information that looks similar to this
2016 CostCarryover costProjectConstruction DateConstruction CostTopDateTopCostHorizontalDate HorizontalCostTILDateTILCost
8732301/2/201650002/3/20165000004/1/201620000005/1/2016200000
3456781/3/201650002/5/20165000004/4/201620000005/5/2016200000

<tbody>
</tbody>




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".
Construction1/2/20165000
Top 2/3/2016500000
Horizontal4/1/20162000000
TIL5/1/2016200000

<tbody>
</tbody>







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

Well-known Member
Joined
Feb 1, 2016
Messages
2,339
Office Version
2013
Platform
Windows
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)

Code:
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:
ABC
1
2Construction01/02/20165000
3Top02/03/2016500000
4Horizontal04/01/20162000000
5TIL05/01/2016200000
6Construction01/03/20165000
7Top02/05/2016500000
8Horizontal04/04/20162000000
9TIL05/05/2016200000

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</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

New Member
Joined
Feb 12, 2013
Messages
17
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

Well-known Member
Joined
Feb 1, 2016
Messages
2,339
Office Version
2013
Platform
Windows
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.
 

Forum statistics

Threads
1,081,417
Messages
5,358,553
Members
400,503
Latest member
RedSquirrel

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top