VBA help- after change, if populated, copy/paste

[KeithHes]

Hello all,

First time poster here, with a question about how to handle something.

I have a spreadsheet that I maintain, where I copy data in from a report. As I copy in this data, I'd like Excel to copy/paste from a cell for each item copied. So an example

The sheet is currently populated to Row 1816.

I now paste 200 more rows in one lump, with columns 1-8. I'd like the worksheet to copy for each of these rows from cell R2, and R3, and paste them in column 9 & 10 respectively for each line.

I'd like to share the workbook; however the information contained is financial and I'm not certain of how that would work.
Is what I am looking for possible? Can someone help with the code and explain it so that I learn as well?

 

[JLGWhiz]

Hi KeithHes, welcome to the Forum.
This is event code and should be copied to the worksheet code module. To access the code module, right click on the sheet name tab, then click View Code in the pop-up menu. The code will run when you make entries in columns A:H if there is no entry in column I of the same row(s).

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, c As Range
If Application.CountA(Range("A" & Target.Row).Resize(1, 8)) = 0 Then Exit Sub
    If Application.CountA(Range("I" & Target.Row) _
    .Resize(Target.Rows.Count, 1)) = 0 Then
        Set rng = ActiveSheet.Range("I" & Target.Row). _
        Resize(Target.Rows.Count, 1)
        Application.EnableEvents = False
        For Each c In rng
            c = ActiveSheet.Range("R2").Value
            c.Offset(0, 1) = ActiveSheet.Range("R3").Value
        Next
        Application.EnableEvents = True
    End If
End Sub

 

[KeithHes]

Hi KeithHes, welcome to the Forum.
This is event code and should be copied to the worksheet code module. To access the code module, right click on the sheet name tab, then click View Code in the pop-up menu. The code will run when you make entries in columns A:H if there is no entry in column I of the same row(s).

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, c As Range
If Application.CountA(Range("A" & Target.Row).Resize(1, 8)) = 0 Then Exit Sub
    If Application.CountA(Range("I" & Target.Row) _
    .Resize(Target.Rows.Count, 1)) = 0 Then
        Set rng = ActiveSheet.Range("I" & Target.Row). _
        Resize(Target.Rows.Count, 1)
        Application.EnableEvents = False
        For Each c In rng
            c = ActiveSheet.Range("R2").Value
            c.Offset(0, 1) = ActiveSheet.Range("R3").Value
        Next
        Application.EnableEvents = True
    End If
End Sub

Thank you so much, this worked like a charm!

 

[JLGWhiz]

Thank you so much, this worked like a charm!

You're welcom,
Regards, JLG