Copying Data from one Execl to another Matching Headers.

  • Yes, I put them all in the same folder. Master file and the two source files. I am using excel 2013, is that a problem? what if i can name all the excel in a sheet so the macro can pick it up from there, will that help>?

  • Have you actually saved the Master to the folder or did you just copy it into the folder? Open the Master and save it. Now try the macro. If that still doesn't work, what is the full path to the folder?

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • I saved the master file and tried, it doesn't still work. I don't think its the path because i changed the name of the file and ran the macro and it picked up the name properly. I think whatever it does after picking up the path and the file seems to be the issue. :( my actual path is C:\Users\D\Desktop\DProJect\TheFolder

  • To be honest, I'm at a loss to see a solution. The macro works properly for me. Perhaps if you start a new thread, attach the 3 files and explain the problem, another Forum member may be able to isolate the problem and find a solution. :(

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • I may have found the problem. Try:

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Try:

    Code
    Sub CopyRange()     Application.ScreenUpdating = False     Dim wsDest As Worksheet, wkbSource As Workbook, strExtension As String, LastRow As Long, LastRow2 As Long, x As Long: x = 2     Set wsDest = ThisWorkbook.Sheets("Sheet1")     Dim arr2 As Variant, arr3 As Variant, i As Long, header1 As Range, header2 As Range     arr2 = Array("Name", "ID", "Date of Birth", "Place of Work", "School", "Education Level", "Year Leaving School", "Highest Education", _             "Education Expecting to Complete Next", "Marital Status", "Ambition", "A Reference", "L Reference")     arr3 = Array("Name", "ID", "Date of Birth", "Place of Work", "Highest Education", "Ambition", "A Reference", "L Reference")     strExtension = Dir(ThisWorkbook.Path & Application.PathSeparator & "*.xlsx")     Do While strExtension  ""         Set wkbSource = Workbooks.Open(ThisWorkbook.Path & Application.PathSeparator & strExtension)         If Left(Right(strExtension, 6), 1) = 2 Then             With Sheets("Sheet1").Rows(1)                 .Replace "First Name", "Name"                 .Replace "Identification", "ID"                 .Replace "DOB", "Date of Birth"                 .Replace "Work", "Place of Work"                 .Replace "Edu", "School"                 .Replace "Education Level", "Highest Education"                 .Replace "Qualification", "Education Level"                 .Replace "School Year", "Year Leaving School"                 .Replace "What do you want to do", "Education Expecting to Complete Next"                 .Replace "Status", "Marital Status"                 .Replace "Vision", "Ambition"             End With             With Sheets("Sheet1")                 LastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row                 LastRow2 = wsDest.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row                 For i = LBound(arr2) To UBound(arr2)                     Set header1 = .Rows(1).Find(arr2(i), LookIn:=xlValues, lookat:=xlWhole)                     Set header2 = wsDest.Rows(1).Find(arr2(i), LookIn:=xlValues, lookat:=xlWhole)                     Range(.Cells(2, header1.Column), .Cells(LastRow, header1.Column)).Copy wsDest.Cells(LastRow2 + 1, header2.Column).End(xlUp).Offset(1, 0)                 Next i             End With         ElseIf Left(Right(strExtension, 6), 1) = 3 Then             With Sheets("Sheet1").Rows(1)                 .Replace "Full Name", "Name"                 .Replace "Identification", "ID"                 .Replace "DOB", "Date of Birth"                 .Replace "Work PLC", "Place of Work"                 .Replace "Education Stats", "Highest Education"                 .Replace "What Next", "Ambition"             End With             With Sheets("Sheet1")                 LastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row                 LastRow2 = wsDest.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row                 For i = LBound(arr3) To UBound(arr3)                     Set header1 = .Rows(1).Find(arr3(i), LookIn:=xlValues, lookat:=xlWhole)                     Set header2 = wsDest.Rows(1).Find(arr3(i), LookIn:=xlValues, lookat:=xlWhole)                     Range(.Cells(2, header1.Column), .Cells(LastRow, header1.Column)).Copy wsDest.Cells(LastRow2 + 1, header2.Column)                 Next i             End With         End If         wkbSource.Close False         strExtension = Dir     Loop     Application.ScreenUpdating = True End Sub

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Hye I figured and added a few columns and it works perfectly. Would you know how i can reference a coulmn dynamically using macro? My Folder path looks like


    ---- A ----
    _ _|_ _
    C B
    |_ _ D


    B and C are inside A and D is inside B, how do I reference a file inside Folder B from Folder D? Dynamically without giving the absolute path?

  • What do you mean by:

    Quote

    how I can reference a column dynamically


    The only way I can think of referencing the folder without using the full path, is if the workbook containing the macro is saved in the same folder. Then you can use "ThisWorkbook.path".

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!