VBA to extract specific data from sheet 1 to transfer into sheet 2

[George1]

Hi there, I currently have a master invoice template that I use for invoicing customers. I have put some automation on this such as next invoice and save as pdf to a particular drive. I am looking to capture specific data from the invoice template, when I click the "save to PDF" button, to capture invoice date, invoice number, supplier, goods description, sub total, VAT and Gross amount and save these particular bits of data to an invoice details sheet that I will hold on the same workbook to track suppliers and invoices at a glance. Any suggestions would be appreciated. Regards G1.

 

[mumps]

You are very welcome.

 

[George1]

You don't have a column for the Invoice Number in the "Invoice Details" sheet. Insert a new column B to the right of the "Date" column and name it "Invoice #". Replace the current macro that saves to PDF by the one below:

Sub SavePDF()
    Dim LastRow As Long
    LastRow = Sheets("Invoice Details").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:="C:\Users\sheeh\Documents\Suppliers\Invoice_" & _
        ActiveSheet.Range("F9").Value & ".pdf", _
        OpenAfterPublish:=True
    Sheets("Invoice Details").Cells(LastRow, 1).Resize(1, 6) = Array(Range("F10").Value, Range("F9").Value, Range("B9").Value, Range("G41").Value, Range("G42").Value, Range("G43").Value)
End Sub

Hi there,
You kindly helped me out with the VBA coding above recently. The button to saved to a PDF was picking up the invoice number and customer name and putting these into the title of the file but I've just noticed that when the file is saved, it does not pick up the supplier name in cell "B9". I've tried amending the range to include B9 along with F9 (Invoice number) but I keep getting an error. If you have any suggestions how I could fix this please? Regards G1.

 

[mumps]

Get back soon.

 

[mumps]

Try:

Sub SavePDF()
    Dim LastRow As Long
    LastRow = Sheets("Invoice Details").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:="C:\Users\sheeh\Documents\Suppliers\Invoice_" & ActiveSheet.Range("B9").Value & ActiveSheet.Range("F9").Value & ".pdf", OpenAfterPublish:=True
    Sheets("Invoice Details").Cells(LastRow, 1).Resize(1, 6) = Array(Range("F10").Value, Range("F9").Value, Range("B9").Value, Range("G41").Value, Range("G42").Value, Range("G43").Value)
End Sub

 

[George1]

Try:

Sub SavePDF()
    Dim LastRow As Long
    LastRow = Sheets("Invoice Details").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:="C:\Users\sheeh\Documents\Suppliers\Invoice_" & ActiveSheet.Range("B9").Value & ActiveSheet.Range("F9").Value & ".pdf", OpenAfterPublish:=True
    Sheets("Invoice Details").Cells(LastRow, 1).Resize(1, 6) = Array(Range("F10").Value, Range("F9").Value, Range("B9").Value, Range("G41").Value, Range("G42").Value, Range("G43").Value)
End Sub

Thank you

 

[mumps]

My pleasure.