Copy Paste code not working correctly

[ipbr21054]

Hi,
I am using the code below but not getting a true copy / paste value etc.

The worksheet called MILEAGE at cell C32 has the formula =DOLLAR(D31*0.45) & currently shows £1.80
When i press the button the £1.80 is copied to cell reference E58 on the SUMMARY sheet but there is a mark in the cells top left corner.

The calculation then on the SUMMARY sheet is incorrect because of this mark or the way it was copied over even though paste special is used.

I mean if i delete the £1.80 from cell E58 & then manually type it in the calculation on the sheet is correct and no mark in the top left of cell.
If i then delete the £1.80 value in the cell & go back to the sheet MILEAGE & press the button the £1.80 is then put back into the cell E58,the mark is shown again & the calculation is wrong.

Private Sub CommandButton1_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("MILEAGE").Range("C32").Copy
        Workbooks("SUMMARY 2018 - 2019").Sheets("Sheet1").Range("E58").PasteSpecial xlPasteValues
        ActiveWorkbook.Save
    End If
End Sub

 

[MARK858]

That is because the Dollar function is a text function and so returns text not a number. Why can't you just format the cell?

https://support.office.com/en-us/article/dollar-function-a6cd05d9-9740-4ad3-a469-8109d18ff611

 

[ipbr21054]

Like this you mean ?

Private Sub CommandButton1_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("MILEAGE").Range("C32").Copy
        Workbooks("SUMMARY 2018 - 2019").Sheets("Sheet1").Range("E58").PasteSpecial xlPasteValues
        Workbooks("SUMMARY 2018 - 2019").Sheets("Sheet1").Range("E58").NumberFormat = "$#,##0.00"
        ActiveWorkbook.Save
    End If
End Sub

I tried but still the same

 

[ipbr21054]

Forget the last post.

=DOLLAR(D31*0.45)
is now
=SUM(D31*0.45)

 

[MARK858]

No you are already converting it to text in the Dollar formula, if you use it then it will be text not a number.

Format both the cells as accountancy (use Round or Trunc if you need less decimal places before the format).

Edit: I see you have just amended your formula so all good

Not sure why you need the Sum part though as you could just do

=D31*0.45