When a cell value changes, copy the column from where the cell value changed to another sheet at the same range

adpatel

New Member
Joined
May 30, 2019
Messages
42
Office Version
  1. 2016
Platform
  1. Windows
For example, if in the range of A:A cell A8 change then copy D4:I4 and paste it as value in another sheet named as "ADP" at the same place i.e. D4:I4
For that I have tried the following macro which requires some modifications, please suggest the same. any help greatly appriciated. thank you.


<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">Private Sub Worksheet_Change(ByVal Target As Range)

Dim r As Range

If Target.Column = 1 And Target.Row > 4 Then
Set r = Target.Offset(-4, 3).Resize(5)
Worksheets
("ADP").Range(r.Address).Value = r.Value
End If

End Sub

</code>
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
In what way isn't it working?
 
Upvote 0
if cell A4 changed then D4:I4 should be copied & paste as a value in sheet "adp" at same place i.e. D4:I4 & if cell A5 change then range D5:I5...like wise....
 
Upvote 0
In that case try
Code:
Private Sub Worksheet_Change(ByVal Target As Range)


Dim r As Range

If Target.Column = 1 And Target.Row >= 4 Then
Set r = Target.Offset(, 3).Resize(6)
Worksheets("ADP").Range(r.Address).Value = r.Value
End If

End Sub
 
Upvote 0
In above given code, whole column gets copied, and i have changed cell A10 but values reflected in column D4:V4 whereas if change in cell A10 then value should be change in D10:I10 only
 
Upvote 0
i have added above code, & when cell A10 changed, value of D10:D15 Changed, instead of it value of D10:I10 Should be changed
 
Upvote 0
How about
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range

If Target.Column = 1 And Target.Row >= 4 Then
Set r = Target.Offset(, 3).Resize(, 6)
Worksheets("ADP").Range(r.Address).Value = r.Value
End If

End Sub
 
Upvote 0
I was searching for the same solution as provided by you above from very long time, thank you so much, i greatly & by heart appriciate your knowledge and thank you for putting efforts & investing your time. thank you.
 
Upvote 0
suppose in sheet if there is any change at any cell , then macro should run, then what should be the macro? kindly can you help on this? present situation in above code is, if there is change in range A:A then only macro runs. thank you.
 
Upvote 0

Forum statistics

Threads
1,223,243
Messages
6,170,964
Members
452,371
Latest member
Frana

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