Hi All, I am trying to reference an excel workbook that is on a LAN (at work). The idea is to copy the contents that is on sheet1 and paste it on sheet1 of the workbook where the macro is running. It seems to work when I have the workbook open. The reference to the workbook in this case was created using the 'Record Macro' feature and does not specify the full path name. However when the workbook is not open I get a subscript out of range error. Any help and a quick response will be greatly appreciated THANKS

Hi, 1. If the workbook is not open, your macro will not know where it is - as you said yourself, it does not specify the full path name. 2. You can't copy and paste from a closed workbook. You need to include the full path to the Workbook in your macro, then get the Macro to open it, copy & paste and then close it. Regards

While it's true that you cannot copy and paste from a closed workbook, you can link to it, pull in the data and then Copy/PasteSpecial the values. Just as a basic example, this will essentially "copy" the data from A1:J10 of a closed workbook into A1:J10 of the workbook where the macro is run by creating formulae to link to the closed workbook and then eliminating the formulae to leave just the data. Sub CreateLink() 'Fill A1:J10 with Link Formulae For myRw = 1 To 10 For myCol = 1 To 10 Cells(myRw, myCol).FormulaR1C1 = _ "='C:\[Book1.xls]Sheet1'!R" & myRow & "C" & myCol Next Next 'Copy/PasteSpecial Values With Range(Cells(1, 1), Cells(10, 10)) .Copy .PasteSpecial Paste:=xlPasteValues End With End Sub Obviously, if you need to deal with formatting and other "stuff", then the code would need to be more complex.

Thanks Guys for your replies. I will explore both options to see which one is a better given the environmental parametres. Just as an aside and for my own edification, based on your example DerbyDad, how does one deal with a dynamic range since the number of records (from the source) changes from week to week. Really appreciate the help. Wayne

After a little research, I found this, tried it and it worked great: http://www.ozgrid.com/VBA/ExtractFr... It's the same concept as mine, but a lot more elegant.

Hi DerbyDad03, Thanks for that info. from OzGrid. I hadn't seen that before. Interesting, but I think I'll stick to opening workbooks, it's just as easy and a lot less code. Regards

re: but I think I'll stick to opening workbooks, it's just as easy and a lot less code. Don't take this the wrong way, but "Are you serious?" :-) It took me less than a minute to set up 2 workbooks to try the code offered at the Ozgrid site. If you add the final piece to have it run as soon as you open the destination workbook, it will be automatic. It doesn't get much easier than that.