November 11, 2017

How to Archive Invoices Data Using VBA

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:

Option Explicit

Sub transferData()
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)
Next i
Sheet3.Cells(erow, 5) = WorksheetFunction.Sum(Worksheets(“Bill Format”).Range(“E4:E14”))
End Sub

2 thoughts on “How to Archive Invoices Data Using VBA

  1. Hello Sir,
    code is working perfect but i need some more things;
    1. all data paste as value
    2. while archiving , a blank row between two record
    and last thing
    3. in another sheet i retrieve all invoice data by putting invoice number.

    please help

  2. Sub TransferData()

    Dim i As Long
    Dim lastrow As Long
    Dim erow As Long

    lastrow = Sheets(“Bill”).Range(“A” & Rows.Count).End(xlUp).Row

    For i = 2 To lastrow
    erow = Sheets(“Archive”).Range(“A” & Rows.Count).End(xlUp).Offset(1, 0).Row
    Range(Cells(i, 1), Cells(i, 5)).Copy Destination:=Sheet2.Cells(erow, 1)

    Next i

    Sheet2.Cells(erow, 5) = WorksheetFunction.Sum(Worksheets(“Bill”).Range(“E9:E19”))

    End Sub

Comments are closed.