Error '1004' & '13' Copying Range from Another Open Workbook.

bs0d

Well-known Member
Joined
Dec 29, 2006
Messages
622
I'm building a macro within one workbook, to open another WB and copy a range of data, then go back to the original WB and paste the results.

With the code below, I get a '1004' error (application or object defined error):

Code:
WB.Sheets(NAME).Range(Cells(copyFrom, START_COL), Cells(copyTo, END_COL)).Select
Selection.Copy

After researching '1004' errors, I tried the code below, but now get a '13' error (type mismatch):

Code:
Dim copy_rng as Range
Set copy_rng = WB.Sheets(NAME).Range("A" & copyFrom).Resize(copyTo, END_COL)

Other information you may need:

WB is defined as Excel.Workbook, the 2nd workbook I'm trying to copy from.
CopyFrom, copyTo and END_COL contain integers.
NAME is a string variable or the tab name.

Any thoughts?
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
You need to qualify both the range and cells properties:

Rich (BB code):
WB.Sheets(NAME).Range(WB.Sheets(NAME).Cells(copyFrom, START_COL), WB.Sheets(NAME).Cells(copyTo, END_COL)).Copy

There is no need to Select.
 
Upvote 0
What about pasting?
This throws error 438:

Code:
MasterWB.Sheets(TAB_NAME).Cells(FinalRow + 1, 3).Paste

BTW - qualifying both range and cells properties worked for copying.
 
Upvote 0
You can use the Copy method's Destination argument to paste:

Code:
WB.Sheets(NAME).Range(WB.Sheets(NAME).Cells(copyFrom, START_COL), WB.Sheets(NAME).Cells(copyTo, END_COL)).Copy MasterWB.Sheets(TAB_NAME).Cells(FinalRow + 1, 3)

The Range object doesn't have a Paste method.
 
Upvote 0
Interesting! That's pretty handy. Didn't know you could do that, thanks again

Curious if I could pick your brain for another error. I have this copy and paste as a custom function, which is within a for next loop, and it passes through the first time without an error. But on the 2nd pass, I get an error '9' when setting variables:

Code:
    START_DATE = MasterWB.Sheets("MASTER_LIST").Cells(2, 8).Value
      END_DATE = MasterWB.Sheets("MASTER_LIST").Cells(3, 8).Value

Is this because they were already set via first pass? I'm not sure why it would work the 1st time, but then have issues for the second iteration?
 
Last edited:
Upvote 0
What error do you get? You can assign a value to a variable multiple times (that's why it's called a variable).
 
Upvote 0
It was a type mismatch error. I got it to go away by closing the opened workbook after each loop cycle.
Code:
WB.Close False

The problem is, I would be opening and closing the workbook over and over. Is this ok?

From your experience, do you have a recommended function or approach to perhaps detect if the workbook is already open? The way it is now (which could be the root of the problem) is Workbooks.Open is being executed each time.
 
Upvote 0
MasterWB is the workbook the code is executing in, while WB is the workbook I am opening to get data from.

I defined both of them because I was having issues with variable assignments pointing to the right tabs and workbooks. This approach, combined with qualifying the properties appropriately seemed to resolve that issue.
 
Upvote 0

Forum statistics

Threads
1,213,522
Messages
6,114,112
Members
448,549
Latest member
brianhfield

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
Back
Top