if cell value changes, then copy only that column from which cell value changed, to another worksheet,but against common value in both the sheets

Question

I do have Excel sheet in which I have inserted IF formula,

i.e.=IF(C23='Data Nifty'!$AD$2,"CMP"," ")

use of erlier derived "CMP" as above mentioned, is done in another condition

i.e. =IF(A23="CMP",VLOOKUP($D$3,'Data Nifty'!$N:$Y,7,FALSE)," ")

now the problem is, if CMP gets change with other column then value disappers from original where erlier CMP was mentioned, I want to make sure that once CMP appears value do not change.

I have tried for vba code in which, if value gets change, copy the data to another worksheet, but it did't work.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.range = "A:A" Then
        Call copy_paste_as_value
    End If
End Sub


Sub copy_paste_as_value()
    Range("A4").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Range("B4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("C4").Select
    Application.CutCopyMode = False
End Sub

Current issue:-when cmp change from one price to another,values mentioned in front of erlier CMP becomes 0,and new value appears against new cmp

(my objective:-if once "CMP" appears, values mentioned in front of that(in above pic. i.e. 26,55,110.35,117,29.35,9) should be remained as it is & new values should also appears in front of new cmp, so in short, all values should remain as it is, once CMP is derived)

Hope fully, I have explained my query to the level that can be understood.

Any help will be appriciacted heartly.

Can you also paste the code in your **copy_paste_as_value** module — Mikku, May 23 '19 at 04:01
i have added that code now, in my q. for more clarity. thank you. — Arshit patel, May 23 '19 at 04:38
You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — Pᴇʜ, May 23 '19 at 06:02
thank you for giving your valuable time, i would like to have more simple & effective solution, if any is there. — Arshit patel, May 23 '19 at 06:15

score 1 · Accepted Answer · answered Sep 24 '19 at 14:39

This should fix your problems, try this code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A:A")) Is Nothing Then copy_paste_as_value
End Sub

Sub copy_paste_as_value()
    With ActiveSheet
        .Range(.Cells(4,1),Cells(Rows.Count, 1).End(xlUp)).Copy
        .Range("B4").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End With
    Application.CutCopyMode = False
End Sub

if cell value changes, then copy only that column from which cell value changed, to another worksheet,but against common value in both the sheets

1 Answers1