Code works in pieces but not as a whole

[Reddog94]

I have three lines of code that do the following:

(1) Opens another file based on a filepath in a cell in the current workbook.
(2) Copies the values of a named range in the newly opened book.
(3) Pastes values in tab "Detail" of the original workbook.

When I put the three lines in the immediate window, I get an error message "PasteSpecial method of Range class failed", run-time 1004. However, if in the immediate window, I put my mouse at the end of the second line and execute, then put my mouse at the end of the third line and execute it work fine. The error comes if I only try to execute at the end of the third line.

I have tried activating the original workbook where the data is to be pasted (inserting the activate code b/t lines 2 and 3 below), but that didn't change anything. Can anyone tell me what else I need to do so the code can run will work (run all at once)?

Application.Workbooks.Open Filename:=Range("TEST_FILEPATH"), Password:=("yobaby3")

 
Worksheets("DETAIL").Range("RANGE_DETAIL").Copy
 
Workbooks("Rebuild 7 detail").Worksheets("Detail").Range("a1").PasteSpecial Paste:=xlPasteValues

 

[Jerry Sullivan]

Hi Reddog94,

In the Immediate Window, you'll need to evaluate Line 3 after evaluating Line 2 or else the clipboard might be empty which would give you the error you describe.

Is your Workbook reference missing a file extension?
Your code works for me when I add an extension, but not without one.

Workbooks("Rebuild 7 detail[COLOR="Blue"].xlsx[/COLOR]").Worksheets("Detail").Range("a1").PasteSpecial Paste:=xlPasteValues

 

[Reddog94]

I moved the sub from ThisWorkbook to a module, as I read this morning that you cannot activate another workbook from ThisWorkbook. Not sure if that is what made it work or not, since I ended up with a totally different code:

Application.Workbooks.Open Filename:=Range("TEST_FILEPATH"), Password:=("yobaby3")
Dim rCopy As Range
Dim rPaste As Range
    Set rCopy = Range("RANGE_DETAIL")
    
Application.Workbooks("Rebuild7").Activate
    Set rPaste = Range("Detail_Paste_Range")
    
    rPaste.Clear
      
    rCopy.Copy rPaste

Thanks for the assistance - it definitely wouldn't have worked without the file extension (rookie mistake).

 

[Jerry Sullivan]

Glad you got it to work.

FYI, you don't need to activate Workbook Rebuild7 to accomplish your objective.

This code should give the same result with less steps...

Sub Copy_MyDetail()
    Workbooks.Open Filename:=Range("TEST_FILEPATH"), Password:=("yobaby3")
    Worksheets("DETAIL").Range("RANGE_DETAIL").Copy
    Workbooks("Rebuild 7.xlsx").Worksheets("Detail") _
        .Range("Detail_Paste_Range").PasteSpecial Paste:=xlPasteValues
End Sub

Range("Detail_Paste_Range") only needs to be Cleared if you have prior values that might extend beyond the newly pasted data.

It is a better practice though to put this code in a Standard Module.
The ThisWorkBook Module is typically used for Event Code that affects the entire Workbook.