Code works in pieces but not as a whole

Reddog94

Board Regular
Joined
Dec 20, 2011
Messages
52
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)?

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

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
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.

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

Reddog94

Board Regular
Joined
Dec 20, 2011
Messages
52
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:


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

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
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...
Code:
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.
 
Last edited:

Forum statistics

Threads
1,136,331
Messages
5,675,156
Members
419,552
Latest member
jsanjur

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