-1

My question is rather complex so I will try to use examples and be clear as possible. In one table I have values that are populated based off formula:

Name  Timestamp  Datedif1
123    1-Oct        1     (date dif between today and timestamp, formula based)

In another table I want to be able to enter the name and populate some of the information from the first table:

Name  Timestamp  Datedif1  Datedif2
123     2-Oct       1         0

Datedif1 being drawn from the first table, Datedif2 being the new timestamp (entered into the second table and current date).

The problem is, I want to be able to delete the row from the first table after it has copied over Datedif1.

Essentially I would like to do a VLOOKUP() based off the name, to get Datedif1, but have it paste the value of Datedif1 into the cell versus constantly looking to find the name and relying on the first table to remain populated with the information.

I am pretty sure I need VBA code to do this since formulas will always rely on pointing to cells, and I do not know a formula to copy and paste a static value without referring to another cell. I am not versed in VBA code but have programmed C a long time ago.

Thanks Eric F for the start. I think I need to clairfy my desire activity a little more. What I would like to do is once I manually enter Name in table 2, the Datedif1 value will be copy and pasted from table 1, as a value. Theory would look like this: if Name = "", "" If Name = 123, VLOOKUP(123, table 1, return datedif1(column 3), FALSE) paste the vlookup as a value so it will not chnage if table 1 gets deleted

Thanks.

1
  • Would this be something you need done once or something you want to be reusable?
    – Eric F
    Oct 2, 2014 at 15:06

1 Answer 1

0

enter image description here

I have made an example that should help you with this. The data is different but hopefully you can follow it to make your own macro. I have two sheets like you do. Sheet1 has my initial data. Sheet2 will be my filtered data.

Public Sub do_stuff_and_things()
Dim i As Integer
Dim insertrow As Integer
i = 1
Do Until i = Range("'Sheet1'!A1").End(xlDown).Row + 1

 If Range("'Sheet1'!A" & i).Value = "Joe" Then
  If Range("'Sheet1'!B" & i).Value = "2" Then
   'conditions are true so copy to next sheet
   insertrow = Range("'Sheet2'!A1").End(xlDown).Row + 1
   Range("'Sheet2'!A" & insertrow & ":D" & insertrow).Value = Range("'Sheet1'!A" & i & ":D" & i).Value
  End If
 End If

i = i + 1
Loop


End Sub

Upon running the macro, it will go through sheet1 using the conditions that I specified with my IF statements. If the conditions are true then it will proceed to copy the entire line to the next blank row on Sheet2. I specified the row to be columns A through D but of course change it to yours.

Hope this helps you to get started at least.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .