Subscript out of range (error 9) - Windows(name).Activate

Question

Keep getting 'Subscript out of range' when trying to run my VBA macro. The macros aim is to activate the workbook2, and import into workbook1. This was originally working when the macro was saved as a personal macro workbook, but as I am wanting workbook1 to be shared with others I changed to a ThisWorkbook macro.

I have tried debugging by showing the workbook.Name before and after the error is displayed so have found exactly the line that it is crashing on. (Windows(WBN.Name).Activate)

The MsgBox also displays the correct filename so believe there isn't any errors with the name being wrong.

The file extension is also correct.

Have also tried using Workbooks().Activate

Dim MainWorkbook As String
Dim WBN As Object

MainWorkbook = ActiveWorkbook.Name
Personal = "PERSONAL.XLSB"

For Each WBN In Application.Workbooks()
    WBN.Save
    If WBN.Name <> MainWorkbook Then
        If WBN.Name <> Personal Then
            MsgBox WBN.Name
            Windows(WBN.Name).Activate
            Sheets("Data Entry").Copy_
            After:=Workbooks("RER.xlsm").Sheets(1)
        End If
    End If
Next

Expected result is workbook2 is imported into workbook1. Instead I'm receiving Subscript out of range.

1. Please use a different variable name than ThisWorkbook to avoid confusion with Application.ThisWorkbook 2. WBN is the Workbook you want to be working with. Normally you don't need to Activate. — BigBen
1. I have edited the question to show MainWorkbook instead of ThisWorkbook. 2. If I remove the activate line then I still get Subscript out of range, presumably for the next line of code as it cant find the data entry sheet because it is still working within the active workbook (MainWorkbook). The MsgBox correctly displays the workbook2 that I want to import so what would I use instead of .Activate in order to import? — Snowcap96

BigBen BigBen · Accepted Answer · 2019-09-24T14:23:37

There is normally no need to Activate.

Use the Workbook object you already have: WBN.

If WBN.Name <> Personal Then
     WBN.Sheets("Data Entry").Copy_
         After:=Workbooks("RER.xlsm").Sheets(1)
End If

Subscript out of range (error 9) - Windows(name).Activate

1 Answers