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

[adpatel]

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>

 

[Fluff]

In what way isn't it working?

 

[adpatel]

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....

 

[Fluff]

In that case try

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

 

[adpatel]

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

 

[adpatel]

i have added above code, & when cell A10 changed, value of D10:D15 Changed, instead of it value of D10:I10 Should be changed

 

[Fluff]

How about

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

 

[adpatel]

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.

 

[Fluff]

Glad to help & thanks for the feedback

 

[adpatel]

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.