Macro to combine multiple worksheets into a summary workbook without merging the worksheets

[treyabrown]

I am trying to figure out how to combine 2 macros. The first macro allows me to select the file path I need, and the second macro will use the file path from the first macro and pull the worksheets from all files in the file path and combine them into 1 summary workbook.

What is the best way to combine the 2 macros, OR call the variable from the first macro?

The first macro:
Sub GetFolder()
Set myFolder = Application.FileDialog(msoFileDialogFolderPicker)
With myFolder
.Title = "Choose Folder"
.AllowMultiSelect = False
If .Show <> -1 Then
Exit Sub
End If
FolderSelected = .SelectedItems(1)
End With
MsgBox "You Selected:" & FolderSelected
End Sub

Second macro:
Sub GetSheets()
Path = "Y:\MC+ Data Dumps" & "\"
Filename = Dir(Path & "*.xls")
Do While Filename <> ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Copy After:=ThisWorkbook.Sheets(1)
Next Sheet
Workbooks(Filename).Close
Filename = Dir()
Loop
End Sub

 

[AlphaFrog]

Try this...

[color=darkblue]Sub[/color] GetSheets()
    
    [color=darkblue]Dim[/color] strPath [color=darkblue]As[/color] [color=darkblue]String[/color], Filename [color=darkblue]As[/color] [color=darkblue]String[/color], ws [color=darkblue]As[/color] Worksheet, counter [color=darkblue]As[/color] [color=darkblue]Long[/color]
    
    [color=darkblue]With[/color] Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Choose Folder"
        .AllowMultiSelect = [color=darkblue]False[/color]
        [color=darkblue]If[/color] .Show <> -1 [color=darkblue]Then[/color] [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
        strPath = .SelectedItems(1) & "\"
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    
    Application.ScreenUpdating = [color=darkblue]False[/color]
    Filename = Dir(strPath & "*.xls")
    [color=darkblue]Do[/color] [color=darkblue]While[/color] Filename <> ""
        [color=darkblue]With[/color] Workbooks.Open(Filename:=strPath & Filename, ReadOnly:=True)
            counter = counter + 1
            [color=darkblue]For[/color] [color=darkblue]Each[/color] ws [color=darkblue]In[/color] .Worksheets
                ws.Copy After:=ThisWorkbook.Sheets(1)
            [color=darkblue]Next[/color] ws
            ActiveWorkbook.Close SaveChanges:=[color=darkblue]False[/color]
        [color=darkblue]End[/color] [color=darkblue]With[/color]
        Filename = Dir
    [color=darkblue]Loop[/color]
    Application.ScreenUpdating = [color=darkblue]True[/color]
    
    MsgBox counter & " files merged. ", vbInformation, "File Merge Complete"
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]