Copy/paste special as each entry is made

[kateb64]

I have a data table in excel. As data is entered, a VLOOKUP calculates the price of the items sold. What I would like is a way of taking the result of the VLOOKUP in (for example) cell H4 and 'paste special' that figure into cell M4. However, I need it to be dynamic and do this for each row where there is a value in M4. I don't know if this can be automated or if it is simpler to attach a macro to a button that can be clicked at the end of each data entry session.

I have managed to get this far:

Sub Button2_Click()

Sheets("Input").Range("i140:i150").Copy
Sheets("input").Range("k140:k150").PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
End Sub

but the range is fixed and ideally I need it to work row by row, or maybe relate it to the date of entry (which is entered in another cell in the row) as the LOOKUP reference table will change periodically and I need a fixed figure. Hope this makes sense!

 

[iferror]

The Worksheet' Change event can be an option

Private Sub Worksheet_Change(ByVal Target As Range)

  Dim lstRow As Long
  lstRow = Range("A" & Rows.Count).End(xlUp).Row 'COUNT USED ROWS IN COLUMN A

  Dim trgtRng As Range
  Set trgtRng = Range("H1", Range("H" & lstRow))

   If Intersect(Target, trgtRng) Is Nothing Then Exit Sub

   Target.Offset(0, 5).Value=Target.Value 'Column M is 5 to the right from Column H

  Application.EnableEvents = True
End Sub

Now, everytime you edit a cell in column H you'll have the same value on Column M

 

[kateb64]

Many thanks for your help. It has raised 2 queries, the first is that as the initial value is pulled into the Excel 2010 formatted table via a VLOOKUP the change event doesn't 'fire' unless I overtype the lookup which I don't want to do!

Secondly, I already have a worksheet_change event to enter the date of input so how would I combine the two?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
  If Not Intersect(Target, Range("A2:A100000")) Is Nothing Then
  With Target(1, 10)
  .Value = Date
  .EntireColumn.AutoFit
  End With
  End If
End Sub

 

[iferror]

ok, fair enought my misreading "As data is entered" and assuming actual key typing

I think i need additional input here

PS combining the two wouldn't have been a big deal: you use UNION to join the two ranges (A2:A100000 and M2:M100000) and if the target is not in the union, then quit. If it is in the range, then based on column(target) you can discriminate one case against the other