Paste Values with VBA

Reddog94

Board Regular
Joined
Dec 20, 2011
Messages
52
The code below works fine to open another workbook, copy data, then paste into the original file. However, I need to paste only values. I have tried a couple different ways of doing it after the fact (ie after pasting data formulas and all), but nothing has worked and I would prefer to just paste values initially.

Can anyone tell me how to alter the code below to only paste values?

Code:
On Error GoTo ErrorHandler
Application.Workbooks.Open Filename:=Range("FILEPATH"), Password:=("yobaby3")
Dim rCopy As Range
Dim rPaste As Range
    Set rCopy = Range("RANGE_DETAIL")
    
Application.Workbooks("Rebuild 11").Activate
    Set rPaste = Range("Detail_Paste_Range")
    
    rPaste.Clear
      
    rCopy.Copy rPaste
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Try changing:

Code:
rCopy.Copy rPaste
to:

Code:
rCopy.Copy
rPaste.PasteSpecial Paste:=xlPasteValues
 

Reddog94

Board Regular
Joined
Dec 20, 2011
Messages
52
It works - thank you thank you!

Would you be willing to explain something to me? Before I posted this issue on the board, I tried a similar code except all on one line:

Code:
rCopy.Copy rPaste.PasteSpecial Paste:=xlPasteValues
How come I got an error when it was on one line but putting it on two like you suggested worked? I'm new to vb so don't know some of the nuances yet - why does having it on another line matter?
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
How come I got an error when it was on one line but putting it on two like you suggested worked? I'm new to vb so don't know some of the nuances yet - why does having it on another line matter?
That's just one of those quirks. You can do a direct copy/paste on one line, but to apply any paste special formats you need to extend it to a second line.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
The Copy method has an optional Destination argument, which you were using in your original code.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,733
Messages
5,470,435
Members
406,699
Latest member
perfectioncts

This Week's Hot Topics

Top