May 21, 2015

Transfer Data to Specific Excel Worksheet Using VBA

How can we create a search engine in Sheet1 so that when a button is clicked we can transfer data to specific Excel worksheet using VBA based on a vendor name or number. This just means that we have our data in sheet1. Now we wish to transfer a specific vendor’s or supplier’s data to a specific worksheet on click of a button automatically. Let’s think through the problem to find a solution. First we need to identify a specific vendor. Now we need to transfer all the data related to this vendor to a specific worksheet named after him. Therefore we need to loop through all the data in sheet1 starting from row 2 since the first row has headers. The last row used in sheet1 should also be defined or known. Next we need to find out how many total worksheets our workbook has. Once this is accomplished we loop through all the worksheets and identify the specific worksheet name where we wish to transfer the data. Here we locate the next blank row for data pasting. Since our nested looping or loop within a loop involves in going from one sheet to another in the transfer process, we must ensure that we copy from the correct sheet and paste the transferred data into an appropriate worksheet. Watch the training vide below before you study the complete VBA code:

Watch the video on YouTube.

The complete VBA code:
Sub testTransfer()
Dim lastrow As Long
Dim erow As Long
Dim vendor As String
lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
vendor = InputBox(“Enter name or number of vendor.”)

For i = 2 To lastrow
If Cells(i, 1) = vendor Then
Range(Cells(i, 2), Cells(i, 3)).Copy
Dim p As Integer, q As Integer

p = Worksheets.Count

For q = 1 To p

If ActiveWorkbook.Worksheets(q).Name = vendor Then
End If
Next q

erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

ActiveSheet.Cells(erow, 1).Select

End If

Next i

Application.CutCopyMode = False
End Sub

Download a sample Excel file for practice:

Further reading:
Find Next Blank Row for Data Entry

3 thoughts on “Transfer Data to Specific Excel Worksheet Using VBA

  1. Hi Sir,

    Thanks for this macro.

    But my doubt is, it is restricted to copy of 2 cells for a specific vendor, what if any particular vendor is followed by product, amount, quality, price (assume any value for quality and price).

    Note – quality and price is just header like, product and amount.


  2. Pranam Mahodhai,

    Mera Naam D Joji Redy.(Hyderabad) INDIA. Mein ne aap ka Excel training videos last one year se follow kar raha hui. Aap ka videos muje bahuth kaam araha hai. Isse bath per aap ko mera dhanyawad svekaar kejiye. Paranthu muje ek sankat me ullja hui. Weh yeh hai ki:-

    1. Mera pass Main Sheet per pura data enter kiya huwa hai. Lekin iss main sheet data se kuch specific hole columns data muje Sheet2, Sheet6, Sheet9 etc mein data copy and paste hona chahiya. Aur Jab mein New data main sheet per enter kartha hui tab Automatically baki sheets per data update hona chahiye Ek our bath iss ke aunder koye bhe duplicates nahi ana chahiye.

    a. Example:- Main Sheet

    EmpID(C5), Emp Name(D6), Designation(E6), Grade(F6), Location(G6), Ref No(H6),
    DOB(I6), Name of Bank(J6), Acct No(K6), Fathers Name(L6) and So on (Total 55 colums data hai)

    My requirement is- The Data require in Sheet2, Sheet4, Sheet7 and So on

    EmpID(C5), Emp Name(D6), Grade(F6), Ref No(H6) {Same columns in all three sheets}

    2. Main sheet data is same. lakin iss bar muje different columns different sheets per with out any duplicates copy and paste hona chahiye. [Pura columns data] Agar main sheet per koye bhi change kiya to bha ki sheets per bhi automatically update hona chahiye.

    b. Example:- Main Sheet

    EmpID(C5), Emp Name(D6), Designation(E6), Grade(F6), Location(G6), Ref No(H6),
    DOB(I6), Name of Bank(J6), Acct No(K6), Fathers Name(L6) and So on

    My requirement is- The Data require in Sheet3:-

    EmpID(C5), Emp Name(D6), DOB(I6), ———

    In Sheet5:-

    EmpID(C5), Emp Name(D6),Fathers Name(L6), Location(G6) ——-

    In Sheet6:-

    EmpID(C5), Emp Name(D6), Name of Bank(J6), Acct No(K6), ———-

    Mahodhai jee upeer diyagaya ulljan se muje sahi rasta dikaye.
    Aap ka fllower

  3. thank you sir ,
    i watch about all video,can make simple on one workshet say invoice on sheet 1 and data transfer to sheet2 that can be name jornal entry and same should be transfer to relevant buyer say sheet 3 sheet 4 sheet 5 a complete account system in my view sir pls do favour on these thanks in advance

Comments are closed.