Paste Values with VBA

[Reddog94]

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?

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

 

[Andrew Poulsom]

Try changing:

rCopy.Copy rPaste

to:

rCopy.Copy
rPaste.PasteSpecial Paste:=xlPasteValues

 

[Reddog94]

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:

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]

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.

 

[Reddog94]

Interesting and good to know. Thanks to both of you!

 

[Andrew Poulsom]

The Copy method has an optional Destination argument, which you were using in your original code.