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]

Try adding the file extension to the workbook name.

 

[abdelfattah]

hi, Fluffs
if you mean this i did it

Set ws = Workbooks("merge file").Worksheets("sheet1").xlsm

not succeed

 

[Fluff]

It needs to be

Set ws = Workbooks("merge file.xlsm").Worksheets("sheet1")

 

[abdelfattah]

it seems code works but no data in sheet1 from multiple files, do you have any idea?

 

[Fluff]

If you step through the code using F8, does it open any other files?

 

[abdelfattah]

yes, now i closed all of opened files except merge file but still doesn't show any data in merge file

 

[Fluff]

As you step through the code check what the values for lr2 & lr are, to make sure that you are
a) actually copying data.
b) pasting it where you would expect.

 

[abdelfattah]

it gives me empty what that means

 

[Fluff]

As you step through the code the values of lr & lr2 will change as they are assigned values.
Every time through the loop check the values are what you would expect.