[Solved] Merging multiple workbooks into single sheet in current workbook

by


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

7

solved Merging multiple workbooks into single sheet in current workbook