Merge multiple workbooks

[bmrsalgas]

Need help with vba code that should merge multiple workbooks into one workbook from a folder that the user chooses, but it should only merge the 1st sheet of each workbook.

I found this code only it does half of what I need but I'm clueless about the rest.

Scheme for macro working:

1st - Ask user the location folder of all workbooks to merge

2nd - merge the first sheet of all of the workbooks on the folder into to the MAIN workbook (where I run the macro with a cute button)


The code that I found online.

[COLOR=#101094][FONT=inherit]Option[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Explicit[/FONT][/COLOR]<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">[COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Sub[/FONT][/COLOR][COLOR=#303336][FONT=inherit] GetSheets[/FONT][/COLOR][COLOR=#303336][FONT=inherit]()[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Dim[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Path [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]String[/FONT][/COLOR][COLOR=#303336][FONT=inherit],[/FONT][/COLOR][COLOR=#303336][FONT=inherit] fileName [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]String[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Dim[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Sht [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Worksheet

    Path [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"\Users\myname\Documenten\Test\"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    fileName [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Dir[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]Path [/FONT][/COLOR][COLOR=#303336][FONT=inherit]&[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"*.xls"[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Do[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]While[/FONT][/COLOR][COLOR=#303336][FONT=inherit] fileName [/FONT][/COLOR][COLOR=#303336][FONT=inherit]<>[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]""[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
        Workbooks[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Open fileName[/FONT][/COLOR][COLOR=#303336][FONT=inherit]:=[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Path [/FONT][/COLOR][COLOR=#303336][FONT=inherit]&[/FONT][/COLOR][COLOR=#303336][FONT=inherit] fileName[/FONT][/COLOR][COLOR=#303336][FONT=inherit],[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]ReadOnly[/FONT][/COLOR][COLOR=#303336][FONT=inherit]:=[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]True[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
        [/FONT][/COLOR][COLOR=#101094][FONT=inherit]With[/FONT][/COLOR][COLOR=#303336][FONT=inherit] ActiveWorkbook
            [/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Worksheets[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]1[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Copy After[/FONT][/COLOR][COLOR=#303336][FONT=inherit]:=[/FONT][/COLOR][COLOR=#303336][FONT=inherit]ThisWorkbook[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Sheets[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]1[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
            ThisWorkbook[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Sheets[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]2[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]name [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]name
        [/FONT][/COLOR][COLOR=#101094][FONT=inherit]End[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]With[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
        ActiveWorkbook[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Close
        fileName [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Dir[/FONT][/COLOR][COLOR=#303336][FONT=inherit]()[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Loop[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR]</code>[COLOR=#101094][FONT=inherit]End[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Sub[/FONT][/COLOR]

 

[bsquad]

replace this:

        Open_iWorkbook = Application.GetOpenFilename(filefilter:="Excel Workbooks(*.xlsx; *xls[COLOR=#ff0000]m[/COLOR], *.xlsx; *xls[COLOR=#ff0000]m[/COLOR]", _
                        Title:="Import File Select", MultiSelect:=True)

With this:

        Open_iWorkbook = Application.GetOpenFilename(filefilter:="Excel Workbooks(*.xlsx; *[COLOR=#0000ff]xls[/COLOR], *.xlsx; *[COLOR=#0000ff]xls[/COLOR]", _
                        Title:="Import File Select", MultiSelect:=True)

 

[bmrsalgas]

Hi.

Now I know what is really needed for this macro.

I attached the example file.

So...

1- The user opens the summary file and runs the macro, where he chooses the folder with all of the files to be used;

2- The macro import from each file all of the non blank lines, and excludes the header from each file (it's the same size has the summary), in some files it may be necessary to unhide columns.

3- All of the data is consolidated in the sheet named "RAW DATA"

4- Nothing

Is this possible?


Link to the file

 

[bsquad]

lol, if you are being serious >> that is the exact code I posted for you a while back

let me know if you are having trouble using it

Sub Aggregation()
    Dim MasterWorkbook As Workbook
        Set MasterWorkbook = ThisWorkbook
    Dim iWorkbook As Workbook
    Dim Open_iWorkbook As Variant
    Dim x As Long
    Dim y As Long
    Dim x1 As Long
    Dim y1 As Long
        Application.DisplayAlerts = False
        Application.AskToUpdateLinks = False
        Application.ScreenUpdating = False
        ChDir ActiveWorkbook.Path
        Open_iWorkbook = Application.GetOpenFilename(filefilter:="Excel Workbooks(*.xlsx; *xls, *.xlsx; *xls", _
                        Title:="Import File Select", MultiSelect:=True)
                        On Error Resume Next
            For x = LBound(Open_iWorkbook) To UBound(Open_iWorkbook)
                Set iWorkbook = Workbooks.Open(Open_iWorkbook(x))
                    x1 = iWorkbook.Worksheets(1).Range("A1").SpecialCells(xlCellTypeLastCell).Row
                    y1 = iWorkbook.Worksheets(1).Range("A1").SpecialCells(xlCellTypeLastCell).Column
                    With iWorkbook.Worksheets(1)
                        .Range(Cells(1, 1), Cells(x1, y1)).Copy
                    End With
                    With MasterWorkbook.Worksheets(1)
                        y = Cells(Rows.Count, 1).End(xlUp).Row + 1
                        .Cells(y, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, _
                                    Transpose:=False
                        .Cells(y, 1).PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, SkipBlanks:=False, _
                                    Transpose:=False
                        .Cells(y, 1).pastespeical Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, _
                                    Transpose:=False
                    End With
                    Application.CutCopyMode = False
                    iWorkbook.Close savechanges:=False
            Next x
        Application.DisplayAlerts = True
        Application.AskToUpdateLinks = True
        Application.ScreenUpdating = True
End Sub