select folder istead of select each file to pull data

Hi Maklal

If you want to pick individual files in a chosen folder, you can the AllowMultiSelect property of FileDialog. The Microsoft guidance on that is here: FileDialog.AllowMultiSelect property (Office)

To get all files in a folder, you can use the Folder picker instead the use VBA's DIR function to pick the first Excel file (with the chosen folder path plus a wildcard string), process that then use Dir() to get VBA to look for the next matching file in that directory.

In the modified code below, I've made changes in bold and added comments so you see what's happening. Note that I've just searched for .xls* files since you're looking for the first sheet of each in the existing code like your file (and it will fail is there is no such sheet) even though your code above looks for the 5th sheet via Set OUTrng = .Sheets(5) :

Sub LoopFolder()

Dim lRw As Long
Dim OUTrng As Range
Dim wbNm As String


'get user to pick a folder
With Application.FileDialog(msoFileDialogFolderPicker)
   ' If okay was pressed
   If .Show = -1 Then
    
    'get first file with wildcard match
    wbNm = Dir(.SelectedItems(1) & "\*.xls*", vbNormal)
    ' loop while there's another matching file
    Do While wbNm <> ""
        With GetObject(.SelectedItems(1) & "\" & wbNm)
            ' get last row number in column B
            lRw = ThisWorkbook.Sheets("sheet1").Cells(Rows.Count, 2).End(xlUp).Row
            Set OUTrng = .Sheets(1).Range("g2:i1000")
            With OUTrng
                Set OUTrng = OUTrng.Resize(.Rows.Count, .Columns.Count + 1)
            End With
            Application.DisplayAlerts = False
            With OUTrng
                .Resize(.Rows.Count, .Columns.Count - 1).SpecialCells(xlCellTypeVisible).Copy
            End With
            ' close file without saving
            .Close 0
             Application.DisplayAlerts = True
            ' paste to bottom of row B but don't use helper column
            ThisWorkbook.Sheets("sheet1").Cells(lRw + 1, 2).PasteSpecial
        End With

        ' get next matching file
        wbNm = Dir()
    Loop
   End If
End With

Hope this works for you.