Copy value not the formula

[ipbr21054]

Hi,

I am using the code at present shown below.

The pasted value is £0.00 & in the address bar i see =SUM(C30:D30) which is from the cell that was copied on the original sheet.
I tried .PasteSpecial but that kicked up the Run time error 13 type mismatch,strange as when i debug it im shown something that isnt to do with the cell in question.

Private Sub CommandButton2_Click()    Dim Answer As Long
    Answer = MsgBox("Transfer Values To Summary Sheet ?", vbYesNo + vbInformation, "End Of Month Accounts")
    If Answer = vbYes Then
        Workbooks("ACCOUNTS").Sheets("INCOME1").Range("E32").Copy _
        Destination:=Workbooks("SUMMARY 2018 - 2019").Sheets("Sheet1").Range("E49").PasteSpecial
        Workbooks("ACCOUNTS").Sheets("INCOME1").Range("F32").Copy _
        Destination:=Workbooks("SUMMARY 2018 - 2019").Sheets("Sheet1").Range("E50").PasteSpecial
        ActiveWorkbook.Save
    End If
    
End Sub

 

[Rick Rothstein]

Remove the line continuation characters after each Copy method name, remove the Distination:= argument names and add xlPasteValues at the end of the PasteSpecial property names.

[table="width: 500"]
[tr]
	[td]Private Sub CommandButton2_Click()
    Dim Answer As Long
    Answer = MsgBox("Transfer Values To Summary Sheet ?", vbYesNo + vbInformation, "End Of Month Accounts")
    If Answer = vbYes Then
        Workbooks("ACCOUNTS").Sheets("INCOME1").Range("E32").Copy
        Workbooks("SUMMARY 2018 - 2019").Sheets("Sheet1").Range("E49").PasteSpecial xlPasteValues
        Workbooks("ACCOUNTS").Sheets("INCOME1").Range("F32").Copy
        Workbooks("SUMMARY 2018 - 2019").Sheets("Sheet1").Range("E50").PasteSpecial xlPasteValues
        ActiveWorkbook.Save
    End If
End Sub[/td]
[/tr]
[/table]

 

[ipbr21054]

Sorry but could you advise the code as im finding hard to decipher that.

 

[Rick Rothstein]

Sorry but could you advise the code as im finding hard to decipher that.

I am not sure what you are asking me for as I did post the revised code for you.

 

[ipbr21054]

OK sorry.
That goes to show my brain power isnt much.

I have now added it & works fine.

 

[ipbr21054]

Hi,
Can the below be simplified please ?

Private Sub CommandButton2_Click()    Dim Answer As Long
    Answer = MsgBox("Transfer Values To Summary Sheet ?", vbYesNo + vbInformation, "End Of Month Accounts")
    If Answer = vbYes Then
        Workbooks("ACCOUNTS").Sheets("EXPENSES1").Range("D30").Copy
        Workbooks("SUMMARY 2018 - 2019").Sheets("Sheet1").Range("E51").PasteSpecial xlPasteValues
        Workbooks("ACCOUNTS").Sheets("EXPENSES1").Range("F30").Copy
        Workbooks("SUMMARY 2018 - 2019").Sheets("Sheet1").Range("E52").PasteSpecial xlPasteValues
        Workbooks("ACCOUNTS").Sheets("EXPENSES1").Range("G30").Copy
        Workbooks("SUMMARY 2018 - 2019").Sheets("Sheet1").Range("E53").PasteSpecial xlPasteValues
        Workbooks("ACCOUNTS").Sheets("EXPENSES1").Range("H30").Copy
        Workbooks("SUMMARY 2018 - 2019").Sheets("Sheet1").Range("E54").PasteSpecial xlPasteValues
        Workbooks("ACCOUNTS").Sheets("EXPENSES1").Range("I30").Copy
        Workbooks("SUMMARY 2018 - 2019").Sheets("Sheet1").Range("E55").PasteSpecial xlPasteValues
        Workbooks("ACCOUNTS").Sheets("EXPENSES").Range("J30").Copy
        Workbooks("SUMMARY 2018 - 2019").Sheets("Sheet1").Range("E56").PasteSpecial xlPasteValues
        Workbooks("ACCOUNTS").Sheets("EXPENSES1").Range("K30").Copy
        Workbooks("SUMMARY 2018 - 2019").Sheets("Sheet1").Range("E57").PasteSpecial xlPasteValues
        ActiveWorkbook.Save
    End If
End Sub