Private Sub CommandButton1_Click()
[COLOR="SeaGreen"]'
'Purpose:
'To increment sheet names in the following manner:
'PCO 1 rev 1
'PCO 1 rev 2
'PCO 1 rev 3
'PCO 1 rev 4
'PCO 1 rev 5
'PCO 2 rev 1
'... etc. with 5 revisions for each PCO number, up to 100 PCO numbers
'This macro does not require sheets to be in order.
'This macro does require that all PCO sheets have exactly 11 characters in the above form.
'---------------------------------------------------------------------[/COLOR]
Dim ws As Worksheet
Set ws = Sheets("Template").Copy(After:=Sheets(Sheets.Count))
ws.Name = NextPCO()
End Sub
'-----------------------------------
Private Function NextPCO() As String
Dim ws As Worksheet
Dim i As Integer [COLOR="seagreen"]'temp variable[/COLOR]
Dim p As String [COLOR="seagreen"]'PCO Number - 1 to 100[/COLOR]
Dim r As String [COLOR="seagreen"]'Revision Number - 1 to 5[/COLOR]
Dim blnFound As Boolean
Dim a(0 To 1) As String
For Each ws In ThisWorkbook.Worksheets
[COLOR="seagreen"]'//Find pco sheets. They must have 11 characters[/COLOR]
If (LCase(Left(ws.Name, 3)) = "pco") And (Len(ws.Name) = 11) Then
blnFound = True [COLOR="seagreen"]'//flag that a pco already exists[/COLOR]
p = Mid(ws.Name, 5, 1) [COLOR="seagreen"]'//pco number[/COLOR]
r = Mid(ws.Name, 11, 1) [COLOR="seagreen"]'//revision number[/COLOR]
[COLOR="seagreen"]'//determine if it is the highest pco number so far[/COLOR]
If p = udfMaxInt(i, CInt(p)) Then
i = CInt(p)
a(0) = p
a(1) = r
End If
End If
Next ws
[COLOR="seagreen"]'//if revision number is 5 then start a new series[/COLOR]
If a(1) > 4 Then
a(0) = a(0) + 1
a(1) = 1
End If
[COLOR="seagreen"]'//if pco number is 100 and revision is 5, STOP.[/COLOR]
If a(0) > 99 And a(1) > 4 Then
MsgBox "PCO numbers cannot be greater than 100."
End [COLOR="seagreen"]'//Complete stop.[/COLOR]
End If
[COLOR="seagreen"]'//return result[/COLOR]
If blnFound Then
NextPCO = "PCO " & a(0) & " rev " & a(1)
Else
NextPCO = "PCO 1 rev 1" [COLOR="seagreen"]'//we're on the first pco sheet[/COLOR]
End If
End Function
'----------------------------------------------------------------
Private Function udfMaxInt(ByVal arg1 As Integer, arg2 As Integer)
[COLOR="seagreen"]'Returns higher of two integers[/COLOR]
If arg1 > arg2 Then
udfMaxInt = arg1
Else
udfMaxInt = arg2
End If
End Function