Subscript out of range - Can't find Open Workbooks on Another Computer

Question

I am a newer user of Excel VBA. I have been checking through all the other questions on here to try to solve my problem. I created a Master workbook, which copies info from 3 template spreadsheets when I run the macro. On my computer, it works correctly. However, when we tried to run it on a coworker's computer with the exact same 4 workbooks open, we ran into the "Subscript out of range" error.

I used the below code from this post (Out of range error for Windows().Activate) to check the names of all the open workbooks. Only 1 of the 4 (Master.xlsm) open workbooks displayed when I ran the code on my coworker's computer. On my computer, it displays 4 out of 4 open workbooks when I run that code

dim oBook as workbook

for each obook in workbooks
    debug.print ">" & obook.name & "<"
next

Below are the first lines in my code. It breaks on the first line because it can't find the template workbook even though we have it open. When we run the above code on my coworker's computer during debugging, the Template1.xlsx workbook is not listed. I am not sure what is going wrong, and would really appreciate any help on this.

Sub Copy_To_Master()

Workbooks("Template1.xlsx").Worksheets("1").Range("A65:E104").Copy
Workbooks("Master.xlsm").Worksheets("1").Range("A65:E104").PasteSpecial Paste:=xlPasteValues

End Sub

Is the template workbook open in protected view on your coworker's computer? — BigBen
I don't think so, we saved it to the desktop and opened all the workbooks from there. — Wal-Mart Security
It still could be opened in protected view. That's the only reason I can think of that it would not show up in the Workbooks collection. — BigBen
OK, I will double-check that and let you know. I figured it was something like that at first, and looked for the protected view ribbon, but did not see it. Maybe it is still enabled. — Wal-Mart Security
I tested it on another coworker's computer, the files are not in protected view. This time it found 3 of the 4 workbooks open. — Wal-Mart Security

HackSlash HackSlash · Accepted Answer · 2019-12-10T23:21:29

I'm guessing that your workbooks are opened in different instances. You can't control what your users are going to do so I would recommend opening the files using VBA so that you get a handle on them as they are opened.

Dim templateBook as Workbook
Set templateBook = Workbooks.Open("Template1.xlsx")

This way you can use the templateBook object to do all the things you need to do. I would then further recommend that you keep these workbooks on a server share so their location is reliable. Then any employee can run these things, they all run off the server and you never have to worry about "works on my machine" again.

Finally, don't use copy and paste. Once you have workbook handles you can set values directly without copy and paste. It looks like this:

masterBook.Worksheets("1").Range("A65:E104").Value = templateBook.Worksheets("1").Range("A65:E104").Value

Subscript out of range - Can't find Open Workbooks on Another Computer

1 Answers