Increment number to return to 0 or number after the last sheet created

[jo_hivera]

Good afternoon all!

I've been trying to tweak this code, based on other posts i saw around, similar to this situation.

I use the macro to copy/paste the template sheet and it names the new sheet according to a code and the number it stores on cell H2 of the template. This number increments but the rest stays the same.
The problem is, I need it to return to zero or the previous number if I delete sheets.

For example, the name of the sheets created is ORC01_R0 and if i create a new one, it names the new sheet ORC02_R0. (if others are added, then ORC03_R0, ORC04_R0 and so on)
If i delete all sheets created, the name of a newly created sheet will be ORC01_R0.
If I only delete ORC02_R0, then the next sheet should be ORC02_R0, because the previous one is ORC01_R0.

My code currently keeps the last number used and doesn't return to zero if i delete all sheets, or to the previous number if the sheet name doesn't exist.

Sub NewNum()

' This macro assumes that there is a worksheet named "Template"
' and that worksheet contains the current sheet number in cell H2.

    Dim i As Long, temp As String
    Dim iNextNum As Long
    Dim ws As Worksheet
    Dim REV As Variant
    Dim Year As String, wsName As String
    Year = DatePart("YYYY", Date)
    On Error GoTo 0
    
    Set ws = Sheets("Template")
    ws.Visible = xlSheetVisible
    REV = ws.Range("I2").Value
    
    With Sheets("Template").Range("H2")
        iNextNum = .Value + 1
        .Value = iNextNum
    End With
    
    wsName = "ORC" & Format(iNextNum, "0") & "-" & Right(Year, 2) & "_R" & REV
    
    If WorksheetExists(wsName) Then
     temp = wsName
     i = 1
     wsName = temp & i
     Do While WorksheetExists(wsName)
     i = i + 1
     wsName = temp & i
     Loop
    End If
    
    Sheets("Template").Copy After:=Sheets(Sheets.Count)
    
    ActiveSheet.Name = wsName
    ws.Range("I2").Value = i
    ws.Visible = xlSheetHidden
    
End Sub

 Function WorksheetExists(wsName As String) As Boolean

 On Error Resume Next
 WorksheetExists = Worksheets(wsName).Name = wsName
 On Error GoTo 0
 End Function

All suggestions are welcome!

 

[BobUmlas]

Sub NewNum()
On Error Resume Next
n = 0
Sheets("Template").Copy after:=Sheets(Sheets.Count)
Do
Err.Clear
n = n + 1
ActiveSheet.Name = "ORC" & Format(n, "00") & "_R0"
If Err.Number = 0 Then Exit Sub
Loop
End Sub

 

[jo_hivera]

Sub NewNum()
On Error Resume Next
n = 0
Sheets("Template").Copy after:=Sheets(Sheets.Count)
Do
Err.Clear
n = n + 1
ActiveSheet.Name = "ORC" & Format(n, "00") & "_R0"
If Err.Number = 0 Then Exit Sub
Loop
End Sub

Thank you for the suggestion Bob. It works, aside from the number stored in cell H2. It names the sheet correctly, but the cell H2 is not storing the same number as the N result (If ORC01_R0, H2 is 1, if ORC02_R0, H2 is 2, and so on). Help?

 

[BobUmlas]

No need to store that number in H2. That was the original problem! If H2 is 6 and you have sheets named ORC01_R0 thru ORC04_R0, would you want the next sheet to be ORC05_R0 or ORC06_R0? How is H2 helpful?

 

[jo_hivera]

No need to store that number in H2. That was the original problem! If H2 is 6 and you have sheets named ORC01_R0 thru ORC04_R0, would you want the next sheet to be ORC05_R0 or ORC06_R0? How is H2 helpful?

The number in H2 is the print number, it was supposed to work like an invoice number.
If I have sheets named ORC01_R0 thru ORC04_R0 the next one will be ORC05_R0 and the number in h2 will be returning 5.
When we print the sheet created, that number is needing for project tracking purposes ? sorry if i didn't make that clear

 

[BobUmlas]

In your original question, you said " If I only delete ORC02_R0, then the next sheet should be ORC02_R0, because the previous one is ORC01_R0. "
What would you want H2 to then contain? And how would it be used when you create the ORC02_R0 sheet? And THEN what would you want H2 to be?
The use of H2 is still pretty confusing to me.

 

