Excel VBA to Paste Values of a Cell Only

jrjobe

New Member
Joined
Feb 3, 2012
Messages
29
Office Version
  1. 365
  2. 2003 or older
Platform
  1. Windows
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!

VBA Code:
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
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Candyman8019

Active Member
Joined
Dec 2, 2020
Messages
429
Office Version
  1. 365
Platform
  1. Windows
Give this a try...

Selection.PasteSpecial Paste:=xlPastValues
 

jrjobe

New Member
Joined
Feb 3, 2012
Messages
29
Office Version
  1. 365
  2. 2003 or older
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
63,243
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Give this a try...

Selection.PasteSpecial Paste:=xlPastValues
There is a typo there. It should be "xlPasteValues".
 

jrjobe

New Member
Joined
Feb 3, 2012
Messages
29
Office Version
  1. 365
  2. 2003 or older
Platform
  1. Windows
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:

VBA Code:
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValues
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,492
Messages
5,832,010
Members
430,103
Latest member
BIGGAZ

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top