Hi everyone!

I am having a problem with the code below. I have above 1000 excel files, all of which are workbooks with one sheet. I am trying to copy data from these workbooks and paste them one after another in one new workbook in the same worksheet one after the other.

I wrote the following code but it doesn't execute what I want.

The data I am copying is a range of 97 rows and 5 columns from A to F. I want to copy these data into a worksheet one after the other, in a descending order.

Would anyone be able to take a look at this code?

Thank you in advance!

Option Explicit

Sub mergingfiles()

Dim mergedfile As Workbook
Dim filestomerge As Workbook
Dim selectedfile As String
Dim selection As Integer
Dim datarows As Integer

With Application.FileDialog(msoFileDialogOpen)
    .AllowMultiSelect = True
    .Show
    
    Set mergedfile = ActiveWorkbook
    
    For selection = 1 To .SelectedItems.Count
        selectedfile = .SelectedItems.Item(selection)
        If Right(selectedfile, 4) = ".xls" Then
            Workbooks.Open selectedfile, 0, True
            
                Set filestomerge = ActiveWorkbook
                datarows = mergedfile.Sheets(1).Range("A2:E97").Rows.Count
                filestomerge.Sheets(1).Range("A2:E97").Copy Destination:=mergedfile.Sheets(1).Range("A" & datarows).Offset(1, 0)

                mergedfile.Close SaveChanges:=False
        End If
    Next selection
End With
Set mergedfile = Nothing
Set filestomerge = Nothing

With Application
    .ScreenUpdating = True
    .EnableEvents = True
         
End With
                
End Sub