changing "new sheet" function to open duplicate sheet

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