Forum Discussion

Leighmichelle's avatar
Leighmichelle
Copper Contributor
Jun 20, 2023

changing "new sheet" function to open duplicate sheet

I am using windows 10 pro, excel 16

I would like to be able to have the "new page" button open another copy of the invoice and number it sequentially .. the ones in the picture I made by using the "create copy" function.

excel

1 Reply

Sort By
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Jun 21, 2023

    Leighmichelle 

    Added sample file. You may need to change the worksheet name to suit your needs.

     

    Private Sub CommandButton1_Click()
    Dim newSheet As Worksheet
    Dim lastInvoiceNumber As Long
    
    ' Determine the last invoice number
    lastInvoiceNumber = GetLastInvoiceNumber()
    
    ' Create a copy of the invoice sheet
    ThisWorkbook.Sheets("Invoice").Copy After:=Sheets(Sheets.Count)
    
    ' Set the new sheet as the active sheet
    Set newSheet = ActiveSheet
    
    ' Rename the new sheet with the sequential invoice number
    newSheet.Name = "Invoice_" & lastInvoiceNumber + 1
    
    ' Update the invoice number in cell B2 of the new sheet
    newSheet.Range("B2").Value = lastInvoiceNumber + 1
End Sub

Function GetLastInvoiceNumber() As Long
    Dim sheet As Worksheet
    Dim lastInvoiceSheet As Worksheet
    Dim lastInvoiceNumber As Long
    
    lastInvoiceNumber = 0
    
    ' Loop through each sheet in the workbook
    For Each sheet In ThisWorkbook.Sheets
        ' Check if the sheet name starts with "Invoice_"
        If Left(sheet.Name,  = "Invoice_" Then
            ' Extract the invoice number from the sheet name
            lastInvoiceNumber = Application.Max(lastInvoiceNumber, Val(Mid(sheet.Name, 9)))
        End If
    Next sheet
    
    GetLastInvoiceNumber = lastInvoiceNumber
End Function

     

     

Resources