Meger multiple file but header not same

[Admin]

Hi

Try

Option Explicit

Sub ConsolidateWorkbooks()
    
    Dim j           As Long
    Dim Fldr        As String
    Dim Fname       As String
    Dim wbkActive   As Workbook
    Dim wbkSource   As Workbook
    Dim Dest        As Range
    
    '// User settings
    Const SourceFileType        As String = "xls*"  'xls,xlsx,xlsb,xlsm
    Const DestinationSheet      As String = "Sheet1"
    Const DestStartCell         As String = "A1"
    Const StartRow              As Long = 2
    '// End
    
    Application.ScreenUpdating = False
    With Application.FileDialog(4)
        .Title = "Select source file folder"
        .AllowMultiSelect = False
        If .Show = -1 Then
            Fldr = .SelectedItems(1)
        Else
            GoTo Xit
        End If
    End With
    
    Set wbkActive = ThisWorkbook
    
    Set Dest = wbkActive.Worksheets(DestinationSheet).Range(DestStartCell)
    
    Fname = Dir(Fldr & "\*." & SourceFileType)
    
    Do While Len(Fname)
        If wbkActive.Name <> Fname Then
            Set wbkSource = Workbooks.Open(Fldr & "\" & Fname)
            With wbkSource.Worksheets(1)
                j = .Range("a" & .Rows.Count).End(3).Row
                .Range("a2:as" & j).Copy Dest
                Set Dest = Dest.Offset(j)
            End With
            wbkSource.Close 0
            Set wbkSource = Nothing
        End If
        Fname = Dir()
    Loop
    
Xit:
    Application.ScreenUpdating = True
    
End Sub

[rocky]

Thanks buddy, now it is working....

[rocky]

Hi

I have run this macro and it is working fine. But there is blank row coming in consolidated sheet after sheet data. For example one workbook data is pasted in consolidated sheet and then one row left blank. Then second workbook data is pasted and then one row blank. There thirty row leave blank because thirty file to consolidate.

Seondly the formating of sheet is not proper way. Is there any way to consolidate the data as value so that cell size does not change.

Thanks buddy, now it is working....