Macro to update information

[EduPAz]

Hi,

Can someone please help me with this:

I have an excel file (A) with this columns: ID, employee, country

I have a folder with 50 excel sheets. Each of these files has a summary workbook with this columns: ID, employee, country.


I need a macro that allows me to fill in the summary tab of each excel file in the folder with the excel file (A). ID column the key identifier.

Thanks,

Edu

 

[mumps]

What is the full path to the folder containing the 50 files? What is the extension of the files (xlsx, xlsm)? Are these files the only files in that folder? What is the name of the sheet containing the ID's in file(A)? You want to copy column A in file(A) to the "Summary" sheet in each of the 50 files. Is this correct?

 

[EduPAz]

Hi,
the full path to the folder containing the 50 files? It's c:master folder
What is the extension of the files (xlsx, xlsm)? It's xlsx
Are these files the only files in that folder? Yes
What is the name of the sheet containing the ID's in file(A)? Salesforce information
You want to copy column A in file(A) to the "Summary" sheet in each of the 50 files. Is this correct? The macro should go in every ID from file (A) and check if there's a file in the folder with the same ID (summary tab). If there's a match, the macro should copy the corresponding information from file (A) into the summary tab of the corresponding file.

 

[mumps]

Try:

Sub updateInfo()
    Application.ScreenUpdating = False
    Dim desWB As Workbook, srcWS As Worksheet
    Dim IDrng As Range, searchRng, ID As Range, rowNum
    Set srcWS = Sheets("Salesforce information")
    Set IDrng = srcWS.Range("A2", srcWS.Range("A" & srcWS.Rows.Count).End(xlUp))
    Const strPath As String = "C:\Master\"
    ChDir strPath
    strExtension = Dir(strPath & "*.xlsx")
    Do While strExtension <> ""
        Set desWB = Workbooks.Open(strPath & strExtension)
        With Sheets("Summary")
            searchRng = .Range("A2", .Range("A" & .Rows.Count).End(xlUp)).Value
            For Each ID In IDrng
                rowNum = Application.Match(ID, searchRng, 0)
                If Not IsError(rowNum) Then
                    ID.Offset(0, 1).Resize(, 2).Copy .Range("B" & rowNum)
                End If
            Next ID
            desWB.Close True
        End With
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True
End Sub