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

aravindh8686

New Member
Joined
Oct 31, 2018
Messages
5
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
FPSMC


62/85R2


74DC


85R2


86F-2


CFL


DS1



<tbody>
</tbody>

Lable DB.xlsm workbook looks like
COL A COL B COL C
62/85R2
SYS1

74DC
SYS2

85R2
SYS5
DATABASE
FPSMC
SYS8
BLOCK
CFL
SYS6

DS1
SYS3
DB3
86F-2
SYS9
CKT

<tbody>
</tbody>


Expected result in M.xlsx

COL A COL B COL C
FPSMC
SYS8
BLOCK
62/85R2
SYS1

74DC
SYS2

85R2
SYS5
DATABASE
86F-2
SYS9
CKT
CFL
SYS6

DS1
SYS3
DB3

<tbody>
</tbody>
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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
FPSMC
62/85R2
74DC
85R2
86F-2
CFL
DS1

<tbody>
</tbody>

Lable DB.xlsm workbook looks like
COL A COL B COL C
62/85R2SYS1
74DCSYS2
85R2SYS5DATABASE
FPSMCSYS8BLOCK
CFLSYS6
DS1SYS3DB3
86F-2SYS9CKT

<tbody>
</tbody>


Expected result in M.xlsx

COL A COL B COL C
FPSMCSYS8BLOCK
62/85R2SYS1
74DCSYS2
85R2SYS5DATABASE
86F-2SYS9CKT
CFLSYS6
DS1SYS3DB3

<tbody>
</tbody>


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
FPSMC
62/85R2
74DC
85R2
86F-2
CFL
DS1

<tbody style="border-collapse: collapse; width: auto;">
</tbody>

Lable DB.xlsm workbook looks like
COL A COL B COL C
62/85R2SYS1
74DCSYS2
85R2SYS5DATABASE
FPSMCSYS8BLOCK
CFLSYS6
DS1SYS3DB3
86F-2SYS9CKT

<tbody style="border-collapse: collapse; width: auto;">
</tbody>


Expected result in M.xlsx

COL A COL B COL C
FPSMCSYS8BLOCK
62/85R2SYS1
74DCSYS2
85R2SYS5DATABASE
86F-2SYS9CKT
CFLSYS6
DS1SYS3DB3

<tbody style="border-collapse: collapse; width: auto;">
</tbody>
 
Upvote 0

Forum statistics

Threads
1,212,932
Messages
6,110,748
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top