Copy data from multiple workbook to Master file

[Ramya A]

Hi,

I am looking for a tracker for my team to capture the data. there are almost 20 people in my team so sharing a workbook will not be ideal one so i am thinking to create a tracker for them. Each individual will have a separate file to update the their data, all individual files and the master file will be saved in the same folder.

Column D (Plan No.) will be unique value.

Any help on this is greatly appreciated

 

[herofox]

hello try this

Sub CopyRange()
    Application.ScreenUpdating = False
    Dim wkbDest As Workbook
    Dim wkbSource As Workbook
    Set wkbDest = ThisWorkbook
    Dim LastRow As Long
    Const strPath As String = " C:\Users\xbv\Desktop\group1\"
    ChDir strPath
    strExtension = Dir("*.xls*")
    Do While strExtension <> ""
        Set wkbSource = Workbooks.Open(strPath & strExtension)
        With wkbSource
            LastRow = .Sheets("appendix B").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            .Sheets("appendix B").Range("C6:F" & LastRow).Copy wkbDest.Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
            .Close savechanges:=False
        End With
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

from this site
VBA to copy data from multiple workbooks into master sheet
and this another Solution
Transfer Data Multiple Workbooks Master Workbook Automatically
elso you can to see this web
Copy data from Multiple workbooks into One
and this one elso
How To Combine Multiple Workbooks Into One Master Workbook In Excel?
and elso another Code You Can Use It

Sub datatransfer()
'https://stackoverflow.com/questions/36335281/copying-worksheet-data-from-multiple-workbooks-and-pasting-it-into-a-master-data
    'have to specify type for all variables, techinically it still works the way you did, but you are setting unncessary memory
    Dim FolderPath As String, FilePath As String, Filename As String, targetfile As String
    Dim wb1 As Workbook, wb2 As Workbook
    targetfile = "Left the location out on purpose"
    FolderPath = " Left the location out on purpose "
    FilePath = FolderPath & "*.xls*"
    Set wb2 = Workbooks.Open(targetfile) 'only need to open this once and leave open until execution is finished
    Filename = Dir(FilePath)
    Do While Filename <> "" ' need "<>" to say not equal to nothing
        wb2.Worksheets.Add After:=wb2.Worksheets(wb2.Worksheets.Count) 'add new sheet to paste data in target book
        Set wb1 = Workbooks.Open(FolderPath & Filename)
        Dim lastrow As Long, lastcolumn As Long
        With wb1.Worksheets(1) 'best to qualify all objects and work directly with them
            lastrow = .Cells(.Rows.Count, 1).End(xlUp).Row
            lastcolumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
            'pretty sure you want to add this A1, since it's a new blank sheet
            .Range(.Cells(2, 1), .Cells(lastrow, lastcolumn)).Copy _
                Destination:=wb2.Worksheets(wb2.Worksheets.Count).Range("A1")
        End With
        wb1.Close False 'assume no need to save changes to workbook you copied data from
        Filename = Dir
    Loop
    wb2.Close True 'no close and save master file
End Sub

 

[Ramya A]

Thank you so much for the quick response. I am afraid i cant make edits to coding.

I dont want to copy the data directly. I want the to master file look up for the values based on the column D(Plan No.)