Subscript out of range error for subroutine

Question

I am trying to do a copy of data between different workbooks. The name for the workbooks happened to be random since users are given the option to select the file names, so I did a set of workbook in my codes. When I included a subroutine into my program, it stated subscript out of range.

Any ideas on how to ensure that the subroutine continue to understand my definition of the workbooks?

My codes are simplified in case you don't quite understand the rationale behind the first sub:

Option Explicit
Sub testwe()
Dim wb As Workbook, wb2 As Workbook, vFile As Variant
Set wb = ActiveWorkbook 

vFile = Application.GetOpenFilename("Excel-files,*.xls", _
    1, "Select One File To Open", , False)
'if the user didn't select a file, exit sub
If TypeName(vFile) = "Boolean" Then Exit Sub
Workbooks.Open vFile
Set wb2 = ActiveWorkbook 'set the opened file as wb2

Call test123
End Sub

 sub test123()
 wb.Worksheets("Sheet1").Range("A1") = wb2.Worksheets("Sheet1").Range("B1")
 End Sub

Where do you declare wb and wb2 for test123. If I run you code without Option Explicit set I get an Object Required error. Also, are you sure wb2 will have a worksheet named Sheet1? — Doug Glancy
@DougGlancy you are right, I tried the code again it showed run time error instead of the one I stated.. Kind of weird. I am sure wb2 has Sheet1. — user1204868
Assuming the error happens in test123 the subscript out of range error would mean there's no "Sheet1" - spelled exactly like that. I can't see another reason for it, at least. — Doug Glancy

Gary's Student Gary's Student · Accepted Answer · 2013-09-28T14:59:01

You need to make wb and wb2 Global:

Dim wb As Workbook, wb2 As Workbook

Sub testwe()
    Set wb = ActiveWorkbook

    vFile = Application.GetOpenFilename("Excel-files,*.xls", _
        1, "Select One File To Open", , False)
    'if the user didn't select a file, exit sub
    If TypeName(vFile) = "Boolean" Then Exit Sub
    Workbooks.Open vFile
    Set wb2 = ActiveWorkbook 'set the opened file as wb2
    Call test123
End Sub

 Sub test123()
    wb.Sheets("Sheet1").Range("A1") = wb2.Sheets("Sheet1").Range("B1")
 End Sub

EDIT#1:

This version uses Doug's suggestion:

Sub testwe()
    Dim wb As Workbook, wb2 As Workbook
    Set wb = ActiveWorkbook
    vFile = Application.GetOpenFilename("Excel-files,*.xls", _
        1, "Select One File To Open", , False)
    'if the user didn't select a file, exit sub
    If TypeName(vFile) = "Boolean" Then Exit Sub
    Workbooks.Open vFile
    Set wb2 = ActiveWorkbook 'set the opened file as wb2
    Call test123(wb, wb2)
End Sub

 Sub test123(wb As Workbook, wb2 As Workbook)
    wb.Sheets("Sheet1").Range("A1") = wb2.Sheets("Sheet1").Range("B1")
 End Sub

Subscript out of range error for subroutine

1 Answers