merge multiple sheets for one file into one file and sheet

Hello Hasson,

What you want to achieve, unfortunately, isn't entirely clear but here is a start.

Sub CopySheets()
    ' 282

    Dim FileName    As String
    Dim wsDest      As Worksheet
    Dim Ws          As Worksheet
    Dim Rng         As Range                    ' range to be copied

    With Application.FileDialog(msoFileDialogFilePicker)
        .Title = "Please select a file"
        .AllowMultiSelect = False
        If Not .Show Then Exit Sub

        FileName = .SelectedItems(1)
    End With

    Set wsDest = ThisWorkbook.Worksheets("Summary")
    Application.ScreenUpdating = False

    With Workbooks.Open(FileName)
        For Each Ws In .Worksheets
            ' not clear what you want to check here
            If Not IsEmpty(Ws.Cells(1, 1)) Then
                ' define the range to be copied here
                Set Rng = Ws.Cells(1, 1).CurrentRegion
                Rng.Offset(1).Copy
                With wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1)
                    .PasteSpecial xlPasteValues
                    .PasteSpecial xlPasteFormats
                End With
            End If
        Next Ws
        .Close False
    End With
    Application.ScreenUpdating = True
End Sub

My code largely follows your design but some lines were moved to their proper place in the logical working sequence. You had declared many more variables than are needed. I couldn't see that in your code because they were all bunched up. Perhaps that's the reason you couldn't see it, either.

I didn't understand what you wanted to check with the ISREF function. Therefore my code just checks if A1 is blank. This will be easy to modify if needed.

You said, you want to copy Range(A1:N ). That is insufficient information. In VBA you would start from A1 and look for the last used cell in column A, then go across to column N in that row. Or you would determine the last used row in column N. The difference is important if the column length isn't the same. But if they are of the same length then CurrentRegion will probably do fine. The UsedRange, that you used, can capture columns of different lengths but it will sometimes include blank cells the user deleted but that wouldn't necessarily be a problem because End(xlUp) would overwrite them. In the above code I prepared the Rng variable. You can set it the way best suited to your needs.