Compare two workbook column and pull data from one work book to other

[aravindh8686]

I have two workbooks M.xlsx and Lable DB.xlsm. I need VBA to compare this two workbook column A which has matching values, and when values are matched copy the corresponding Col B and Col C datas from Lable DB.xlsm to the matched value of M.xlsx workbook.

Some condition:

• I will place both files in one folder.
• M.xlsx , workbook’s worksheet name may be different each time.And it may not be open all time.
• Macro will be runned from Lable DB.xlsm
• After pasting data’s to M.xlsx, it will save and close the workbook.

I searched lot in online and tried to use some codes which may satisfy my requirement, but I couldn’t rightly choose since I am not more familiar with codes. Kindly help.

M.xlsx looks like

COL A COL B COL C
[TABLE="width: 224"]
<tbody>[TR]
[TD]FPSMC
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]62/85R2
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]74DC
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]85R2
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]86F-2
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]CFL
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]DS1
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]

Lable DB.xlsm workbook looks like
COL A COL B COL C
[TABLE="width: 224"]
<tbody>[TR]
[TD]62/85R2
[/TD]
[TD]SYS1
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]74DC
[/TD]
[TD]SYS2
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]85R2
[/TD]
[TD]SYS5
[/TD]
[TD]DATABASE
[/TD]
[/TR]
[TR]
[TD]FPSMC
[/TD]
[TD]SYS8
[/TD]
[TD]BLOCK
[/TD]
[/TR]
[TR]
[TD]CFL
[/TD]
[TD]SYS6
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]DS1
[/TD]
[TD]SYS3
[/TD]
[TD]DB3
[/TD]
[/TR]
[TR]
[TD]86F-2
[/TD]
[TD]SYS9
[/TD]
[TD]CKT
[/TD]
[/TR]
</tbody>[/TABLE]


Expected result in M.xlsx

COL A COL B COL C
[TABLE="width: 224"]
<tbody>[TR]
[TD]FPSMC
[/TD]
[TD]SYS8
[/TD]
[TD]BLOCK
[/TD]
[/TR]
[TR]
[TD]62/85R2
[/TD]
[TD]SYS1
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]74DC
[/TD]
[TD]SYS2
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]85R2
[/TD]
[TD]SYS5
[/TD]
[TD]DATABASE
[/TD]
[/TR]
[TR]
[TD]86F-2
[/TD]
[TD]SYS9
[/TD]
[TD]CKT
[/TD]
[/TR]
[TR]
[TD]CFL
[/TD]
[TD]SYS6
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]DS1
[/TD]
[TD]SYS3
[/TD]
[TD]DB3
[/TD]
[/TR]
</tbody>[/TABLE]

 

[aravindh8686]

I have two workbooks M.xlsx and Lable DB.xlsm. I need VBA to compare this two workbook column A which has matching values, and when values are matched copy the corresponding Col B and Col C datas from Lable DB.xlsm to the matched value of M.xlsx workbook.

Some condition:

• I will place both files in one folder.
• M.xlsx , workbookÂ’s worksheet name may be different each time.And it may not be open all time.
• Macro will be runned from Lable DB.xlsm
• After pasting dataÂ’s to M.xlsx, it will save and close the workbook.

I searched lot in online and tried to use some codes which may satisfy my requirement, but I couldnÂ’t rightly choose since I am not more familiar with codes. Kindly help.

M.xlsx looks like

COL A COL B COL C
[TABLE="width: 224"]
<tbody>[TR]
[TD]FPSMC[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]62/85R2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]74DC[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]85R2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]86F-2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CFL[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DS1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Lable DB.xlsm workbook looks like
COL A COL B COL C
[TABLE="width: 224"]
<tbody>[TR]
[TD]62/85R2[/TD]
[TD]SYS1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]74DC[/TD]
[TD]SYS2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]85R2[/TD]
[TD]SYS5[/TD]
[TD]DATABASE[/TD]
[/TR]
[TR]
[TD]FPSMC[/TD]
[TD]SYS8[/TD]
[TD]BLOCK[/TD]
[/TR]
[TR]
[TD]CFL[/TD]
[TD]SYS6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DS1[/TD]
[TD]SYS3[/TD]
[TD]DB3[/TD]
[/TR]
[TR]
[TD]86F-2[/TD]
[TD]SYS9[/TD]
[TD]CKT[/TD]
[/TR]
</tbody>[/TABLE]


