Combine mutiple xlsx into one master sheet

Need to combine multiple xls from a specific location. All xlsx's are the same format meaning all have the exact columns headings for A-N. Would like to combine each sheet into a new workbook with 1 master sheet of the data. 1st row should have the common colun headings and all the data combined in the rows below without breaks or blanks.

Also if possible take the file name and input that into column N for each entry(preferably without the file extension.
I currently have a VBA code that allows me to make one workbook with multiple sheets ,but not a master book with 1 master sheet.
You can work with my current code(request it) or provide new vba code. Any help would be appreciated

Copy of the current code


'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\"

'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


would like to implement something like the following:

With .Worksheets("CalOptima Sept Template")
numrows = .Cells(.Rows.Count, "A").End(xlUp).Row
If nextrow = 1 Then
.Rows(1).Resize(numrows).Copy wbTarget.Worksheets(1).Cells(nextrow, "A")
Else
.Rows(2).Resize(numrows - 1).Copy wbTarget.Worksheets(1).Cells(nextrow, "A")
End If
nextrow = nextrow + numrows
End With

Reposting the code

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\"

'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

would like to implement something like the following:

With .Worksheets("CalOptima Sept Template")
numrows = .Cells(.Rows.Count, "A").End(xlUp).Row
If nextrow = 1 Then
.Rows(1).Resize(numrows).Copy wbTarget.Worksheets(1).Cells(nextrow, "A")
Else
.Rows(2).Resize(numrows - 1).Copy wbTarget.Worksheets(1).Cells(nextrow, "A")
End If
nextrow = nextrow + numrows
End With

The following code is slightly working however it's not merging all active sheets

Any Ideas?

Sub CopyRangeFromMultiWorksheets()
    Dim sh As Worksheet
    Dim DestSh As Worksheet
    Dim Last As Long
    Dim CopyRng As Range

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    'Delete the sheet "ScanStatus" if it exist
    Application.DisplayAlerts = False
    On Error Resume Next
    ActiveWorkbook.Worksheets("ScanStatus").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True

    'Add a worksheet with the name "ScanStatus"
    Set DestSh = ActiveWorkbook.Worksheets.Add
    DestSh.Name = "ScanStatus"

    'loop through all worksheets and copy the data to the DestSh
    For Each sh In ActiveWorkbook.Worksheets
        If sh.Name <> DestSh.Name Then

            'Find the last row with data on the DestSh
            Last = (M1)

            'Fill in the range that you want to copy
            Set CopyRng = sh.Range("A1").CurrentRegion

            'Test if there enough rows in the DestSh to copy all the data
            If Last + CopyRng.Rows.Count > DestSh.Rows.Count Then
                MsgBox "There are not enough rows in the Destsh"
                GoTo ExitTheSub
            End If

            'This example copies values/formats, if you only want to copy the
            'values or want to copy everything look at the example below this macro
        CopyRng.Copy DestSh.Cells(Last + 1, "A")

            'Optional: This will copy the sheet name in the H column
            DestSh.Cells(Last + 1, "N").Resize(CopyRng.Rows.Count).Value = sh.Name

        End If
    Next

ExitTheSub:

    Application.Goto DestSh.Cells(1)

    'AutoFit the column width in the DestSh sheet
    DestSh.Columns.AutoFit

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    
    
Exit Sub
  
        
            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()

Do


    Loop
    WorkbookDestination.Worksheets(1).Delete
    
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    
End Sub