Help with Interesting vlookup, please

boombox · May 28th 2012

Hi!

I have an excel workbook 'managing' invoices for me.

I know Access would be better, but for various reasons it has to be Excel for the moment.

I have a macro which increments an invoice number and saves a PDF of the invoice.

I would like to populate two columns on a worksheet within the workbook where it keeps a record of the invoice number and the total amount it was raised for.

Could anyone help me achieve this? forum.ozgrid.com/index.php?attachment/46049/

Thanks!

P.S. DUMMY DATA IS USED TO PROTECT CUSTOMER INFO.

cytop · May 28th 2012

Re: Help with Interesting vlookup, please

Code

Sub CopyToInvoiceHistory()
    ' Local Variables
    Dim wksInvoiceMaster    As Worksheet
    Dim wksInvoiceHistory   As Worksheet
    
    ' Fill from data on "Invoice Master" worksheet
    Set wksInvoiceMaster = Worksheets("Invoice Master")
    Set wksInvoiceHistory = Worksheets("Invoice History")
    
    wksInvoiceHistory.Range("A" & wksInvoiceHistory.UsedRange.Rows.Count + 1).Value = wksInvoiceMaster.Range("B19").Value
    wksInvoiceHistory.Range("B" & wksInvoiceHistory.UsedRange.Rows.Count).Value = wksInvoiceMaster.Range("G53").Value
    
End Sub

Display More

Very simplistic code as an example. You should add column headers in row 1 so there is something on the Invoice History sheet to start with. Also, there are some rows that have been blanked - you should physically delete everything except row 1 before using as UsedRange is a little fiddly. You might prefer to use a more robust means of finding the last row.

Help with Interesting vlookup, please

Participate now!

Share