Copy invoice input to separate sheet, save the invoice as pdf & reset invoice sheet

Question

I am looking for VBA code that will copy invoice data to a separate worksheet with a button (e.g. "Save Invoice"), a button that saves the file as .pdf and finally a button with a macro to reset the invoice sheet ("Reset invoice"). I tried some VBA solutions, but I can't seem to find a feasible solution.

• Each new saved input should be saved in an empty row below the previous saved output
• All inputs/rows within the invoice should be copied if they contain data
• A button with "Save as pdf"
• A button to "clear the sheet"

Code:

Sub InvoiceToRecords()

    LastRecordsRow = Worksheets("Invoice Data").UsedRange.Rows.Count
    'determines the # of rows used

    NewRecordsRow = LastRecordsRow + 1
    'Row for pasting latest invoice will be 1 row below the end of the last invoice

    Sheets("Invoice").Activate

    Range("Invoice").Copy Sheets("Invoice Data").Range("D" & Rows.Count).End(xlUp).Offset(1, 0)

    Range("Customer").Copy Sheets("Invoice Data").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    Range("Invoice Number").Copy Sheets("Invoice Data").Range("B" & Rows.Count).End(xlUp).Offset(1, 0)
    Range("Invoice Date").Copy Sheets("Invoice Data").Range("C" & Rows.Count).End(xlUp).Offset(1, 0)

End Sub

Highlighted code

Error message Syntax error

Answer 1

To copy the secondary data as many times as there are invoice details lines, you could do that as below, just replace your code with this one:

Sub InvoiceToRecords()
Dim ws As Worksheet: Set ws = Worksheets("Invoice")
Dim wsData As Worksheet: Set wsData = Worksheets("Invoice Data")
'declare and set the worksheets, amend as required
Dim i As Long, dataRows As Long

    dataRows = ws.Range("Invoice").Columns(1).SpecialCells(xlCellTypeConstants, 23).Count
    'count the number of Invoice lines with data (non-empty)
    ws.Range("Invoice").Copy wsData.Range("D" & Rows.Count).End(xlUp).Offset(1, 0)
    'copy invoice lines to Invoice Data
    For i = 1 To dataRows 'loop from 1 to however many lines your named range "Invoice" has
        ws.Range("Customer").Copy wsData.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
        ws.Range("Invoice Number").Copy wsData.Range("B" & Rows.Count).End(xlUp).Offset(1, 0)
        ws.Range("Invoice Date").Copy wsData.Range("C" & Rows.Count).End(xlUp).Offset(1, 0)
    Next i
End Sub

To save your worksheet as PDF, the following will do, I would use some sort of variable to generate the PDF filename, so you don't keep overwriting the same file, maybe a combination of company & invoice number or even a timestamp would do:

Sub foo()
    Dim ws As Worksheet: Set ws = Sheets("Sheet1")
    Filen = "C:\Users\Lorenz\Desktop\NewPdf.pdf"
    'amend filename & path to save above
    ws.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=Filen, _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=False
End Sub

UPDATE:

Putting it all together now:

Sub InvoiceToRecords()
Dim ws As Worksheet: Set ws = Worksheets("Invoice")
Dim wsData As Worksheet: Set wsData = Worksheets("Invoice Data")
'declare and set the worksheets, amend as required
Dim i As Long, dataRows As Long
'TRANSFER data to Invoice Data
    dataRows = ws.Range("Invoice").Columns(1).SpecialCells(xlCellTypeConstants, 23).Count
    'count the number of Invoice lines with data (non-empty)
    ws.Range("Invoice").Copy wsData.Range("D" & Rows.Count).End(xlUp).Offset(1, 0)
    'copy invoice lines to Invoice Data
    For i = 1 To dataRows 'loop from 1 to however many lines your named range "Invoice" has
        ws.Range("Customer").Copy
        wsData.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        ws.Range("Invoice Number").Copy
        wsData.Range("B" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        ws.Range("Invoice Date").Copy
        wsData.Range("C" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    Next i

'SAVE Invoice as PDF
FilenameValue = ws.Range("Customer") & "_Invoice" & ws.Range("Invoice Number")
FilenameValue = Replace(FilenameValue, " ", "") 'remove spaces
FilenameValue = Replace(FilenameValue, ".", "_") 'replace dots with underscore
Filen = "C:\Users\Lorenz\Desktop\" & FilenameValue & ".pdf"
'amend filename & path to save above
ws.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=Filen, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False

'CLEAR ranges ready for next invoice
ws.Range("Invoice").ClearContents
ws.Range("Customer").ClearContents
ws.Range("Invoice Number").ClearContents
ws.Range("Invoice Date").ClearContents
End Sub