Run time error 9 Subscript out of range when activating a workbook

Question

When I try to activate "Sheet1" of Workbook1 (Work.xlsm) as the image shows, I get a run time error. (There are no spelling mistakes).

It runs with no problems from another workbook (Book1.xlsx) for populating sheet (LS) data on to the UserForm Combo box and Text Boxes.

When I click on "ADD" button the userform data is populating on workbook (Book1.xlsx) sheet (LS) whereas I want it to populate on "Sheet1" of Workbook1 (Work.xlsm).

I am trying to call my main "Sheet1" sheet of Workbook1 (Work.xlsm) but get the error in this line.

From range A8 it should start populating (desired ouput in workbook1)

My code for combobox change and commandbutton(ADD):

Private Sub cboLs_Change()
Dim i As Long, LastRow As Long, ws As Worksheet
Dim wb As Workbook
Set wb = Workbooks.Open("C:\Users\Desktop\Book1.xlsx")
Set ws = wb.Worksheets("LS")
LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
    If Val(Me.cboLs.Value) = ws.Cells(i, "A") Then
        MsgBox Me.cboLs.Value
        Me.txtProject = ws.Cells(i, "B").Value
    End If
Next i
End Sub


Private Sub cmdadd_Click()
    Dim i As Integer
    Dim wb As Workbook
    Dim ws1 As Worksheet
    Set wb = Workbooks.Open("C:\Users\Desktop\Work.xlsm")
    wb.Activate
    Set ws1 = wb.Worksheets("Sheet1")
    Worksheets("Sheet1").Activate
    'position cursor in the correct cell A2.
    ActiveSheet.Range("A8").Select
    Do Until ActiveCell.Value = Empty
        ActiveCell.Offset(1, 0).Select 'move down 1 row
        i = i + 1 'keep a count of the ID for later use
    Loop
    'Populate the new data values into the 'Data' worksheet.
    ActiveCell.Value = i 'Next ID number
    'Populate the new data values into the 'Data' worksheet.
    ws1.Range("A6").Value = e 'Next ID number
    ws1.Range("B6").Value = Me.txtname.Text 'set col B
    ws1.Range("C6").Value = Me.txtbook.Text 'set col C
    ws1.Range("D6").Value = Me.cboLs.Text
 End Sub


Private Sub UserForm_Initialize()
    Dim i As Long, LastRow As Long, ws As Worksheet
    Dim wb As Workbook
    Set wb = Workbooks.Open("C:\Users\Desktop\Book1.xlsx")
    Set ws = wb.Worksheets("LS")
    LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To LastRow
        Me.cboLs.AddItem ws.Cells(i, "A").Value
    Next i
 End Sub

are you sure you need to be saying "Set"? See https://stackoverflow.com/questions/349613/what-does-the-keyword-set-actually-do-in-vba — Jeremy Kahan, Sep 02 '18 at 12:41
In which line you mean? I used "Set" to read the worksheet as an object. — Santo, Sep 02 '18 at 12:57
My main issue is in my commandbutton code the new workbook with "Sheet1" is not getting activated. My userform data is tranferring to other workbook but not the workbook which I want to be. — Santo, Sep 02 '18 at 13:15
You have worksheet references - use those instead of `Activate` and `Select`. — Comintern, Sep 02 '18 at 13:28
i = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row : you mean this way? — Santo, Sep 02 '18 at 13:30

score 0 · Accepted Answer · answered Sep 03 '18 at 14:54

Very simple:

Set ws1 = wb.Worksheets("Sheet1") throws a Subscript out of Range error, because there is no sheet with the name "Sheet1" contained in the file "C:\Users\Desktop\Work.xlsm"

You are referencing by the Name of the WorkSheet object, this is what is displayed on the little tab. The Object might very well be "Sheet1" in your object explorer in VBA:

In that case you can use Set ws1 = wb.Sheet1
If you wish to use the name, check the tab in the Excel GUI, and use Set ws1 = wb.Worksheets("Whatever is displayed on the little tab at the bottom of your screen")

For some examples on how to reference sheets, read this

Run time error 9 Subscript out of range when activating a workbook

1 Answers1