User Selected Range Across Workbooks

[Mountainman141]

I am trying to write a sub that will allow the user to select a range and a collection of files. That range will be pulled from each of the files selected and then pasted to a new workbook (same sheet). I will also be doing the same thing but instead to one sheet give each file its own sheet. I have a problem with the range. When I grab the range from the user, I can immediately select that range in the open worksheet. However, when I move over to the next workbook, it seems as if its out of scope or something. The error says object required. I have stripped the code down as much as I can to the part that is causing the problem.

Please Login or Register  to view this content.

Cross Posted:
http://www.mrexcel.com/forum/showthread.php?t=336209
http://www.xtremevbtalk.com/showthre...17#post1301417
http://www.ozgrid.com/forum/showthre...454008#Similar

[Leith Ross]

Hello Moutainmn141,

When you assign an object to an object variable, the object variable's Parent property is set to the Parent property of the object. A Range's parent is the Worksheet, and the worksheet's parent is the Workbook.

You close the workbook and try to reference the range variable that points back to that workbook. Since the workbook no longer exists, you get the error "object required".

You can reference the same range on a different sheet in a different workbook by saving the range's address in a string. Here is an example...

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[Mountainman141]

Thanks a million. I tried that at some point, but must have messed up the syntax or something. Oh-well, works great now! Really appreciate it!