Error 9 (Subscript out of range)

[kitsa]

Hello,
I have created a VBA to open a userform and enter data, but I keep getting this error pop up "Error 9 (Subscript out of range) in procedure Sortit of Module Assorted", but the information still imports to the cells and sorts the spreadsheet. How do I fix this?

Userform VBA:

Private Sub cmdAddProduct_Click()
 Dim X As Integer
 Dim nextrow As Range
On Error GoTo cmdAddProduct_Click_Error
Set nextrow = Sheet5.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0)
For X = 1 To 6
 If Me.Controls("Product" & X).Value = "" Then
 MsgBox "Missing data"
 Exit Sub
 End If
 Next
If WorksheetFunction.CountIf(Sheet5.Range("E:E"), Me.Product4.Value) > 0 Then
 MsgBox "This product code already exists"
 Exit Sub
 End If
 If WorksheetFunction.CountIf(Sheet5.Range("L:L"), Me.Product2.Value) = 0 Then
 Select Case MsgBox("This is a new category. It will now be added to the category list.." _
 & vbCrLf & "Are you sure that you want to proceed?" _
 , vbYesNo Or vbQuestion Or vbDefaultButton1, "Please check this first")
Case vbYes
Case vbNo
 Exit Sub
 End Select
 Sheet5.Cells(Rows.Count, 12).End(xlUp).Offset(1, 0) = Me.Product2.Value
 End If
For X = 1 To 6
 nextrow = Me.Controls("Product" & X).Value
 Set nextrow = nextrow.Offset(0, 1)
 Next
'clear
 For X = 1 To 6
 Me.Controls("Product" & X).Value = ""
 Next
 
Sortit
On Error GoTo 0
 Exit Sub
cmdAddProduct_Click_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdAddProduct_Click of Form frmNewProduct"
 End Sub

Module (Macro Sortit)

Sub Sortit()
On Error GoTo Sortit_Error
 Application.ScreenUpdating = False
 returnto = ActiveSheet.Name
 Sheet5.Activate
 With Sheet5
 Sheet5.Range("B6:G10000").Sort Key1:=Range("C5"), Order1:=xlAscending, Header:=xlGuess
 End With
 ThisWorkbook.Sheets(returnto).Select
 
   On Error GoTo 0
   Exit Sub
Sortit_Error:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Sortit of Module Assorted"
End Sub

 

[Logit]

I ran your macro here and it erred on

[B][COLOR=#ff0000]returnto[/COLOR][/B] = ActiveSheet.Name

Wanting it to be Dim As String which is missing from your macro.

However, Subscript Out Of Range usually indicates an incorrect or missing reference to a range or sheet name or file path, etc.

 

[StephenCrump]

I am guessing that your code is working with a second workbook, and is erroring on the line shown in red:

Sub Sortit()

returnto = ActiveSheet.Name
Sheet5.Activate
'...

[COLOR=#ff0000]ThisWorkbook.Sheets(returnto).Select
[/COLOR]

If I'm correct, this simply means that the worksheet "ActiveSheet.Name" doesn't exist in ThisWorkbook, i.e. the workbook with the code in it.