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

 

[abdelfattah]

every workbook it has sheet1 and contains data from a2:r10000 the same tidy so what you suggest

 

[Fluff]

Do the values for lr & lr2 show what you would expect as you step through the code?

 

[abdelfattah]

i follow by f8 i reaches in theses lines it shows me the value is empty for lr,lr2

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

 

[Fluff]

Are you saying that when you get to this line

ws.Range("a" & lr).PasteSpecial xlPasteValues

the values for both lr & lr2 are shown as empty?

 

[abdelfattah]

yes, that's right

 

[Fluff]

Do you get any error messages?
Also is your code exactly the same as you posted?

 

[abdelfattah]

no error the macro runs normally but no data copys in merge file also this is the same code as in my post

 

[Fluff]

In that case there is something funny going on, because if both those variables are empty you should get an error on this line

wb.Worksheets(1).Range("a2:r" & lr2).Copy

and on this line

ws.Range("a" & lr).PasteSpecial xlPasteValues

 

[abdelfattah]

Fluff give me some times to attach my files and find out where is the problem if you have time

 

[abdelfattah]

here it's
merge.rar