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

A

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
[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]
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
aravindh8686 said:
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]
Click to expand...


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]
 
Upvote 0
You must log in or register to reply here.

Similar threads

Nick30075
  • Question Question
Generate subset table from Master table
2 3 4
Replies
36
Views
4K
Peter_SSs
Peter_SSs
M
  • Question Question
Compare two columns of data and Copy/Paste info from one workbook to another
Replies
0
Views
447
msigman
M

Forum statistics

Threads
1,223,991
Messages
6,175,820
Members
452,672
Latest member
missbanana

Share this page

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