Insert a row and paste value from user input

[jcrook]

Hello,
This is extremely simple but I can't find an example on this board. I'm sure it exists.
I want to get user input on a tool number in the form of alphanumeric. This works ok.
I then Uppercase the letters. This works.
I then check if a worksheet already exists with the name of the tool number. This works.
If there's NOT a sheet, then a new one is created by copying a template and renaming the sheet. This works.
I then want to check the list of tool numbers starting at B9 to the end and insert the tool number alphanumerically. This is NOT working.
Any suggestions would be greatly appreciated.

Option Explicit

Private Sub CommandButton1_Click()

Dim toolName As Variant
Dim i As Integer
Dim lastRow As String
Dim lastOpenCell As Integer
Dim rngCell, testRange As Range



toolName = InputBox("Enter Tool Serial Number." & vbCrLf & _
                     "Upper or Lower Case letters.")

If toolName = "" Then
   MsgBox "Operation Cancelled"
   GoTo 999
Else
   toolName = StrConv(toolName, vbUpperCase)
End If
'Range("D11").Value = toolName - Passed 6/29/2020

Sheets("Start Sheet").Activate
lastRow = Range("B" & Rows.Count).End(xlUp).Row
'Range("D11").Value = lastRow 'Passed 6/29/2020
'testRange = Range("B9: & lastRow")
lastOpenCell = lastRow + 1

  'Check if worksheet already exists - Passed 6/29/2020
For i = 1 To Worksheets.Count
   If Worksheets(i).Name = toolName Then
     MsgBox "A worksheet " & toolName & " Exists!" & vbCrLf & "Operation Cancelled."
     Sheets(toolName).Select
     GoTo CheckList
   End If
Next i

CheckList:

  'Add toolName to list on Start Sheet - Not Passed 6/29/2020
Worksheets("start Sheet").Activate
  For Each rngCell In Range("B9: & lastRow")
      If Cells(rngCell).Value > toolName Then
          Cells(rngCell).Select
          toolName.Copy
          Range(rngCell).EntireRow.Insert
      End If
  Next rngCell
          
  'Create a worksheet "toolName" and copy template worksheet - Passed 6/29/2020
ThisWorkbook.Worksheets("Template").Copy After:=Worksheets(Sheets.Count)
Worksheets(toolName).Activate

        
999 End Sub

Thank you in Advance,
Jay.

 

[Fluff]

Can you please explain what you mean by this

I then want to check the list of tool numbers starting at B9 to the end and insert the tool number alphanumerically

 

[Fluff]

Never mind, how about

Private Sub CommandButton1_Click()
   Dim ToolName As Variant
   Dim LastRow As Long
   Dim rngCell As Range
  
   ToolName = InputBox("Enter Tool Serial Number." & vbCrLf & _
                        "Upper or Lower Case letters.")
  
   If ToolName = "" Then
      MsgBox "Operation Cancelled"
      Exit Sub
   Else
      ToolName = UCase(ToolName)
   End If
  
   If Evaluate("isref('" & ToolName & "'!A1)") Then
      MsgBox "A worksheet " & ToolName & " Exists!" & vbCrLf & "Operation Cancelled."
      Exit Sub
   Else
      ThisWorkbook.Worksheets("Template").Copy After:=Worksheets(Sheets.Count)
      ActiveSheet.Name = ToolName
   End If
    
   Worksheets("start Sheet").Activate
   LastRow = Range("B" & Rows.Count).End(xlUp).Row
   If LastRow < 9 Then
      Range("B9").Value = ToolName
   Else
      For Each rngCell In Range("B9:B" & LastRow)
         If rngCell.Value > ToolName Then
            rngCell.EntireRow.Insert
            rngCell.Offset(-1).Value = ToolName
            Exit Sub
         End If
      Next rngCell
   End If
End Sub

 

[jcrook]

Hi Fluff,
Your code worked perfectly.

Thank you,
Jay.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[jcrook]

Good Morning Fluff,
I've researched the "Evaluate" command a little. I don't understand the "isref" option you used.
I have the line written out below as if I hard coded the ToolName as SA2320.
IF Evaluate("isref('" & ToolName & "'!A1)") Then as
IF Evaluate(isref('SA2320'!A1)) Then - 1
-or-
IF Evaluate(isref("SA2320"!A1)) Then - 2

Am I interpreting the line correctly in option 1 or 2, or (not at all, haha). I think it's option 1.
Also, the "!A1", I thought that referenced a cell not a sheet but maybe it's the entire workbook since I require the user to start on "Start Sheet".
I understand the Evaluate command can eliminate writing out Worksheets or Sheets or Range identifiers.

Thanks in Advance for the education,
Jay.

 

[Fluff]

The Evaluate is simply calculating the Excel formula =ISREF('SA2320'!A1) which returns true or false depending on whether it's a valid cell reference.
So if the sheet exists it returns true.

 

[jcrook]

Ok. Thank you.

Jay.

 

[Fluff]

My pleasure.

 

[jcrook]

Hi Fluff,
I can't believe this is this difficult BUT, I'm trying to add a line in the code you gave me to write the value inside ToolName to cell D3
on the active sheet after it's created in the IF-END IF.
Range("D3").Value = ToolName
What in the world am I doing wrong?

If Evaluate("isref('" & ToolName & "'!A1)") Then
MsgBox "A worksheet " & ToolName & " Exists!" & vbCrLf & "Operation Cancelled."
Exit Sub
Else
ThisWorkbook.Worksheets("Template").Copy After:=Worksheets(Sheets.Count)
ActiveSheet.Name = ToolName
'Inserted Line
Range("D3").Value = ToolName
End If

Thanks,
Jay.