Update master file from different workbooks

Hello,

i will like to know if some one can help me, I have master file (workbook) and 20 workbooks for 20 employes. Alle have the same columns excepte the master have the column (employe name),i want update master file from all 20 workbooks.

Master file

Column 1 = Employes name column 2 = Requisition number column 3 = amount of the requisition

Employe workbooks

column 1 = Requisition number column 2 = amount of the requisition

Originally Posted by alansidman

Is all data to be copied from the Employee Workbooks located on Sheet1?
Is the file name for the 20 employee workbooks EmployeeName.xls?

Hello,

Thanks for your reply.

yes the 20 workbooks have only 1 sheet (sheet 1) and yes for each 20 xls file will be the name of each employee.

thanks again

Originally Posted by alansidman

Try this code out and make sure to change the path in the appropriate line noted.

Sub ABC()
    Dim sPath As String, sName As String
    Dim bk As Workbook, r As Range
    Dim wb As Workbook
    Set wb = Workbooks("Master.xls")
    Dim sh As Worksheet
     
    Application.ScreenUpdating = False
    Set sh = Sheets("Sheet1")
    'Change the next line to reflect the path on your computer.
    sPath = "C:\Documents and Settings\Alan M Sidman\Desktop\Test\"
    sName = Dir(sPath & "*.xls")
    Do While sName <> ""
        Set bk = Workbooks.Open(sPath & sName)
        Dim lr As Long
        lr = Range("A" & Rows.Count).End(xlUp).Row
        Set r = Range("A2:B" & lr)
        Dim lrM As Long
        wb.Activate
        lrM = Range("B" & Rows.Count).End(xlUp).Row
        r.Copy wb.Sheets("Sheet1").Range("B" & lrM + 1)
        wb.Sheets("Sheet1").Range("A" & lrM + 1) = sName
        bk.Close SaveChanges:=False
        sName = Dir()
    Loop
    Application.CutCopyMode = xlCopy
    Application.ScreenUpdating = True
    MsgBox ("Completed")
    
End Sub

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button

Many thanks

i will try this evening,

here is the exemple of two employes name johnsmith.xls and mariesmith.xls where I put this file in the code.

thanks for your patience and your help