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.”)
Sheet1.Activate

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
Worksheets(vendor).Select
End If
Next q

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

ActiveSheet.Cells(erow, 1).Select

ActiveSheet.Paste
End If
Sheet1.Activate

Next i

Application.CutCopyMode = False
End Sub

Download a sample Excel file for practice:

Further reading:
Find Next Blank Row for Data Entry