Between Sheets

[fvcyo]

I have information in a cell of sheet1 and desire to place it in another cell in sheet2 I can do it by a reference but if I erase the value of the cell of sheet1 i like to keep the value in the cell of sheet2

i want to do this automatic if i delete a cell of sheet1 don't erase a cell in sheet2. i can do with copy-paste special but i try a difference way. Anyone Help me..

 

[ktab]

If i got it well, you have some =sum functions, that when you change numbers you want the total to be copied at sheet2?
e.g. =sum function is at "a6". if you change any number from "a1" to "a5", you want "a6" to be copied at sheet2; is that right?
If so, your sums are in row (horizontial) or in column (vertical)?

 

[fvcyo]

If i got it well, you have some =sum functions, that when you change numbers you want the total to be copied at sheet2?
Yes
e.g. =sum function is at "a6". if you change any number from "a1" to "a5", you want "a6" to be copied at sheet2; is that right?
Yes
If so, your sums are in row (horizontial) or in column (vertical)?
Colum

If i got it well, you have some =sum functions, that when you change numbers you want the total to be copied at sheet2?
e.g. =sum function is at "a6". if you change any number from "a1" to "a5", you want "a6" to be copied at sheet2; is that right?
If so, your sums are in row (horizontial) or in column (vertical)?

 

[ktab]

I hope that this is what you are looking for (it assumes that all your sum functions are placed in columns and at the bottom):

Private Sub Worksheet_Change(ByVal Target As Range)
Dim CheckRow As Long, NextRow As Long, LastRow As Long, ws As Worksheet
CheckRow = Target.Row
NextRow = CheckRow + 1
LastRow = Cells(65536, Target.Column).End(xlUp).Row
Set ws = Sheet2
With Target
    If Cells(NextRow, .Column).Value = "" Then Exit Sub
    If Not ws.Range("iv1").Value = "" Then
        If MsgBox("Copied Data Row is full." & Chr(10) & "Do you want to clear?", _
        vbCritical + vbYesNo) = vbYes Then ws.Rows(1).ClearContents
    End If
    Do
        If Cells(CheckRow, .Column).HasFormula Then
            If ws.Range("a1").Value = "" Then
                ws.Range("a1").Value = Cells(CheckRow, Target.Column).Value
            Else
                ws.Range("iv1").End(xlToLeft).Offset(, 1).Value = Cells(CheckRow, Target.Column).Value
            End If
            Exit Do
        Else
            CheckRow = CheckRow + 1
        End If
    Loop While CheckRow <= LastRow
End With
End Sub