[jo_hivera]

In your original question, you said " If I only delete ORC02_R0, then the next sheet should be ORC02_R0, because the previous one is ORC01_R0. "
What would you want H2 to then contain? And how would it be used when you create the ORC02_R0 sheet? And THEN what would you want H2 to be?
The use of H2 is still pretty confusing to me.

I'm sure i'm not making it easy to understand. Sorry!
What i'm trying to say is that the number in H2 should be the N value.

 

[BobUmlas]

But when you delete one from the middle, H2 doesn't change. So if you have 01,02,04,05 and even if H2 is 3, to fill in the gap, what would make it then become 6 next?
I think my first solution is really what you're looking for. I can modify it to change the H2 value:
Sub NewNum()
Dim temp As Worksheet
On Error Resume Next
n = 0
Sheets("Template").Copy after:=Sheets(Sheets.Count)
Do
Err.Clear
n = n + 1
ActiveSheet.Name = "ORC" & Format(n, "00") & "_R0"
If Err.Number = 0 Then Exit Do
Loop
n = 0
Do
Err.Clear
n = n + 1
Set temp = Sheets("ORC" & Format(n, "00") & "_R0")
If Err.Number <> 0 Then Exit Do
Loop
Sheets("Template").Range("H2").Value = n
End Sub

 

[jo_hivera]

But when you delete one from the middle, H2 doesn't change. So if you have 01,02,04,05 and even if H2 is 3, to fill in the gap, what would make it then become 6 next?
I think my first solution is really what you're looking for. I can modify it to change the H2 value:
Sub NewNum()
Dim temp As Worksheet
On Error Resume Next
n = 0
Sheets("Template").Copy after:=Sheets(Sheets.Count)
Do
Err.Clear
n = n + 1
ActiveSheet.Name = "ORC" & Format(n, "00") & "_R0"
If Err.Number = 0 Then Exit Do
Loop
n = 0
Do
Err.Clear
n = n + 1
Set temp = Sheets("ORC" & Format(n, "00") & "_R0")
If Err.Number <> 0 Then Exit Do
Loop
Sheets("Template").Range("H2").Value = n
End Sub

I decided to retweak my code, based on your inputs and ended with this solution. So far works the way I intended.

Private Sub SUBMIT_Click()
  
 Dim i As Long, wsName As String
 Dim ws As Worksheet
 Dim Year As String
 Year = DatePart("YYYY", Date)
 
 Set ws = Worksheets("Template")
 
  ws.Visible = xlSheetVisible
         
    With ws
     .Cells(9, 11).Value = PART1.Value
     .Cells(10, 11).Value = FILE1.Value
     .Cells(9, 19).Value = ANNUAL1.Value
     .Cells(10, 20).Value = MINLSIZE1.Value
     .Cells(11, 20).Value = PTSHOT1.Value
     .Cells(13, 11).Value = INJ1.Value
     .Cells(13, 17).Value = INJ2.Value
     .Cells(14, 11).Value = INJ3.Value
     .Cells(16, 11).Value = RISK1.Value
    End With
  
 PART1.Value = ""
 FILE1.Value = ""
 ANNUAL1.Value = ""
 MINLSIZE1.Value = ""
 PTSHOT1.Value = ""
 INJ1.Value = ""
 INJ2.Value = ""
 INJ3.Value = ""
 RISK1.Value = ""
 
 'When you submit data, it creates new employee sheet,
    'using the "Template" Worksheet and naming it after them
    Sheets("Template").Copy After:=Sheets(Sheets.Count)
    Do
        i = i + 1
        wsName = "ORÇ" & i & "-" & Right(Year, 2)
    Loop While WorksheetExists(wsName)
    ActiveSheet.Name = wsName
    ActiveSheet.Range("U3").Value = i
    ws.Visible = xlSheetVeryHidden
 
 UserForm2.Hide
 
End Sub

Function WorksheetExists(wsName As String) As Boolean
    On Error Resume Next
    WorksheetExists = Worksheets(wsName).Name = wsName
    On Error GoTo 0
End Function