.Copy Destination copying Values only (not formulea) [SOLVED]

[Dancey_]

Hey guys,

Hoping you can help me, I have the following code that copies data from an input sheet to a 'sales database' I have made. The following code works perfectly, but it copies the formula in some of the cells, where I just want the value. For your reference the formula's are =TODAY() and =NOW() to create a time (and date) stamp for the sale.

Here is what I have, but I would like it just copy values please:

Sub Save()
Workbooks.Open "C:\Documents and Settings\tom.dance\Desktop\Sales Database.xlsm"
ThisWorkbook.Sheets("Sheet1").Range("A2:M2").Copy Destination:=Workbooks("Sales Database.xlsm").Sheets("Sales").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
Workbooks("Sales Database.xlsm").Save
Workbooks("Sales Database.xlsm").Close
ThisWorkbook.Sheets("Sheet1").Range("C2:M2").ClearContents
End Sub

Once again thank you very much for your help!

Cheers

 

[VoG]

Re: .Copy Destination copying Values only (not formulea)

Try

Sub Save()
Workbooks.Open "C:\Documents and Settings\tom.dance\Desktop\Sales Database.xlsm"
ThisWorkbook.Sheets("Sheet1").Range("A2:M2").Copy
Workbooks("Sales Database.xlsm").Sheets("Sales").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Workbooks("Sales Database.xlsm").Save
Workbooks("Sales Database.xlsm").Close
ThisWorkbook.Sheets("Sheet1").Range("C2:M2").ClearContents
End Sub

 

[Dancey_]

Re: .Copy Destination copying Values only (not formulea)

Try

Sub Save()
Workbooks.Open "C:\Documents and Settings\tom.dance\Desktop\Sales Database.xlsm"
ThisWorkbook.Sheets("Sheet1").Range("A2:M2").Copy
Workbooks("Sales Database.xlsm").Sheets("Sales").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Workbooks("Sales Database.xlsm").Save
Workbooks("Sales Database.xlsm").Close
ThisWorkbook.Sheets("Sheet1").Range("C2:M2").ClearContents
End Sub

Awesome! Thank you very much, worked perfectly.

Cheers