"Run-time error '9': Subscript out of range" when referencing between workbooks

Hello,

I'm developing a more streamlined invoicing system for a friend where information from an invoicing template ("InvoiceTemplate_CompanyName.xlsm") is copied to an excel document summarizing all of their invoices ("InvoiceSummary_CompanyName.xlsx"). I have gotten this to work exactly as I want it on the computer that I've been developing the solution on, but I had an embarrassing moment when trying to transfer the files to my friends computer (i.e., the computer that will end up hosting the excel files). I thought that I would just need to change the path names to those of the new computer, but it did not work. When trying to execute the code, I kept getting a "Run-time error '9': Subscript out of range" error.

The one thing that I think the problem could be related to is the setup I've been using to develop the VBA solution. I've been using a mac running windows 7 through virtualbox. I've been writing the VBA code in Excel 2010 on the windows side. Like I said, the code worked on my mac (running windows 7), just not on my friends computer. I can't remember, but my friend might have also been running Excel 2013....could there be an issue with my code being developed in Excel 2010?

Thanks in advance for any advice on this issue!

Here is the code:

[FONT=Courier New]

Please Login or Register  to view this content.

'This function is used to see if "InvoiceSummary_CompanyName.xlsx" is already open or not.

Please Login or Register  to view this content.

Code Tags Added
Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html



(Because you are new to the forum, I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)

1) put you code into spoiler
2) Workbooks("InvoiceSummary_CompanyName") => Workbooks("InvoiceSummary_CompanyName.xlsx")

The use of code tags makes it easier for others to see your code.
To place code in code tags click to edit your post --> highlight the section of code --> click on the hashtag button or pound symbol in the WYSIWYG editor.

You should really declare and initialize variables to avoid confusion. For example...

Please Login or Register  to view this content.

By declaring and initializing your variables clearly you can always refer to exactly what you want at any time in the code easily

Thanks for the feedback cplettner. I do apologize for not adding the code tags, this is my first post to excelforum. I've just recently learned some of the basics in VBA coding in excel, and I think I get easily carried away with applying basic techniques when other techniques would serve me better. I really appreciate you adding to the code and I will try your modifications to clean it up and see if it resolves the error I was getting. Just for my understanding of the Run-time error '9', was there something specifically in my code that caused this?

In the following line you are missing the file extension

Please Login or Register  to view this content.

Should be

Please Login or Register  to view this content.