Merging two worksheets together.

[StruglnPP]

I need some help merging these two worksheets, can someone please help me.

Worksheet 1.xlsm
Worksheet 2.xlsm

What I need is from worksheet 1 the field "Link Code" to be placed in worksheet 2 under the proper name in ws1. Using the Merchant ID could be a reference point to compare.

[jaslake]

Hi StrugInPP

What Column would you like to put it in?

[StruglnPP]

Column K "Link Code" from worksheet 1

[jaslake]

Hi StrugInPP

If I understand you correctly you wish to find Worksheet1 Merchant ID in Worksheet2. Having found a match in Worksheet2 you wish Worksheet1 Column K (Link Code) inserted into Worksheet2.

If my understanding is correct, where in Worksheet2 should it be inserted?

[StruglnPP]

Yes, That would be correct or just merge the two together.

[jaslake]

Hi StrugInPP

Place the two attached files in the same folder. Run the code from the button in Worksheet1. Let me know of issues.

Option Explicit

Sub Update_Book()
    Dim wb1 As Workbook
    Dim wb2 As Workbook
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim Rng1 As Range
    Dim Rng2 As Range
    Dim cel As Range
    Dim LR As Long
    Dim LC As Long
    Dim myPath As String
    Dim wasOpen As Boolean


    myPath = ThisWorkbook.Path & "\"
    Set wb1 = ThisWorkbook
    Set ws1 = wb1.Sheets("Sheet1")
    On Error Resume Next
    Set wb2 = Workbooks.Open(myPath & "worksheet 2.xlsx")
    Set ws2 = wb2.Sheets("Sheet1")
    On Error GoTo 0

    Application.ScreenUpdating = False

    If wb2 Is Nothing Then
        Set wb2 = Workbooks.Open(Workbooks.Open(myPath & "worksheet 2.xlsx"))
    Else
        wasOpen = True
    End If

    With ws2
        LC = .Cells.Find(What:="*", After:=[A1], _
                SearchOrder:=xlByColumns, _
                SearchDirection:=xlPrevious).Column + 1
    End With

    With ws1
        LR = .Range("C" & .Rows.Count).End(xlUp).Row
        Set Rng1 = .Range("C2:C" & LR)
        For Each cel In Rng1
            With ws2.Columns("A:A")
                Set Rng2 = .Find(What:=cel, _
                        After:=.Cells(.Cells.Count), _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)
                If Not Rng2 Is Nothing Then
                    ws2.Cells(Rng2.Row, LC).Value = ws1.Cells(cel.Row, 11).Value
                End If
            End With
        Next cel
    End With
    Application.ScreenUpdating = True
End Sub

[StruglnPP]

Thank you it works great. After the holidays I will be able to buy you a cup of coffee for your help, sorry i can't do more.

[jaslake]

Hi StrugInPP

You're welcome...glad I could help. Coffee will be fine as long as it's before 10:00 AM.