Modify code to cycle through all the workbooks in the folder

[03856me]

I have written the following code that extracts several pieces of data from all the worksheets in that file. How can I modify the code (which I can move to a separate workbook, that would cycle through all the workbooks in a specific directory, creating one table of data for all the workbooks. The user needs to be able to select the folder location. Any help if really appreciated.

Option Private Module
Sub MakeTextOutput()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    Dim wb As Workbook:  Set wb = ThisWorkbook
    Dim ws As Worksheet
        Set ws = wb.Sheets("TEXT")
             CopyST_TEXT
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.Goto Cells(ActiveWindow.SplitRow + 1, ActiveWindow.SplitColumn + 1)
End Sub
 
Sub CopyST_TEXT()
    For Each ws In Worksheets
        If ws.Name <>  "TEXT" And ws.Range("E13").Value > "0" Then
    'R13 - Straight Time
            Range("A500").End(xlUp).Offset(1, 0).Value = ws.Range("A1").Value       'Payroll
            Range("B500").End(xlUp).Offset(1, 0).Value = ws.Range("A2").Value       'Emp #
            Range("C500").End(xlUp).Offset(1, 0).Value = ws.Range("A3").Value       'Name 
            Range("D500").End(xlUp).Offset(1, 0).Value = ws.Range("A13").Value      'Div
            Range("E500").End(xlUp).Offset(1, 0).Value = ws.Range("B13").Value      'Dept
            Range("F500").End(xlUp).Offset(1, 0).Value = ws.Range("C13").Value      'CIP
            Range("G500").End(xlUp).Offset(1, 0).Value = ws.Range("D13").Value      'Shift      
            Range("H500").End(xlUp).Offset(1, 0).Value = ws.Range("E13").Value      'Hours  
            Range("I500").End(xlUp).Offset(1, 0).Value = "UE105"                    'Earnings Code
            Range("J500").End(xlUp).Offset(1, 0).Value = ws.Range("L13").Value      'Pay Rate
        End If
    Next ws
End Sub

 

[John_w]

If I can give you a brief pointer, use Application.FileDialog(msoFileDialogFolderPicker) to browse and select a folder, and a Dir function loop to loop through workbooks in the selected folder. Similar to the code here - Importing worksheets from files stored in a folder

 

[03856me]

thank you for the pointers, this helps me learn. I will give this a try.