fixing code merge multiple file into one

[abdelfattah]

hi,
i have about 3 files and the main file to merge data the code gives me error subscript out of range in this line Set ws = Workbooks("merge file").Worksheets("sheet1")
i'm sure about the name's main file

Option Explicit

Private Sub CommandButton1_Click()
Dim Path
Dim File
Dim wb As Workbook
Dim x
Dim lr, lr2
Dim ws As Worksheet
Set ws = Workbooks("merge file").Worksheets("sheet1")
Application.ScreenUpdating = False
With ws.Range("a2:r100000"): .ClearContents
.Activate
End With
Path = ThisWorkbook.Path & "\file\"
File = Dir(Path & "*.xlsx*")
    Do While File <> ""
    Set wb = Workbooks.Open(Path & File)
    If Not IsError(x) Then
             lr2 = wb.Worksheets(1).Cells(Rows.Count, "a").End(xlUp).Row
    wb.Worksheets(1).Range("a2:r" & lr2).Copy
    lr = ws.Range("a" & Rows.Count).End(3).Row + 1
    ws.Range("a" & lr).PasteSpecial xlPasteValues
    Application.CutCopyMode = False
     End If
     wb.Close
          File = Dir
     Loop
     Application.ScreenUpdating = True
End Sub

 

[Fluff]

I cannot open rar files.

 

[abdelfattah]

so i attach 3 files xlsm

 

[abdelfattah]

here it's
d ‫‬.xlsm
v ‫‬.xlsm
merge file.xlsm

 

[Fluff]

Those files are xlsm files, but your code is only opening xlsx files.

 

[abdelfattah]

so this is no way to adjusting and make it more flexible to open xls,xlsm ...etc

 

[Fluff]

Use

File = Dir(Path & "*.xls*")

 

[abdelfattah]

thanks Fluff it works perfectly

 

[Fluff]

You're welcome & thanks for the feedback.