Read-in CustomerID from Sheet1, find CustomerID on Sheet 2, copy/paste CustomerName to Sheet1

Question

I'm quite new at excel, so please bear with me.

On Sheet1 of my workbook I have data like CostumerID, CostumerName, Email and so on spread in columns A-G. CostumerID is filled with ID's and CostumerName is empty.

On Sheet2 of my workbook I have 2 columns (A and B). Column A contains CustomerID and column B contains the correct CustomerName for that CustomerID.

My problem is:

I need a macro that goes to Sheet1, reads the CustomerID in column A, then goes to Sheet2, find that CustomerID also in column A, get the CustomerName in column B and paste that name to column B in Sheet1 next to cell from which it got the first CustomerID.

Further ellaboration:

Step 1: Get value of cell in column A on Sheet 1

Step 2: Go to Sheet2, find that value in column A, then copy CustomerName from same row.

Step 3: Paste that CostumerName into column B on the same row as original value from column A on Sheet1.

Sheet1 can be 100-6000 rows, and it needs to check all CustomerID's in column A in Sheet1 and find the corresponding CustomerName. There can be duplicates in column A on Sheet1, but the value of columns D, F and G are all unique.

Normally I would present my attempt on how to do this, but I have no idea how to do this.

Anybody have an idea?

Any help would be greatly appreciated!

You don't need to use VBA: use VLOOKUP to achieve this - see support.office.com/en-in/article/… to get started — Mark Wickett

amg amg · Accepted Answer · 2015-03-26T13:47:39

" Mark Wickett " has correctly pointed it out. But if you still want to go to the vba than here it is.

say here is the screenshot of your first sheet enter image description here

and here is screenshot of your second sheet

enter image description here

Here is the Code

Dim rngCust As Range
Dim rNo As Long
Dim strCust As String
Dim rngSearch As Range
Dim rngFound As Range
Dim intFound As Long

Application.ScreenUpdating = False

Set rngCust = Sheets(1).Range(Cells(2, 1), Cells(2, 1).End(xlDown))
    For Each cell In rngCust.Cells
        rNo = cell.Row
        strCust = Cells(rNo, 1).Value
        Sheets(2).Select
            Set rngSearch = Sheets(2).Range(Cells(2, 1), Cells(2, 1).End(xlDown))
            Set rngFound = rngSearch.Find(What:=strCust, Lookat:=xlWhole, _
                           SearchOrder:=xlByRows, searchdirection:=xlNext, _
                           MatchCase:=False)
            If Not rngFound Is Nothing Then
                intFound = rngFound.Row
            End If
        Sheets(1).Select
        Sheets(1).Cells(rNo, 2).Value = Sheets(2).Cells(intFound, 2).Value

    Next
    Application.ScreenUpdating = True

here is the Output

enter image description here

Read-in CustomerID from Sheet1, find CustomerID on Sheet 2, copy/paste CustomerName to Sheet1

1 Answers