VBA worksheet change copy values derived by formula to adjacent column in same worksheet in Excel table

[labfm1]

I've been searching all over for a couple of hours and it seems what I am looking for is either too simple to have been requested. I see far more complex requests but I still can't figure out how to do it.

I have an excel table where users will enter data in columns A3 -> D3. Column E is hidden and contains a simple formula of =row(). Essentially to generate an ID number for each entry. So if they enter data in A3->D3, the formula in E3 is row(D3), which will return the number 3.

Since it's in excel table format, as the user enters more entries, it will expand to include the formula in column E.

What I want to do, in column F, is copy the value generated in E. So if a user enters 10 entries starting from A3 to D12, the formula in column E will generate values of 3 through 10, and what I want is a VBA (on worksheet change) that will auto copy the values of 3 through 10 and paste them in column F, starting from F3 through F12 (adjacent to the formula in column E).

I expect users to input data over a wide range; anywhere from 50 entries up to 2000 entries. Can someone help with this?

 

[JLGWhiz]

You can try this. It should work with the table.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
    If Cells(Target.Row, 5) <> "" Then Cells(Target.Row, 6) = Cells(Target.Row, 5).Value
Application.EnableEvents = True
End Sub

 

[labfm1]

You can try this. It should work with the table.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
    If Cells(Target.Row, 5) <> "" Then Cells(Target.Row, 6) = Cells(Target.Row, 5).Value
Application.EnableEvents = True
End Sub

Thanks a lot for such a quick response. Just tried this out and it works well if each line is entered by the user one-by-one. If the user pastes entries (which we do expect them to do; they'll be copying employee names from older databases) into any of the columns A -> D, it appears to only count as a single "worksheet change" and thus only the first non-blank cell in row 5 is copied to row 6.

So for example, I pasted 5 employee names in column C3, which, by formula row() in column E, creates 3,4,5,6&7 in column E3->E7, but only "3: is copied into column F3. F4->F7 remain blank.

 

[JoeMo]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
Application.EnableEvents = False
For Each c In Target
    If Cells(c.Row, 5) <> "" Then Cells(c.Row, 6) = Cells(c.Row, 5).Value
Next c
Application.EnableEvents = True
End Sub

 

[JLGWhiz]

Ok, the multiple paste was not specified in the OP but JoeMo's version should fix that.
Regards, JLG

 

[JoeMo]

Ok, the multiple paste was not specified in the OP but JoeMo's version should fix that.
Regards, JLG

I generally assume that users at some point will copy/paste multiple cells to save time, and it's simple to anticipate that. For the same reason, I eschew the line: If Target.CountLarge > 1 Then Exit Sub, b/c the paste will take place and the code will not respond to it.

 

[labfm1]

Works like a charm. Totally appreciate it.

 

[JoeMo]

Works like a charm. Totally appreciate it.

Glad we could help - thanks for the reply.