Excel 2007 : Macro copy sheet into diff workbook

Hello all, I am trying to create a macro to copy a sheet from a closed excel file into the currently open workbook that I will be running the macro from. I have tried several bits of code that seemed to work for others but not me, figured it was because I was using 2007. the last bit of code I tried was:

Please Login or Register  to view this content.

I got the runtime error 9, subscript out of range error on this bit...I thought this should be a simple macro but am having trouble with it. Any suggestions?? Thanks in advance for your help guys!!

hi

AFAIK, the use of "workbooks(..." requires the file to be part of the collection of currently open workbooks. I would suggest opening the file first (you can make it hidden as well, or re-close it immediately).

You may be interested in Dave McRitchie's code:
http://www.ozgrid.com/VBA/ExtractFromClosedWorkbook.htm

hth
Rob

As Rob says, Workbooks() always refers to an open workbook, and never includes a path.