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

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

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

Leave a Reply

Your email address will not be published. Required fields are marked *