Subscript out of range error - combining worksheets

Question

I have multiple workbooks with worksheet named 'SUMMARY-F'. I need to combine these worksheets into one workbook and I am using the below code:

Sub CopySheets1()
Dim wkb As Workbook
Dim sWksName As String

sWksName = "SUMMARY-F"
For Each wkb In Workbooks
    If wkb.Name <> ThisWorkbook.Name Then
        wkb.Worksheets(sWksName).Copy _
          Before:=ThisWorkbook.Sheets(1)
    End If
Next
Set wkb = Nothing


End Sub

The code worked perfectly about 4 times however now when I run it, I get a subscript out of range error 9. Any tips on how to fix this?

Thanks, Lucinda

One of your workbooks doesn't have a worksheet named "SUMMARY-F" ? — Tim Williams, Mar 07 '17 at 06:41

score 0 · Answer 1 · edited May 23 '17 at 12:17

If you have a workbook that is open that doesn't contain a sheet called SUMMARY-F you'll get an out-of-range error because Excel can't find a sheet with the specified name. This error will also apply to hidden workbooks such as a PERSONAL.xlsm if you've used it to record macros.

You should include a check in your code to handle the case when an open workbook doesn't have a sheet called SUMMARY-F.

See this question that gives options on how to identify if a sheet exists, such as defining a function that could be called from your code first:

How to check whether certain sheets exist or not in Excel-VBA?

You'll just need to modify it to check a sheet in another workbook, something like:

Public Function CheckSheet(ByVal sWB As String, ByVal sSheetName As String) As Boolean

Dim oSheet As Excel.Worksheet
Dim bReturn As Boolean

For Each oSheet In Workbooks(sWB).Sheets

    If oSheet.Name = sSheetName Then

        bReturn = True
        Exit For

    End If

Next oSheet

CheckSheet = bReturn

End Function

Then you can add a check in your code:

Sub CopySheets1()
Dim wkb As Workbook
Dim sWksName As String

sWksName = "SUMMARY-F"
For Each wkb In Workbooks
    If wkb.Name <> ThisWorkbook.Name Then
        If CheckSheet(wkb.Name,sWksName)
            wkb.Worksheets(sWksName).Copy Before:=ThisWorkbook.Sheets(1)
        End If
    End If
Next
Set wkb = Nothing


End Sub

Subscript out of range error - combining worksheets

1 Answers1