Vlookup from multiple files

[beedee3]

I have a spreadsheet in which I need to get data from various other spreadsheets using VLOOKUP. Here's what I have and what I am supposed to do:
(1) I have 25 spreadsheets, each containing information for Total students
(2) I have to create a master spreadsheet where I write the Total students from each of the 25 files along with the filenames.

For example:
ABC_School.xlsx has "Total Students" text in Column A and # Students (say 100) in Column B
XYZ_School.xlsx has "Total Students" text in Column A and # Students (say 200) in Column B

In my master spreadsheet, I want file name (ABC_Students.xlsx) in Column A and Total Students in Column B. This should be done for all 25 files.

Can anyone please help? I have done this using formulas and it works, but I am looking for a macro because I'll be doing similar stuff for 100's of files.

[GC Excel]

Hi,
Try this code.
Note : Adapt the range for the total # of students (here I assumed it is in cell B1)
Run the macro and select the files to compile

Sub MergeFiles()
   Dim wb As Workbook
   Dim wsM As Worksheet, ws As Worksheet
   Dim vFiles As Variant
   Dim k As Integer

   ' // Get files to merge
   vFiles = Choose_Files("Select files to merge : ")
   If Not IsArray(vFiles) Then
      MsgBox "Error! You must at least select one file."
      Exit Sub
   End If
   On Error Resume Next

   Application.ScreenUpdating = False
   Set wsM = ThisWorkbook.Sheets(1)
   
   ' // Loop through all files, copy to master
   For k = 1 To UBound(vFiles)
      ' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      Set wb = Workbooks.Open(vFiles(k))
      Set ws = wb.Sheets(1)
      wsM.Cells(k, 1) = wb.Name
      wsM.Cells(k, 2) = wb.Sheets(1).Range("B1")    ' # of students
      wb.Close              'Close the file
      Set wb = Nothing
      ' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   Next k
   
   Application.ScreenUpdating = True

End Sub

Function Choose_Files(sTitle As String) As Variant
   Dim sFilter As String, bMultiSelect As Boolean

   sFilter = "Excel files (.xls)(.xlsx)(.xlsm), *.xls*"
   bMultiSelect = True   'Can select multiples files at once
   Choose_Files = Application.GetOpenFilename(Filefilter:=sFilter, Title:=sTitle, MultiSelect:=bMultiSelect)
End Function