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
Bookmarks