Excel VBA to Paste Values of a Cell Only

[jrjobe]

Hi all!

I have macro that copies data from database.xlsm to estimate.xlsx. In its current form - it works flawlessly as long as the cells in the database workbook doesn't have formulas. I am wanting to include formulas to the cells in the database workbook, however, when they are pasted into the estimate workbook, it pastes the formula. Is there a way to paste the value of all cells only? I have tried updating the paste code below with Activesheet.PasteSpecial Paste:=xlPastValues and simply Activesheet.PasteSpecial xlPastValues but regardless, I get an error. Can someone please point me in the right direction to update the following code so that it pastes the value of the cells and not the formulas, if it's possible?

Thank you!

Sub COPY()

    ActiveCell.Offset(0, 0).Range("A1:F1").Select
    Application.CutCopyMode = False
    Selection.COPY
    Application.Run "BACK" 'this simply confirms that the name of the estimate workbook is updated in the database.xlsm workbook (it can change - so if someone saves the blank estimate.xlsx workbook as finalestimate.xlsx - they must update the name in the database to finalestimate.xlsx or it won't work as it is used to paste the data "back" into the estimate.xlsx workbook (whatever file name the user gave it)
    ActiveCell.Offset(0, 0).Range("A1:F1").Select
    ActiveSheet.Paste
    ActiveCell.Offset(1, 0).Select
    Windows("DATABASE.XLSM").Activate
    Application.CutCopyMode = False

End Sub

 

[Candyman8019]

following...

 

[Candyman8019]

Give this a try...

Selection.PasteSpecial Paste:=xlPastValues

 

[jrjobe]

Didn't work - Runtime Error '1004': PasteSpecial method of Range class failed. This was the same error I received with my other attempts mentioned.

 

[Joe4]

Give this a try...

Selection.PasteSpecial Paste:=xlPastValues

There is a typo there. It should be "xlPasteValues".

 

[Candyman8019]

There is a typo there. It should be "xlPasteValues".

Thanks for catching that Joe4!

 

[jrjobe]

I don't even know how I missed that... Working too fast I guess. That does indeed work! Which one should I mark as the solution? For the record this is what I used:

ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValues