title: VBA: Function that will find the sheet name with the highest number of all the sheet names
body: <p>Assume there is a workbook with seven sheets, and the sheet names are as follows:</p>
  
  <p>"Terms"</p>
  
  <p>"Revolvers"</p>
  
  <p>"15"</p>
  
  <p>"22"</p>
  
  <p>"55"</p>
  
  <p>"59"</p>
  
  <p>"146"</p>
  
  <p>I need VBA code that will identify the highest numbered sheet name of any sheet in the whole workbook.  In this example, it should identify 146 as the highest number.  Then the code should create a new sheet, with a sheet name that is one integer higher than the previous sheet name.  In this example, the new sheet would be created and then named "147".  Below is my code, I just need to replace "tab name + 1" with the proper function:</p>
  
  <pre><code>Sub AddSheet()
  
  Dim wb As Workbook: Set wb = ActiveWorkbook
  Dim strName As String: strName = "tab name + 1"
  Dim ws As Worksheet
  Set ws = wb.Worksheets.Add(Type:=xlWorksheet)
  With ws
      .Name = strName
  End With
  
  End Sub
  </code></pre>
  
  <p><strong>Edit Based on BigBen's Answer Below</strong> </p>
  
  <pre><code>Sub AddSheet()
  
  
  Dim wb As Workbook: Set wb = ActiveWorkbook
  Dim strName As String: strName = CStr(MaxSheetNumber(wb) + 1)
  Dim ws As Worksheet
  Set ws = wb.Worksheets.Add(Type:=xlWorksheet)
  With ws
      .Name = strName
  End With
  
  End Sub
  
  Function MaxSheetNumber(ByVal wb As Workbook) As Long
      Dim ws As Worksheet
      For Each ws In wb.Worksheets
          Dim i As Long
          If IsNumeric(ws.Name) Then
              If CLng(ws.Name) &gt; i Then
                  i = CLng(ws.Name)
              End If
          End If
      Next
  
      MaxSheetNumber = i
  End Function
  </code></pre>