"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:

Sub Invoice_Data_CopyPaste()

    Application.ScreenUpdating = False
    
    wsInvoice.Range("H4").Copy 'this worksheet is found in "InvoiceTemplate_CompanyName.xlsm". Users will have this open when code is run 
    
    Dim open_wkb_check As Boolean
    open_wkb_check = IsWorkBookOpen("C:\Users\PathName...\InvoiceSummary_CompanyName.xlsx") 'this calls the function shown below this sub

    If open_wkb_check = False Then
        Workbooks.Open ("C:\Users\PathName....\InvoiceSummary_CompanyName.xlsx") 'note path names are not real
    End If
    
    'The code gets hung up at this line (below)
    Workbooks("InvoiceSummary_CompanyName").Worksheets("Invoice Summary List").Range("A3").End(xlDown).Offset(1, 0) _
         .PasteSpecial Paste:=xlPasteValues

    Workbooks("InvoiceTemplate_CompanyName").Worksheets("Invoice").Range("H2").Copy
    Workbooks("InvoiceSummary_CompanyName").Worksheets("Invoice Summary List").Range("B3").End(xlDown).Offset(1, 0) _
         .PasteSpecial Paste:=xlPasteValues

'....code continues copying and pasting other values between the two workbooks

End sub

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

Function IsWorkBookOpen(FileName As String)
    Dim ff As Long, ErrNo As Long

    On Error Resume Next
    ff = FreeFile()
    Open FileName For Input Lock Read As #ff
    Close ff
    ErrNo = Err
    On Error GoTo 0

    Select Case ErrNo
    Case 0:    IsWorkBookOpen = False
    Case 70:   IsWorkBookOpen = True
    Case Else: Error ErrNo
    End Select
End Function

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

Posting code between [CODE] [/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...

Option Explicit 'This will keep you from a lot of errors by forcing you to declare the variables used in the code
Sub Invoice_Data_CopyPaste()

'Declare variables
Dim wbInvTemp As Workbook, wbInvSum As Workbook
Dim wsInvTemp As Worksheet, wsInvSum As Worksheet
Dim InvSumS As String
Dim open_wkb_check As Boolean

Application.ScreenUpdating = False
Set wbInvTemp = ThisWorkbook
Set wsInvTemp = wbInvTemp.Worksheets("Invoice")

InvSumS = "C:\Users\PathName...\InvoiceSummary_CompanyName.xlsx"

open_wkb_check = IsWorkBookOpen(InvSumS) 'this calls the function shown below this sub

If open_wkb_check = False Then
    Set wbInvSum = Workbooks.Open(InvSumS)  'note path names are not real
Else  'What if the workbook is open
    Set wbInvSum = Workbooks("InvoiceSummary_CompanyName.xlsx")
End If

Set wsInvSum = wbInvSum.Worksheets("Invoice Summary List")

'The code gets hung up at this line (below)

 'rather than copy and paste why not equate the values this saves resources and is faster
wsInvTemp.Range("H4").Value = wsInvSum.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value
wsInvTemp.Range("H2").Value = wsInvSum.Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Value
'etc....

''''''''''Workbooks("InvoiceTemplate_CompanyName").Worksheets("Invoice").Range("H4").Copy 'this worksheet is found in "InvoiceTemplate_CompanyName.xlsm". Users will have this open when code is run
'''''''''Workbooks("InvoiceSummary_CompanyName").Worksheets("Invoice Summary List").Range("A3").End(xlDown).Offset(1, 0) _
'''''''''.PasteSpecial Paste:=xlPasteValues

'''''''''Workbooks("InvoiceTemplate_CompanyName").Worksheets("Invoice").Range("H2").Copy
'''''''''Workbooks("InvoiceSummary_CompanyName").Worksheets("Invoice Summary List").Range("B3").End(xlDown).Offset(1, 0) _
'''''''''.PasteSpecial Paste:=xlPasteValues

'....code continues copying and pasting other values between the two workbooks

End Sub


'This function is used to see if "InvoiceSummary_CompanyName.xlsx" is already open or not.
Function IsWorkBookOpen(FileName As String)
Dim ff As Long, ErrNo As Long

On Error Resume Next
ff = FreeFile()
Open FileName For Input Lock Read As #ff
Close ff
ErrNo = Err
On Error GoTo 0

Select Case ErrNo
    Case 0: IsWorkBookOpen = False
    Case 70: IsWorkBookOpen = True
    Case Else: Error ErrNo
End Select
End Function

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

Workbooks("InvoiceSummary_CompanyName").Worksheets("Invoice Summary List").Range("A3").End(xlDown).Offset(1, 0) _
         .PasteSpecial Paste:=xlPasteValues

Should be

Workbooks("InvoiceSummary_CompanyName.xlsx").Worksheets("Invoice Summary List").Range("A3").End(xlDown).Offset(1, 0) _
         .PasteSpecial Paste:=xlPasteValues