Data range copy and paste if any change in the row

[harinsh]

Hi Team,

I need some help, I have 7 columns data in sheet1 and this is the reference of original data from Sheet2 (through the formula) both data sets are same.

I am not sure if this can be done via worksheet change request. If I change the cell value in any rows then that entire row till data set should get the copy and paste to corresponding data in sheet2.

Is this something can be achieved? Thank you

 

[harinsh]

Usually, it won't be like that...in sample data, I copy pasted the data twice...that's why it is showing like that...

 

[mumps]

OK. I have to go out now so I won't be able to work on something until tomorrow. I will get back to you as soon as I can.
When you say

In case any blank..it should go to last row of data and add as new line....

do you mean the first available row in column A of Sheet2?

 

[harinsh]

I mean to say if anything blank in Sheet1, if I enter something then that particular data should copy as a new row in Sheet2.

Thank you very much !!

 

[mumps]

After having a close look at your data, I can't figure out any way to do what you want. The problem is that once you change a value in Sheet1, then the data in that row no longer matches the corresponding data in Sheet2. The only way I can think of to do what you want is to have a single cell unique identifier for each row in Sheet1 and Sheet2.

 

[harinsh]

Ok got that...can we include unique serial number like 1,2,3,4....n for each line ..will that going to help? I can include these..it does not affect anywhere in my report...

 

[mumps]

That would work. You would need to add a column in Sheet1 and another column in Sheet2 making sure that the ID numbers match. If you are able to do that, please upload an updated version of your file.

 

[harinsh]

Thank you mumps....here is the updated link => https://www.dropbox.com/s/h87qr9friwa7nad/Sample_Update.xlsx?dl=0

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your Sheet1 and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("C4:H20,C25:H41")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim LastRow As Long, SL As Range
    Dim ws2 As Worksheet
    Set ws2 = Sheets("Sheet2")
    Set SL = ws2.Range("A:A").Find(Cells(Target.Row, 2).Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not SL Is Nothing Then
        Cells(Target.Row, 2).Resize(1, 7).Copy
        ws2.Cells(SL.Row, 9).PasteSpecial xlPasteValues
        Application.CutCopyMode = False
    End If
    Application.ScreenUpdating = True
End Sub

 

[harinsh]

Your Super Bro!!!! It's working like a charm ...I will check further if any tweak requires, I may contact you...Thanks your efforts!!

 

[mumps]

You are very welcome.