Expected result in M.xlsx

COL A COL B COL C
[TABLE="width: 224"]
<tbody>[TR]
[TD]FPSMC[/TD]
[TD]SYS8[/TD]
[TD]BLOCK[/TD]
[/TR]
[TR]
[TD]62/85R2[/TD]
[TD]SYS1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]74DC[/TD]
[TD]SYS2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]85R2[/TD]
[TD]SYS5[/TD]
[TD]DATABASE[/TD]
[/TR]
[TR]
[TD]86F-2[/TD]
[TD]SYS9[/TD]
[TD]CKT[/TD]
[/TR]
[TR]
[TD]CFL[/TD]
[TD]SYS6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DS1[/TD]
[TD]SYS3[/TD]
[TD]DB3[/TD]
[/TR]
</tbody>[/TABLE]

I am making the table looks better here. And one of the VBA I found posted here, but its not satisfying my above requirement, also posting error. Kindly help

Sub UpdateW2()
Dim w1 As Worksheet, w2 As Worksheet
Dim c As Range, FR As Long
Application.ScreenUpdating = False
Set w1 = Workbooks("LABLE DB.xlsm").Worksheets("Sheet1")
Set w2 = Workbooks("M.xlsx").Worksheets("Sheet1")
For Each c In w1.Range("A2", w1.Range("A" & Rows.Count).End(xlUp))
FR = 0
On Error Resume Next
FR = Application.Match(c, w2.Columns("A"), 0)
On Error GoTo 0
If FR <> 0 Then w2.Range("b2" & FR).Value = c.Offset(, -3)
Next c
Application.ScreenUpdating = True

End Sub

M.xlsx looks like

COL A COL B COL C
​

[TABLE="width: 224"]
<tbody style="border-collapse: collapse; width: auto;">[TR]
[TD]FPSMC[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]62/85R2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]74DC[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]85R2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]86F-2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CFL[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DS1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Lable DB.xlsm workbook looks like
COL A COL B COL C
​

[TABLE="width: 224"]
<tbody style="border-collapse: collapse; width: auto;">[TR]
[TD]62/85R2[/TD]
[TD]SYS1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]74DC[/TD]
[TD]SYS2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]85R2[/TD]
[TD]SYS5[/TD]
[TD]DATABASE[/TD]
[/TR]
[TR]
[TD]FPSMC[/TD]
[TD]SYS8[/TD]
[TD]BLOCK[/TD]
[/TR]
[TR]
[TD]CFL[/TD]
[TD]SYS6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DS1[/TD]
[TD]SYS3[/TD]
[TD]DB3[/TD]
[/TR]
[TR]
[TD]86F-2[/TD]
[TD]SYS9[/TD]
[TD]CKT[/TD]
[/TR]
</tbody>[/TABLE]

Expected result in M.xlsx

COL A COL B COL C
​

[TABLE="width: 224"]
<tbody style="border-collapse: collapse; width: auto;">[TR]
[TD]FPSMC[/TD]
[TD]SYS8[/TD]
[TD]BLOCK[/TD]
[/TR]
[TR]
[TD]62/85R2[/TD]
[TD]SYS1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]74DC[/TD]
[TD]SYS2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]85R2[/TD]
[TD]SYS5[/TD]
[TD]DATABASE[/TD]
[/TR]
[TR]
[TD]86F-2[/TD]
[TD]SYS9[/TD]
[TD]CKT[/TD]
[/TR]
[TR]
[TD]CFL[/TD]
[TD]SYS6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DS1[/TD]
[TD]SYS3[/TD]
[TD]DB3[/TD]
[/TR]
</tbody>[/TABLE]