VBA: Loop through files in a folder and perform vlookup on two columns in each file to pull in data to a master file

[CatLadee]

Hi all,

Appreciate your help in advance. I have this code and it's purpose is to loop through files in a folder to open them up. From there, a master worksheet will vlookup values from two columns to pull in the cells values where they match the HASH ID (in both files). How do I setup the vlookup formula to have a variable that represents each worksheet? Also open to suggestions on alternative ways to accomplish the same result - thanks! Lindsay

Sub Consolidate()

    'Excel VBA code to loop through files in a folder with Excel VBA


    Dim MyFolder As String, MyFile As String


    'Opens a file dialog box for user to select a folder
    With Application.FileDialog(msoFileDialogFolderPicker)
       .AllowMultiSelect = False
       .Show
       On Error Resume Next
       MyFolder = .SelectedItems(1)
       Err.Clear
       On Error GoTo 0
    End With
   
 If MyFolder = "" Then End
 

    'stops screen updating, calculations, events, and statsu bar updates to help code run faster
    'you'll be opening and closing many files so this will prevent your screen from displaying that



    Application.ScreenUpdating = False
    Application.DisplayStatusBar = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual



    'This section will loop through and open each file in the folder you selected
    'and then close that file before opening the next file



    MyFile = Dir(MyFolder & "\", vbReadOnly)
    Do While MyFile <> ""
        DoEvents
        On Error GoTo 0
        Workbooks.Open Filename:=MyFolder & "\" & MyFile, UpdateLinks:=False

'        ''''''''''''ENTER YOUR CODE HERE TO DO SOMETHING'''''''''


        Workbooks.Open Filename:="H:\HSI\LEAP\LEAP Project Workbook.xlsm"
            Sheets("Master").Select
            Range("O2").Select
            ActiveCell.FormulaR1C1 = ""
            Range("O2").Select
            ActiveCell.FormulaR1C1 = _
            "=VLOOKUP([@[Hash ID]],'Sheet1'!C1:C16,15,FALSE)"
        
            Range("P2").Select
            ActiveCell.FormulaR1C1 = ""
            Range("P2").Select
            ActiveCell.FormulaR1C1 = _
            "=VLOOKUP([@[Hash ID]],'Sheet1'!C1:C16,16,FALSE)"

      
    
        Workbooks(MyFile).Close SaveChanges:=False
        MyFile = Dir

    Loop
'
'    'turns settings back on that you turned off before looping folders
'
    Application.ScreenUpdating = True
    Application.DisplayStatusBar = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationManual

    End Sub

 

[mumps]

You actually don't need the formula. The macro can perform that action. The macro you posted prompts you to choose a folder. If the folder will always be the same, it's path can be hard coded in the macro so you don't have to choose it. What is the full path to the folder containing the files? What is their extension (xlsx, xlsm)? Are those files the only files in that folder? Perhaps you could upload a copy of your file Master file and a copy of one of your source files to a free site such as www.box.com or www.dropbox.com. Once you do that, mark each file for 'Sharing' and you will be given a link to each file that you can post here. Include a detailed explanation of what you would like to do using a few examples from your data and referring to specific cells, rows, columns and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[CatLadee]

Hi mumps! Thanks a bunch for your help. I just can't get a hang of the loops for the life of me.

Here are some answers to your questions:
1 - If the folder will always be the same, it's path can be hard coded in the macro so you don't have to choose it. - It will be different depending on who is running the tool
2 - What is the full path to the folder containing the files - Currently it is "H:\HSI\LEAP\FY19 Rolled Up Spreadsheets"
3 - What is their extension (xlsx, xlsm)? - they are all .xlsm4 - Are those files the only files in that folder? - the file includes a template file for the spreadsheets called LEAP.xlsm
I sanitized the files and put them on Dropbox - please find the links to the files below. The purpose of the formula that I'm trying to put together will pull in the responses from the spreadsheets that go to the offices into the Master worksheet, matching on the Hash ID. All of the data is in the same location as it is in the real files and the data indicates where is to be pulled in and where. There are 30+ spreadsheets with different Hash IDs representing different offices through the country.

Please let me know if I can provide any further info and thank you again! Lindsay



https://www.dropbox.com/s/5kbooiy7jzzcvik/LEAP Project Workbook.xlsm?dl=0

https://www.dropbox.com/s/yqsph0h4xcgrdqf/LEAP_Location1.xlsm?dl=0

 

[mumps]

Let's use an example from the 2 files you uploaded to clarify what you want to do. The first Hash ID in cell A2 of the Location file is "ADHJLEU". In the Master sheet this Hash ID is located in cell A4647. You want to copy cells O2 and P2 in the Location file and paste them into cells O4647 and P4647 respectively in the Master sheet. You want to do this for every Hash ID in every file in your folder. Is this correct? Are the sequential numbers in columns O and P of any significance?
.