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]

Try:

Sub CopyData()
    Application.ScreenUpdating = False
    Dim MASTER As Workbook, MARKS As Workbook, combined As Worksheet
    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 the 'master' file.")
        .AllowMultiSelect = False
        If .Show = -1 Then
            For Each vSelectedItem In .SelectedItems
                If Not vSelectedItem Like "*MASTER*" Then
                    MsgBox ("You have not selected the master file." & Chr(10) & "Please try again and select the 'master' file.")
                    Exit Sub
                Else
                    Set MASTER = Workbooks.Open(vSelectedItem)
                End If
            Next vSelectedItem
        End If
    End With
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
        .Title = ("Please the 'marks' file.")
        .AllowMultiSelect = False
        If .Show = -1 Then
            For Each vSelectedItem In .SelectedItems
                If Not vSelectedItem Like "*MARKS*" Then
                    MsgBox ("You have not selected the marks file." & Chr(10) & "Please try again and select the 'marks' file.")
                    Exit Sub
                Else
                    Set MARKS = Workbooks.Open(vSelectedItem)
                End If
            Next vSelectedItem
        End If
    End With
    With MARKS.Sheets("Sheet1")
        Arr = .Range("B2", .Range("B" & .Rows.Count).End(xlUp)).Resize(, 6).Value
        .Range("B2", .Range("B" & .Rows.Count).End(xlUp)).Interior.ColorIndex = xlNone
    End With
    With combined
        .UsedRange.Offset(1).ClearContents
        lRow = MASTER.Sheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Row
        MASTER.Sheets("Sheet1").Range("A2:A" & lRow).Resize(, 10).Copy .Range("A1")
        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
    Application.DisplayAlerts = False
    MARKS.Close True
    MASTER.Close False
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

 

[ssbhatban]

Dear friend

Sorry for the late reply,
It is working extremely fine. very useful for me
but i fail to understand why those columns were not sorting & aligning properly & cells borders were disappearing even though it was fine in the original file, when i imort secind file in the earlier vesion. can u clarify on that

thank you once again

 

[mumps]

You are very welcome. I'm not sure why you were experiencing the problems.