VBA Subscript out of range on selection

Question

I have 2 values in sheet1 which I want to migrate to sheet2. As of now my my CommandButton works fine till the point I create a newsheet. But as soon as I select my newsheet it gives me a subscript out of Range error. On commenting my last code of line the program runs fine.

Private Sub CommandButton1_Click()

Dim CustomerName As String, CustomerProblem As Integer, newSheet As Worksheet, newName As String


Do
    newName = Application.InputBox("What do you want to name the new sheet?", Type:=2)
    If newName = "False" Then Exit Sub: Rem cancel pressed

    Set newSheet = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(1))

    On Error Resume Next
        newSheet.Name = newName
        newName = Error
    On Error GoTo 0

    If newName <> vbNullString Then
        Application.DisplayAlerts = False
        newSheet.Delete
        Application.DisplayAlerts = True
        MsgBox newName
    End If
Loop Until newName = vbNullString

Worksheets("sheet1").Select
CustomerName = Range("C4")
CustomerProblem = Range("C5")

This line gives me the error.

Worksheets("newName").Select

score 0 · Accepted Answer · edited May 23 '17 at 12:28

0

Anything within quotes will be considered as a string and not a variable. Change

Worksheets("newName").Select

to

Worksheets(newName).Select

Also there is hardly any need to use .Select/.Activate. You may want to see THIS

Followup from comments

Is this what you are trying?

Private Sub CommandButton1_Click()
    Dim CustomerName As String
    Dim CustomerProblem As Integer
    Dim newSheet As Worksheet
    Dim newName As Variant

    Do
        newName = InputBox("What do you want to name the new sheet?", _
                           "Please enter a sheet name")
        If newName = False Or Len(Trim(newName)) = 0 Then Exit Sub

        Application.DisplayAlerts = False
        On Error Resume Next
        ThisWorkbook.Sheets(newName).Delete
        On Error GoTo 0
        Application.DisplayAlerts = True

        Set newSheet = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(1))

        newSheet.Name = newName

        newSheet.Activate
    Loop
End Sub

edited May 23 '17 at 12:28

Community

1
1

answered Jan 08 '14 at 11:00

Siddharth Rout

147,039
17
206
250

In that case, Check what is the value of `newName`? It cannot be blank or cannot contain unwanted spaces. It has to contain the exact name as that of the worksheet. – Siddharth Rout Jan 08 '14 at 11:10
if you take a look at the code above. I am getting the name of the worksheet as an input from the user. newSheet.Name = newName – user2735206 Jan 08 '14 at 11:13
Do you want to add a new sheet with the name that the user provides? – Siddharth Rout Jan 08 '14 at 11:15

VBA Subscript out of range on selection

1 Answers1