Excel VBA Code to combine multiple Excel files

[Tommy1370]

The following code opens a dialog box for the user to select files. I want it to determine which files to consolidate (using the loop).

'The following code will combine all data into one excel workbook.
Sub CombineFiles_Step1()
'Declare Variables
Dim WorkbookDestination As Workbook
Dim WorkbookSource As Workbook
Dim WorksheetSource As Worksheet
Dim FolderLocation As String
Dim strFilename As String
    
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    
    'This line will need to be modified depending on location of source folder
    FolderLocation = "C:\Users\Location"
    
    'Set the current directory to the the folder path.
    ChDrive FolderLocation
    ChDir FolderLocation
    
    'Dialog box to determine which files to use. Use ctrl+a to select all files in folder.
    SelectedFiles = Application.GetOpenFilename( _
        filefilter:="Excel Files (*.xls*), *.xls*", MultiSelect:=True)
    
    'Create a new workbook
    Set WorkbookDestination = Workbooks.Add(xlWBATWorksheet)
    strFilename = Dir(FolderLocation & "\*.xls", vbNormal)
    
    'Iterate for each file in folder
    If Len(strFilename) = 0 Then Exit Sub
    
    
    Do Until strFilename = ""
        
            Set WorkbookSource = Workbooks.Open(FileName:=FolderLocation & "\" & strFilename)
            Set WorksheetSource = WorkbookSource.Worksheets(1)
            WorksheetSource.Copy After:=WorkbookDestination.Worksheets(WorkbookDestination.Worksheets.Count)
            WorkbookSource.Close False
        strFilename = Dir()
        
    Loop
    WorkbookDestination.Worksheets(1).Delete
    
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    
End Sub

The problems:

1. If I click 'Cancel' in the dialog box it still runs the loop.
2. No matter which files I select it runs the program on all of the files in the folder.

Thanks!

 

[sur]

Good One,

But i need to merge the selected files in the folder in to one sheet (where the headers are same in row A).

& save it in the same folder with Name "Master".