Find last modified row, copy a different cell to another column in that modified row

Question

This one's a little complex but I feel there might be a simple way to do it.

If a cell in a column is changed, I am looking to find the row containing that modified cell and copy a cell from another sheet to a different column in that row.

Currently my code will copy the cell from another sheet on a change in the column but paste it into a cell upon mouse click. I am looking for it to automatically paste into a named column (H).

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 6 And (Target.Row >= 1 And Target.Row <= 10000) Then

Sheets("Sheet2").Range("B2:B2").Copy

End If

Dim lastRow As Long
With ActiveSheet
Sheets("Sheet1").Activate
lastRow = .Cells(.Rows.Count, "F").End(xlUp).Row
Selection.PasteSpecial xlPasteValues

End With  

End Sub

why not just store the value and then assign it to the cell you want? `Dim str As String str = Sheets("Sheet2").Range("B2").Value ...... = str` — sam092, Oct 03 '13 at 07:52
it's unclear what you are trying to do. Can you describe in steps how you manually would do it? example. select `A1`, copy `cell B2` from sheet(2) etc — , Oct 03 '13 at 08:00
Thanks! If any cell in `column F` changes in Sheet1 then I would copy the values of `cell B2` from Sheet2 into `column H` of Sheet1 on the same row that was originally changed in Sheet1. — Byate, Oct 03 '13 at 08:09

score 0 · Accepted Answer · answered Oct 03 '13 at 08:11

You want to do something like this?

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim val As String
    val = Sheets("Sheet2").Range("B2").Value
    If Target.Column = 6 And (Target.Row >= 1 And Target.Row <= 10000) Then
        'Whatever column
        Sheets("Sheet1").Range("H" & Target.Row).Value = val
    End If
End Sub

If so happen you try to drag a cell in column F to a number of rows, this piece of code won't work for all the affected rows. Any other behaviors you may expect, perhaps?

This worked really well for what I need to do, thanks very much! — Byate, Oct 03 '13 at 08:36

score 0 · Answer 2 · edited May 23 '17 at 12:03

What you need is the target.Row

Also since you are using Worksheet_Change, I would recommend seeing THIS link.

TRIED AND TESTED

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Whoa

    '~~> If there was a multiple change then exit sub
    If Target.Cells.CountLarge > 1 Then Exit Sub

    Application.EnableEvents = False

    '~~> This is the sheet from where the data needs to be copied
    Dim sourceWs As Worksheet
    Set sourceWs = ThisWorkbook.Sheets("Sheet2")

    '~~> Check if the change happened in Col 6
    If Not Intersect(Target, Columns(6)) Is Nothing Then
        '~~> Get the value in Col H
        Range("H" & Target.Row).Value = sourceWs.Range("B2").Value
    End If

Letscontinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume Letscontinue
End Sub

Find last modified row, copy a different cell to another column in that modified row

2 Answers2