pulling one coumn & related data from one file to other in excel

[ssbhatban]

Dear friends

I have two excel files
1. master
2. marks

There are multiple columns in each file . . now i am creating one more file called combine. my reference column is barcode column in both master & marks file. i want to bring all the data in master file to combine file as well as data from marks file to combine file . data from master file should come as it is , but the barcode in marks file will not be arranged as in master file. i want it to get arranged as in master file when it is imported to combine file and same way marks & bundle columns should also should get arranged in combine file

i am attaching image files & excel sample file for reference. i will be having hundreds of data. i will be having all the file in the same folder
what is the best & simple way to do it

thanks in advance

 

[mumps]

My pleasure.

 

[ssbhatban]

Dear Friend, thank you very much it is working like a charm. My major problem is solved, thank you once again for spending your time & effort

 

[ssbhatban]

My pleasure.

I want to learn vba programming from scratch for excel, where can i get the best tutorial

 

[mumps]

I found that the best way to learn VBA on your own is to use on-line tutorials, searching the web for specific areas of interest and following forums such as this one. The volunteers on sites such as this one are invaluable at providing help. I would suggest you keep a file of codes that you find useful and in this way you can build up a library that you can refer to. A lot is also trail and error. The more you practise, the better your get at it. A good place to start would be the very first post in this Forum (colored in blue). You may also find these links useful:

Big Shoutout!

Hey, I just wanted to take a moment to give a big shout out to everyone here on the forum. I really appreciate your time and assistance throughout the 1+ years that I have been on the forum. The amount of support and quick responses, not to mention clear and concise responses, has been so...

www.mrexcel.com

VBA for smarties

www.snb-vba.eu

Good luck with it.

 

[ssbhatban]

Thanks very much Dear Friend
I did some amount of programming in VB 6 decades back as hoobyist now i have almost forgotten as i have not used it. I thought I will learn from basics properly for programming to excel as i have to use excel regularly now

And to say the least my dear friend, I don't know who you are but you have completely done my job with such a patience & taking all my repeated queries. Thank u once again

 

[mumps]

You are most welcome.

 

[ssbhatban]

Dear Friend

Sorry for disturbing you once again. your code is working very well. i thought whether i can refine it & make it more user friendly. if you could suggest me it would be great.
1. when selecting the files, both files have to be selected at same time. if the files are in different folders then it is not possible to select. is it possible to have an option to select files which are in different folders?
2. as i have attache in the picture below , after merging in the combined file, the cells in columns K L,,M,N are not getting highlighted as in the otter cells . in both original files the cells borders are dark but why it is not the same in the merged file
3. Same thing with the text alignment in the K,L,M,N cells , they are loosing alignment, some are going left &some right

I have attached the modified code below which you had sent me

Sub CopyData()
Application.ScreenUpdating = False
Dim MASTER As Workbook, MARKS As Workbook, combined As Worksheet, y As Long
Dim Arr As Variant, i As Long, srcRng As Range, x As Long, fd As FileDialog, lRow As Long, vSelectedItem As Variant
Set combined = ThisWorkbook.Sheets("combined")
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
.Title = ("Please select two files: '*MASTER' and '*MARKS'.")
.AllowMultiSelect = True
If .Show = -1 Then
If .SelectedItems.Count <> 2 Then
MsgBox ("Please select two files: 'master' and 'marks'.")
Exit Sub
Else
For Each vSelectedItem In .SelectedItems
If vSelectedItem Like "*MASTER*" Or vSelectedItem Like "*MARKS*" Then
y = y + 1
End If
Next vSelectedItem
If y <> 2 Then
MsgBox ("You have selected the wrong file(s)." & Chr(10) & "Please select two files: 'master' and 'marks'.")
Exit Sub
End If
End If
For Each vSelectedItem In .SelectedItems
If vSelectedItem Like "*MASTER*" Then
Set MASTER = Workbooks.Open(vSelectedItem)
ElseIf vSelectedItem Like "*MARKS*" Then
Set MARKS = Workbooks.Open(vSelectedItem)
End If
Next vSelectedItem
End If
If Application.Workbooks.Count <> 3 Then Exit Sub
End With
With MARKS.Sheets("Sheet1")
Arr = .Range("B2", .Range("B" & .Rows.Count).End(xlUp)).Resize(, 6).Value
End With
With combined
.UsedRange.ClearContents
MASTER.Sheets("Sheet1").UsedRange.Cells.Copy .Range("A1")
MARKS.Sheets("Sheet1").Range("B1:G1").Copy .Range("k1")
Set srcRng = .Range("I2", .Range("i" & .Rows.Count).End(xlUp))
For i = LBound(Arr) To UBound(Arr)
If Arr(i, 1) <> "" Then
If Not IsError(Application.Match(Arr(i, 1), srcRng, 0)) Then
x = Application.Match(Arr(i, 1), srcRng, 0)
.Range("k" & x + 1).Resize(, 6).Value = Array(Arr(i, 1), Arr(i, 2), Arr(i, 3), Arr(i, 4), Arr(i, 5), Arr(i, 6))
Else
MARKS.Sheets("Sheet1").Cells(i + 1, 2).Interior.ColorIndex = 3
End If
End If
Next i
End With
MARKS.Close False
MASTER.Close False
Application.ScreenUpdating = True
End Sub

 

[mumps]

It looks like you files now contain additional columns. Please upload the updated files.

 

[ssbhatban]

Yes i have added some more columns in the marks file

 

[ssbhatban]

Dear Friend

find the link for all three files
combined.xlsx.xlsm, EM-102-MASTER.xlsx, EM-102-MARKS-C.xlsx

password for opening the vba project is : 0000