Copy A Worksheet in Directory & Subdirectory

[termeric]

Hello, i've been using this code for a while to open all the excel files in a directory and copy a worksheet so that i can aggregate information into a single space. I'd like to modify this to look in directories and subdirectories. is there an easy way to do that with out starting over from scrathc?

Sub Merge2MultiSheets()


'puts all the data from all the sheets on one worksheet

    Dim xRg As Range, zRg As Range
    Dim xSelItem As Variant
    Dim xFileDlg As FileDialog
    Dim xFileName, xSheetName, xRgStr As String
    Dim xBook, xWorkBook As Workbook
    Dim xSheet As Worksheet
    On Error Resume Next
    

    
'*** This is where the input range is defined ***'
    xSheetName = "Sheet1"
    xRgStr = "A1:H70"

    
    Set xFileDlg = Application.FileDialog(msoFileDialogFolderPicker)
    With xFileDlg
        If .Show = -1 Then
            xSelItem = .SelectedItems.Item(1)
            Set xWorkBook = ThisWorkbook
            Set xSheet = xWorkBook.Sheets("New Sheet")
            If xSheet Is Nothing Then
                xWorkBook.Sheets.Add(after:=xWorkBook.Worksheets(xWorkBook.Worksheets.Count)).Name = "New Sheet"
                Set xSheet = xWorkBook.Sheets("New Sheet")
            End If
            

            xFileName = Dir(xSelItem & "\*.xlsx", vbNormal) 'vbnormal
            If xFileName = "" Then Exit Sub
            Do Until xFileName = ""
                
                Sheets("New Sheet").Cells.Delete Shift:=xlUp
                Sheets("New Sheet 2").Cells.Delete Shift:=xlUp
            
                Set xBook = Workbooks.Open(xSelItem & "\" & xFileName)
                Set xRg = xBook.Worksheets(xSheetName).Range(xRgStr)
                xRg.Copy xSheet.Range("A65536").End(xlUp).Offset(0, 0)
                Debug.Print xFileName

                
                xFileName = Dir()
                xBook.Close
                

                
            Loop
        End If
    End With
    
    
End Sub
[\code]

 

[Fluff]

If you are just interested in going one level down from the selected folder, then you can use something like VBA Loop Through all Files in subfolders – VBAF1

Otherwise you will need to use a recursive loop through the folders.

 

[termeric]

Thanks Fluff, i appreciate the help with all my VBA problems

 

[Fluff]

You're welcome & thanks for the feedback.