Follow along with the video below to see how to install our site as a web app on your home screen.
Note: This feature may not be available in some browsers.
Sub SetSheetNames()
Dim i As Integer
Dim ws As Worksheet
Dim DocProps As DocumentProperties
Set DocProps = ThisWorkbook.CustomDocumentProperties
'initial inv num
i = 321
For Each ws In ThisWorkbook.Worksheets
ws.Name = "#" & Year(Now) & "-" & i
'note use "-" not "/" as / not allowed
i = i + 1
Next
DocProps.Add Name:="InvNum", _
LinkToContent:=False, _
Type:=msoPropertyTypeNumber, _
Value:=i
End Sub
Private Sub Workbook_NewSheet(ByVal Sh As Object)
Dim i As Integer
i = ThisWorkbook.CustomDocumentProperties("InvNum").Value
Sh.Name = "#" & Year(Now) & "-" & i
ThisWorkbook.CustomDocumentProperties("InvNum").Value = i + 1
End Sub
Sub InsertNewInvoiceSheet()
Dim StartName As String
Dim ShtNum As Integer
Dim ShtName As String
Dim InvNum As Integer
Dim TempInvNum As Integer
InvNum = 0
StartName = "Invoice 2004 -"
With ActiveWorkbook
'Check each worksheet
For ShtNum = 1 To .Sheets.Count
'Is worksheet an Invoice?
If Left(Sheets(ShtNum).Name, 14) = StartName Then
'If so then get the number of the invoice
TempInvNum = Val(Right(Sheets(ShtNum).Name, 4))
'Check if the invoice number is the highest
If TempInvNum > InvNum Then InvNum = TempInvNum
End If
Next
'Create the new sheet name
ShtName = StartName & " " & String(5 - Len(InvNum), "0") & InvNum + 1
'Add a new sheet
Sheets.Add
'Rename the new sheet
ActiveSheet.Name = ShtName
End With
End Sub