How to archive invoices data using VBA in Microsoft Excel. A user has created an invoice template where he enters a code and the item name and the rate is automatically filled in the invoice using Vlookup. The user enters the quantity purchased after weighing it and the final price is also automatically calculated by multiplying the rate of the item with the quantity purchased. Now we can automate the archiving of the invoice details including the date of invoice, invoice number, the prices of each item purchased and the total invoice value. Watch the video below to learn step by step how the invoice data is transferred from the invoice worksheet to the archive worksheet.
Watch this video on YouTube.
Here’s the complete VBA code to archive the invoices data:
Dim i As Long
Dim lastrow As Long
Dim erow As Long
lastrow = Sheets(“Bill Format”).Range(“A” & Rows.count).End(xlUp).Row
For i = 2 To lastrow
erow = Sheets(“Transfer File”).Range(“A” & Rows.count).End(xlUp).Offset(1, 0).Row
Range(Cells(i, 1), Cells(i, 5)).Copy Destination:=Sheet3.Cells(erow, 1)
Sheet3.Cells(erow, 5) = WorksheetFunction.Sum(Worksheets(“Bill Format”).Range(“E4:E14”))