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

labfm1

New Member
Joined
Oct 16, 2019
Messages
3
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?
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,533
Office Version
  1. 2013
Platform
  1. Windows
You can try this. It should work with the table.

Code:
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

New Member
Joined
Oct 16, 2019
Messages
3
You can try this. It should work with the table.

Code:
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

MrExcel MVP
Joined
May 26, 2009
Messages
17,325
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Try:
Code:
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

Well-known Member
Joined
Feb 7, 2012
Messages
12,533
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,325
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,314
Messages
5,547,159
Members
410,775
Latest member
alal1030
Top