vba - Merging multiple workbooks into single sheet in current workbook

Question

I need VBA code to select multiple workbooks by browsing the files and then merge all those into 1 sheet of current workbook.

All multiple workbooks having only 1 sheet

headers is same for all workbooks so header is constant

Merging should not get any empty rows while filling workbook by workbook

No repetition of headers when merging.

When 1st workbook merging is done, 2nd workbook data should be merged in the same sheet of current workbook exactly next row of the merged 1st workbook data ends which means no empty rows or gaps

Answer 1

First, This will not paste any headers. Since they are all the same, just move the headers to the excel that will store the consolidation. (No need for a macro to do this since it only needs to happen once).

Second, in the code you need to change "SHEETNAME?" to the name of the sheet on your main book that will store the consolidation. (2 instances, do not remove quotes)

Third, I am assuming there is no blanks in Column A which is what determines how far down to copy. If you expect blanks in Column A, this needs to be amended to reflect a column that is least likely to have blanks.

Lastly, I am copying from Column A to Z. Change the "Z" to the last column in your file that contains data.

Given the quality of (or lack of) your question, this is more than you bargained for :)
Happy Editing.

Option Explicit

Sub Consolidation()

Dim CurrentBook As Workbook
Dim WS As Worksheet
Set WS = ThisWorkbook.Sheets("SHEETNAME?")
Dim IndvFiles As FileDialog
Dim FileIdx As Long
Dim i As Integer, x As Integer

Set IndvFiles = Application.FileDialog(msoFileDialogOpen)
With IndvFiles
    .AllowMultiSelect = True
    .Title = "Multi-select target data files:"
    .ButtonName = ""
    .Filters.Clear
    .Filters.Add ".xlsx files", "*.xlsx"
    .Show
End With

Application.DisplayAlerts = False
Application.ScreenUpdating = False

For FileIdx = 1 To IndvFiles.SelectedItems.Count
    Set CurrentBook = Workbooks.Open(IndvFiles.SelectedItems(FileIdx))
    For Each Sheet In CurrentBook.Sheets
        Dim LRow1 As Long
        LRow1 = WS.Range("A" & WS.Rows.Count).End(xlUp).Row
        Dim LRow2 As Long
        LRow2 = CurrentBook.ActiveSheet.Range("A" & CurrentBook.ActiveSheet.Rows.Count).End(xlUp).Row

        Dim ImportRange As Range
        Set ImportRange = CurrentBook.ActiveSheet.Range("A2:Z" & LRow2)
        ImportRange.Copy
        WS.Range("A" & LRow1 + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Next
    CurrentBook.Close False
Next FileIdx

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub