Pasting a value into a cell losses formula

[ipbr21054]

Hi,

I run the code below.

Private Sub CommandButton1_Click()Sheets("INCOME1").Range("C30:F30").Copy
Sheets("INCOME2").Range("C4:F4").PasteSpecial Paste:=xlPasteValues
Sheets("INCOME2").Activate
ActiveSheet.Range("A5").Select
Sheets("INCOME1").Activate
ActiveSheet.Range("H32").Select
ActiveWorkbook.Save


End Sub

The sheet that the code is pasted to has a formula in cell E4
The formula is,

=IF(C4="","",C4+D4)

This formula is lost upon paste.

 

[Scott T]

You are pasting into C4:F4 this would paste over E4 since it is in the range.

 

[mumps]

You can't have a formula and a value in a cell at the same time. When you paste the C30:F30 range into the C4:F4 range, the pasted value overwrites the formula. This macro copies only the values from columns C, D and F of "INCOME1" and pastes those values to columns C, D and F of "INCOME2" leaving the formula in E4 in place.

Private Sub CommandButton1_Click()
    With Sheets("INCOME1")
        .Range("C30:D30").Copy
        Sheets("INCOME2").Range("C4").PasteSpecial Paste:=xlPasteValues
        .Range("F30").Copy
        Sheets("INCOME2").Range("F4").PasteSpecial Paste:=xlPasteValues
    End With
    Application.CutCopyMode = False
    ActiveWorkbook.Save
End Sub

 

[ipbr21054]

Yes you are correct and it now makes sense to forget about cell E

many thanks

 

[mumps]

You are very welcome.