Automate Search Display Print Archive Data with Excel VBA

How to automate search, display, print and archive data with Excel VBA. A user has inventory data like item code, quantity and price in Sheet1. In Excel Sheet2 he would like to input the item code in cell B3 and he wishes to display the item code, quantity and price in the range A6:C6 as soon as he clicks on the command button ‘search’. Once the data is displayed he would like to print the data in the form of a receipt for his customers. At the same time he would like to maintain a copy of the printed record in Sheet3 with the date.
Watch the training video below and then study the macro code step by step:

Let’s understand the VBA code for the search button:
Sub searchdata()
On Error GoTo MyerrorHandler:
Dim itemcode As Long
itemcode = Sheet2.Range(“B3”)
Set myrange = Sheet1.Range(“A:C”)
If Range(“B3”) = “” Then
MsgBox “You didn’t enter any item code!”
Exit Sub
End If

Range(“A6”).Value = Range(“B3”).Value
Range(“B6”).Value = Application.WorksheetFunction.VLookup(itemcode, myrange, 2, False)
Range(“C6”).Value = Application.WorksheetFunction.VLookup(itemcode, myrange, 3, False)

If Err.Number = 1004 Then
MsgBox “Item code not in the data!”
End If

End Sub

If you notice we have used an error handling technique here. Why? Well, the user may enter an item code that does not exist in the Excel worksheet 1. Sometimes, he may click on the search button by mistake without entering an item code.
Now how do place the appropriate data in the relevant worksheet cells? The item code is copied by using a range reference. The quantity and price are got from the Sheet1 using the worksheet function Vlookup. As we already know, Vlookup looks for an item (itemcode) from a range of cells (myrange) in the columns to the right (column 2 and 3; column 1 has the item code) of the searched item (itemcode).

Macro code for the Print command button:
Sub printdata()
Dim myselection
Dim erow As Long
Dim itemcode As Long
itemcode = Sheet2.Range(“b3”)
erow = Sheet3.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
myselection = MsgBox(“Are you sure you want to print?”, vbOKCancel, “ALERT”)
Select Case myselection
Case vbOK ‘Yes, I want to proceed
Sheet3.Cells(erow, 1) = Date
Sheet3.Cells(erow, 2) = Sheet2.Range(“A6”)
Sheet3.Cells(erow, 3) = Sheet2.Range(“B6”)
Sheet3.Cells(erow, 4) = Sheet2.Range(“C6”)
Case vbCancel ‘ No!
Exit Sub
End Select
End Sub

We define a variable called ‘itemcode’ which gets its value from Range B3 in sheet2. Then using the Select Case and End Select, we perform a variety of actions based on our decision. If we decide to print the displayed data, then the macro code also transfers the date from the system, item-code, quantity and price to worksheet 3. In order to ensure that the next data does not over-write the earlier data, we first find the next blank row before transferring the next data automatically. In case we decide not to print the data due to some error then the VBA code ensures that this data is not transferred to sheet3. You will also notice that when the user clicks on the ‘print’ command button, he sees a print preview of the page. The ide is to save paper during the trials. If you wish to print out then a slight change achieves the goal.

VBA code for the ‘Clear’ command button:
Sub cleardata()
End Sub

The last line of code in the cleardata macro selects the range B3 so that you can start your work again quickly.

Download a sample file for practice:

Published by

Dinesh Kumar Takyar

Welcome to! 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: For a structured Excel VBA training course online you can visit:

3 thoughts on “Automate Search Display Print Archive Data with Excel VBA”

  1. Hello sir!! I want to create online test paper in excel vba, like it’s should be 10qsn with multiple choice. When they are test complete automatically I have to receive mail with marks and candidates name. How to create pls help me. Thanks in advance.

  2. We used Maring Williams practice for a number of disputes but will not use them agnoa.Dishinesty seems to be thee reoccurring theme. Their entitlement in withholding company assets is appalling. Removing DeAnn Pladson from their firm to absolve themselves of her conduct is reprehensible. Good luck but I don’t think Maring will take responsibility for his company’s illicit behavior.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.