vba help to open excel files from a list and capture values from the individual files

Hello jb2009,

Welcome to the Forum!

This macro will examine the workbook the macro is in for the file list. The list is assumed to be "Sheet1" starting with cell "A1". Each workbook will be opened and the contents of cell "A1" on the first sheet in the workbook will be copied into a new workbook starting at cell "A1" on the first sheet. If have any problems, let me know.

Sub CopyDataToMaster()

    Dim Cell As Range
    Dim Data As Variant
    Dim R As Long
    Dim RngEnd As Range
    Dim rngFileList As Range
    Dim rngSummary As Range
    Dim wkbMaster As Workbook
    Dim wkbSummary As Workbook
    Dim wkbSource As Workbook
    
        Set wkbMaster = ThisWorkbook
        
        Set rngFileList = wkbMaster.Worksheets("Sheet1").Range("A1")
        
            Set RngEnd = wkbMaster.Cells(Rows.Count, rngFileList.Column).End(xlUp)
            If RngEnd.Row < rngFileList.Row Then Exit Sub Else Set rngFileList = wkbMaster.Range(rngFileList, RngEnd)
            
            Application.ScreenUpdating = False
            
                Set wkbSummary = Workbooks.Add(xlWBATWorksheet)
                Set rngSummary = wkbSummary.Sheet1.Range("A1")
            
                For Each Cell In rngFileList
                    Set wkbSource = Workbooks.Open(Filename:=Cell.Value, ReadOnly:=True)
                        Data = wkbSource.Sheet1.Range("A1")
                    wkbSource.Close SaveChanges:=False
                    rngSummary.Offset(R, 0).Value = Data
                    R = R + 1
                Next Cell
                
            Application.ScreenUpdating = True
            
End Sub