Dealing with Run-time error '9': Subscript out of range when iterating over Worksheet

Question

Not an Excel/VB expert but I keep getting

Run time error: 9: Subscript out of range

The error is occurring at the For Each line. Not sure why. I'm trying to copy worksheets from one workbook to another workbook.

The Workbook strFileName is being open successfully and the workbook does contain two other worksheets but code is failing on next line. I've seen similar posts regarding similar issue but have not had any luck. Any advice would be great. (I'm using Excel 2010) Thanks

Workbooks.Open (strFileName)

For Each sheet In Workbooks(strFileName).Worksheets
    total = Workbooks(activeWKBook).Worksheets.Count
    Workbooks(strFileName).Worksheets(sheet.Name).Copy _
    after:=Workbooks(activeWKBook).Worksheets(total)
Next sheet

Answer 1

strFileName contains the full path of the workbook.
So you cannot use it in Workbooks(strFileName) since it only expects the workbook name.

This is how you should do it:

Dim wbName As String
wbName = Split(strFileName, "\")(Ubound(Split(strFileName, "\"))) ' Get the WB Name

For Each sheet In Workbooks(wbName).Worksheets
   ' Other cool stuff goes here
Next

But it is better to be explicit right away so you'll not have to worry about default path separator.
Remember that it is not always \. So I suggest you try below.

Dim myWB As Workbook
Set myWB = Workbooks.Open(strFileName)

Dim sheet As Worksheet
For Each sheet In myWB.Worksheets
    With Thisworkbook ' Explicitly refer to the workbook that contains the code
        sheet.Copy After:=.Sheets(.Sheets.Count)
    End With
Next

Remember, you need to use ThisWorkbook in place of ActiveWorkbook.
Why? Because the moment you open the other workbook, the currently opened workbook becomes the ActiveWorkbook.

So to copy all sheets from the opened workbook to the workbook that contains the code, use ThisWorkbook instead.