Add Worksheet with Unique Name


Home           Programming       Favorites      

This function will add a new worksheet with a unique name to the active workbook.

Function SetNewWorksheetName() _
As Boolean
   'Errors will be generated.
   On Error Resume Next
   'Set to false until it
   'successfully completes.
   SetNewWorksheetName = False

   'Set err.number to 0.
   Err.Clear
   Dim objAnything As Object
   Dim strNewWorksheetName As String
   Dim strFileUniqueID As String
   Dim intFileUniqueID As Integer

   'name will be 20090309 format.
   strNewWorksheetName = _
   Year(Date) & _
   Format(Month(Date), "00") & _
   Format(Day(Date), "00")
   'Error will be generated if name already exists.
   Set objAnything = _
   ActiveWorkbook. _
   Sheets(strNewWorksheetName)

   Do Until Err.Number <> 0
      'Error will remain until
      'unique sheetname is genrated.
      'add 001, 002, 003, etc. to
      'sheetname until successfull
      intFileUniqueID = intFileUniqueID + 1
      strFileUniqueID = _
      Format(intFileUniqueID, "000")
      strNewWorksheetName = _
      strNewWorksheetName & strFileUniqueID
      Err.Clear
      Set objAnything = _
      ActiveWorkbook. _
      Sheets(strNewWorksheetName)
   Loop
   Err.Clear
   objExcel.Worksheets.Add.Name = _
   strNewWorksheetName
   Set objWorksheet = objExcel.ActiveSheet
   'Set to True if it successfully
   'reaches this point.
   SetNewWorksheetName = True
   'No errHandler

End Function