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

jo_hivera

New Member
Joined
May 29, 2020
Messages
28
Office Version
  1. 2019
Platform
  1. Windows
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.

VBA Code:
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!
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
VBA Code:
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
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top