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

KeithHes

New Member
Joined
Sep 20, 2013
Messages
2
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?
 
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).
Code:
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
 
Upvote 0
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).
Code:
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!
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top