ThisWorkbook.Sheets("Sheet1") generates Run-Time error '9' Subscript out of range

Question

I am trying to build a userform to input a new row (first row after headers).

The userform contains 6 fields of which 4 are combobox (lists) and 2 text box.

When I run the code it gets stuck at: Set ssheet = ThisWorkbook.Sheets("Sheet1")

Private Sub CommandButton1_Click() Dim ssheet As Worksheet Set ssheet = ThisWorkbook.Sheets("Sheet1") nr = ssheet.Cells(Rows.Count, 1).End(x1Up).Row + 1 ssheet.Cells(nr, 1) = Me.tbNAME ssheet.Cells(nr, 2) = Me.cmbStatus ssheet.Cells(nr, 3) = Me.cmbFunds ssheet.Cells(nr, 4) = Me.cmbDD ssheet.Cells(nr, 7) = Me.cmbDistributor ssheet.Cells(nr, 8) = Me.tbComments End Sub Private Sub UserForm_Initialize() Me.tbDate = Date For Each blah In [StatusList] Me.cmbStatus.AddItem blah Next blah For Each blah In [FundsList] Me.cmbFunds.AddItem blah Next blah For Each blah In [DDList] Me.cmbDD.AddItem blah Next blah For Each blah In [DistributorList] Me.cmbDistributor.AddItem blah Next blah End Sub

ThisWorkbook (the one containing the code) doesn't have a "Sheet1"? — SJR
– SJR, Commented Nov 21, 2018 at 16:14
Yes it does have a Sheet1 also called Master Log, I tried both solutions still nothing unfortunately :( — Alessandro
– Alessandro, Commented Nov 21, 2018 at 16:19
If it's called "Master Log" it's not called "Sheet1"! Set ssheet = ThisWorkbook.Sheets("Master Log") The sheet code name is different from the tab name. — SJR
– SJR, Commented Nov 21, 2018 at 16:20

Kubie · Accepted Answer · 2018-11-21 16:23:29Z

1

When using the line:

Set ssheet = ThisWorkbook.Sheets("Sheet1")

Make sure "Sheet1" is actually the name of your sheet on your Workbook

Per your comment you can use:

Set ssheet = ThisWorkbook.Sheets(1) or Set ssheet = ThisWorkbook.Sheets("Master Log")

And for the following line:

nr = ssheet.Cells(ssheet.Rows.Count, 1).End(xlUp).Row + 1

edited Nov 21, 2018 at 16:23

answered Nov 21, 2018 at 16:19

Kubie

1,5714 gold badges13 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Alessandro Over a year ago

Thank you, I changed the name of my Master Log (Sheet1) into Sheet1. now the debugger says that the error is in the following line: nr = ssheet.Cells(Rows.Count, 1).End(x1Up).Row + 1

Kubie Over a year ago

@SJR nice catch

Alessandro Over a year ago

solved with the xlup and not x1up sorry guys so stupid! thank you so much for your help!!!!

Collectives™ on Stack Overflow

ThisWorkbook.Sheets("Sheet1") generates Run-Time error '9' Subscript out of range

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related