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.

 

[JLGWhiz]

Please refer to your data in terms of Columns (vertical) and rows (horizontal) when describing where you want to copy or paste to and from. We cannot see your worksheet and have no idea where your headers are located for different data types. eg. Copy from sheet 1, cell A1 to sheet 2, cell A4.

 

[Swayzy]

A fast simple solution would be to record a macro with realtive reference on and just do a manual run of an invoice. Be sure to use endxl down function (ctrl + down arrow) to be sure that you go to the last row of current data then you can press down arrow again for a fresh row or you can insert a row and move up a step and paste. If you want to you can then put this after your print code (I assume that is what happens when you press a "button"

 

[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 (F10), invoice number (F9), supplier (B9), goods description (merged cells B,C and D rows 16-40), sub total (G41), VAT (G42), and Gross amount (G43), 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.

thanks

 

[George1]

Hi there, see cell references enclosed.
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 (F10), invoice number (F9), supplier (B9), goods description (merged cells B,C and D rows 16-40), sub total (G41), VAT (G42), and Gross amount (G43), 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.

 

[George1]

Hi there,
Thanks for your reply - I'm not that good with VBA and to be honest I'm just a beginner. If you have an example of the macro to see it in action would be great. Regards G1

 

[mumps]

The cell references are helpful but since it sounds like you are already using one or more macros, the existing macro that saves to PDF would need to be modified to do what you want. I think that it would be easier to help and test possible solutions if we could work with your actual file which includes any macros you are currently using. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do using a few examples from your data and referring to specific cells, rows, columns and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[George1]

The cell references are helpful but since it sounds like you are already using one or more macros, the existing macro that saves to PDF would need to be modified to do what you want. I think that it would be easier to help and test possible solutions if we could work with your actual file which includes any macros you are currently using. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do using a few examples from your data and referring to specific cells, rows, columns and worksheets. If the workbook contains confidential information, you could replace it with generic data.

Hi Mumps,
Thanks for your reply. here is the Drop Box link https://www.dropbox.com/s/c2tqqjbsp...e next invoice and pdf saving auto .xlsm?dl=0

What i am trying to achieve is:
When the "Save PDF" button on the Invoice tab is clicked, that it will not only save the PDF to a specific drive but also transfer specific cell data from the invoice tab such as Date (Cell F10), Invoice No: (F9), Customer name in (B9), Subtotal (G41), VAT (G42) and Total in Cell G43 into the invoice details tab from Row A2 for recording purposes. I would like to preserve the information in this tab and for each additional Invoice that is added, that it added to the next empty row. automatically. Hope you can open the link and thanks again for your help. Regards.

 

[mumps]

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

 

[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

Thank you Mumps - That's brilliant.