Edit macro to perform a Save Workbook & Next Invoice Number

[gatorbug]

I'm new to VBA coding and have an issue with my SaveInvWithNewName code. My workbook contains one open worksheet (Invoice) and four hidden worksheets (Service Date, Work Order, Purchase Order, & Employee). The hidden worksheets need to remain hidden. The open worksheet pulls data from the four hidden worksheets.

I've added a shape and assigned macro to the open worksheet. When I click the shape to perform “Save & Next Invoice Number” function, it goes to the next invoice number and only saves the open worksheet; it does not save the hidden worksheets. Is there a code that can perform a “Save Entire Workbook & Next Invoice Number”?

VBA code:

Sub SaveInvWithNewName()
Dim NewFN As Variant
' Copy Invoice to a new workbook
ActiveWindow.SelectedSheets.Copy
NewFN = "C:\ABC\Invoices\" & Range("D7").Value & ".xlsx"
ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.Close
NextInvoice
End Sub

 

[juddaaaa]

This should do it

Sub SaveInvWithNewName()

    Application.ScreenUpdating = False    

    Dim NewFN As String
  
    NewFN = "C:\ABC\Invoices\" & Range("D7").Value & ".xlsx"
  
    ThisWorkbook.Worksheets.Copy '// THIS WILL COPY ALL THE SHEETS
  
    With ActiveWorkbook
        .SaveAs _
            Filename:=NewFN, _
            FileFormat:=xlOpenXMLWorkbook
      
        .Close _
            SaveChanges:=False
    End With

    Application.ScreenUpdating = True

End Sub

 

[gatorbug]

Sub SaveInvWithNewName() Application.ScreenUpdating = False Dim NewFN As String NewFN = "C:\ABC\Invoices\" & Range("D7").Value & ".xlsx" ThisWorkbook.Worksheets.Copy '// THIS WILL COPY ALL THE SHEETS With ActiveWorkbook .SaveAs _ Filename:=NewFN, _ FileFormat:=xlOpenXMLWorkbook .Close _ SaveChanges:=False End With Application.ScreenUpdating = True End Sub

Thank you. Code is very helpful and it does save entire workbook; however, it does not change Range ("D7") to the next invoice number after clicking "Save & Next Invoice Number" assigned macro button.

First invoice I created is invoice 20-1. When I click "Save & Next Invoice Number", it does save as filename as 20-1, but it does not change Range ("D7") to next invoice number 20-2. Therefore, if I click "Save & Next Invoice Number" again, a message box opens saying ( A file name 'C:\ABC\Invoices\20-1.xlsx' already exists in this location. Do you want to replace it? )

Suggestion?

 

[juddaaaa]

Sorry about that. I've added that in for you now.

Sub SaveInvWithNewName()

    Application.ScreenUpdating = False

    Dim NewFN As String
    Dim CurrentInvoiceNumber As String
    Dim NextInvoiceNumber As String
    
    CurrentInvoiceNumber = Range("D7").Value
    NextInvoiceNumber = "20-" & CStr(Split(CurrentInvoiceNumber, "-")(1) + 1)
    NewFN = "C:\ABC\Invoices\" & CurrentInvoiceNumber & ".xlsx"
    
    Debug.Print CurrentInvoiceNumber, NextInvoiceNumber
  
    ThisWorkbook.Worksheets.Copy '// THIS WILL COPY ALL THE SHEETS

    With ActiveWorkbook
        .SaveAs _
            Filename:=NewFN, _
            FileFormat:=xlOpenXMLWorkbook

        .Close _
            SaveChanges:=False
    End With

    Range("D7").Value = NextInvoiceNumber
    Application.ScreenUpdating = True

End Sub

Just wondering, is the invoicing based on the year i.e 20-1 for the 1st invoice of 2020 and so on.

If so there'll be a little bit more code to add to get that to swap over for a new year.

 

[gatorbug]

Perfect! This works great. Yes, 20 does represent the year. For example, when I start a new invoice for 2021, it will start with 21-1 (21 represents year and 1 will be the first invoice for that year). Thank you.