Increment Sheets Names

[Ivan Howard]

Hello All,

Please can someone give me a hand. I've had a look online for a solution but cannot find something that will do what I need.

Basically I have a macro that creates a new worksheet called "Results". If the user runs the macro more than once, the macro tries to create the "Results" worksheet again - and breaks. What I need is to get the macro to see that "Results" exists and then create "Results 1"... the next problem comes where "Results 1" also already exists and so forth.

The question is how can I get the macro to create incremented worksheet names so that it doesn't fall over?

Any help would be great - thanks in advance.

Ivan

 

[mumps]

Can you post the macro you are using?

 

[Ivan Howard]

Thanks for looking Mumps.

Starting from a blank workbook that only contains one worksheet called "Sheet1", the following runs through twice as the 1st and 2nd "Results" worksheets are created.


I have created a loop to illustrate the issue I have. At the end of the process, I need to have the following worksheets in the workbook:
Sheet1
Results
Results 1
Results 2
Results 3
Results 4

... but the code crashes. It doesn't seem to reset the "On Error" statement but I'm not sure that's the answer anyway... I'm looking for something that will basically see if a worksheet exists and then creates it if it doesn't... if it does then it adds a "1" on the end and tries the process again. The following is essentially the code I'm using.

Thanks.

Sub CreateNewResultsWorksheets()


Dim a As Integer


    For a = 1 To 5
        Sheets.Add
        
        On Error GoTo ERRORFOUND
            ActiveSheet.Name = "Results"
        On Error GoTo 0
        
        GoTo SKIPERRORTRAP
        
ERRORFOUND:
        ActiveSheet.Name = "Results" & a - 1


SKIPERRORTRAP:
        Range("A1").Select
    Next a


End Sub

 

[Fluff]

How about

Sub Shts()
   Dim i As Long
   If shtexists("Results") Then
      For i = 1 To 5
         If Not shtexists("Results " & i) Then
            Sheets.Add.Name = "Results " & i
            Exit For
         End If
      Next i
   Else
      Sheets.Add.Name = "Results"
   End If
         
End Sub


Public Function shtexists(ShtName As String, Optional Wbk As Workbook) As Boolean
    If Wbk Is Nothing Then Set Wbk = ActiveWorkbook
    On Error Resume Next
    shtexists = (LCase(Wbk.Sheets(ShtName).Name) = LCase(ShtName))
    On Error GoTo 0
End Function

 

[Ivan Howard]

Thanks very much Fluff. This is great and after a quick run, I think it is just what I need. I will work on it again next week.

Thank again and have a great weekend!

Ivan

 

[Fluff]

Glad we could help & thanks for the feedback