VBA for Combining 1st sheet of every workbook in folder into one master-sheet of new workbook

[Nick G]

Greetings,

I will get directly to the point.

*What I Have.

1)-I have one folder which contains multiple excel workbooks. All of them are in .xlsx format.
2) Each workbooks has multiple worksheets
3)The important sheet for me is the first one of each workbook, which has a dynamic name i.e. the name of the sheet is not same in any workbook nor there is a pattern to the name. The only similar thing is that the important sheet is always the first sheet.
4)The data of every first worksheet has same no. of columns but different number of rows.
The rows can range from 02 to 60k too.
5)The data of every first worksheet has headings and all the headings are same in each first worksheet of workbook
6)A new workbook gets added to the folder occasionally.

*What I want-

I could actually do with any of the 2 variation of macro. But if possible, the 1st one is preferable.

Variation (1)

1)I want a vba code for macro which opens all work present workbooks in the folder and copies all the data of the first sheet of each workbook with headers.
2)The same macro then opens the master workbook from a different but a specified path.
(Note:- The point I am highlighting is that the master workbook is already created and is saved with the name "MASTER WORKBOOK.xlsx" in a different path before running the macro and macro in this version therefore does not create the master workbook it just opens the master workbook and then pastes the data in it.)
3)The data copied is then pasted in sheet1 of master workbook one below another. The data should be pasted with headers. The data should be pasted only in one single sheet just one below other for each workbook that it is combining.
4)As the data is getting copied and pasted with headers, It will be really helpful if there is any way to highlight those rows which contains the headers, each time they are being pasted in a row. This is just optional. If this is possible then it is big plus but if not then it's still ok.
5) Save and close the master workbook and close all the workbooks from which data was copied.
6) Whenever a new workbook is added to the folder, the macro then should only pick up the new file and run the process of copying the first sheet's data and then paste it below the last row of data in the master workbook's first sheet. This is key element of this version. If possible please suggest a way to execute this.

Variation (2)

1)I want a vba code for macro which opens all work present workbooks in the folder and copies all the data of the first sheet of each workbook with headers.
2)- macro then creates a new excel file.
(Note:- in this variation of the macro, the macro creates the master workbook)
3)The data copied is then pasted in sheet1 of newly created excel file, one below another. The data should be pasted with headers. The data should be pasted only in one single sheet just one below other for each workbook that it is combining.
4)As the data is getting copied and pasted with headers, It will be really helpful if there is any way to highlight those rows which contains the headers each time they are being pasted. This is just optional. If this is possible then it is big plus but if not then it's still ok.
5) Save the workbook as "MASTER WORKBOOK.xlsx" and close it. And also close all the workbooks from which data was copied. It doesn't matter where the file is getting saved It could be a new path or in the same path of the folder.
6) Whenever a new workbook is added to the folder then the macro in this variation will process the code on all the workbooks from the start and create a new master workbook and replace the old one while saving the new master workbook created.

Difference between two variation -

1)
-The first variation copy and pastes the data in an existing workbook.
-The second variation always creates a new file to copy and paste.
2)
-In first variation,the macro will run only on newly added file/s in folder whenever it is added and save the master workbook with newly added data.
-The second variation will always start from the beginning and run the code on all files again whenever a new file is added and create and save new master workbook while replacing old one.


Any variation of macro will work for me just that 1st one is preferable.

I can understand that this is really complex and can be time consuming but kindly help me through this.

I am just getting started in macro. So please let me know if I have to keep any workbooks open while running the macro or not. Like should I keep open the master workbook or the workbooks from which data is going get copied before running the macro or anything else.

Thank you very much in advance.

 

[mumps]

In order to get Variation (1) to work, you would need an extra column in the Master workbook which records the name of each file from which the data is copied. Would that work for you? If not, then you would have to use Variation (2). With Variation (2), you could still use the existing Master workbook without the macro creating it. Please advise. We would need to know the full path to the folder containing all your workbooks and the full path to the folder containing the Master workbook?

 

[Nick G]

First of all, thank you for your reply and effort.

So according to choices provided by you, I have no problem having and extra column in the Master workbook which records the name of each file from which the data is copied. I am totally ok with that.

So,I will go with variation 1

And for the path. I have not yet decided the path as this task of consolidation is going on as a test in my workplace. So you could add a dummy path for both master workbook and all other files. I will copy paste the permanent path in the macro when the testing of the consolidation becomes successful.

Still if you want a path you could use this -

*Files to be copied from would be kept in below folder

C:\Users\username\Test_Data\Data_Dump

*Master Workbook will be in below folder-
(Note:- the below folder will only be used for keeping a single excel file i.e. master Workbook. No other files will be kept in that folder.)

C:\Users\username\Test_Data\Master_Worlbook

Again thank you for your efforts.

Additional:-
If you require how the name of excel files are then they are as below

Mvfeed_working_53.xlsx
Mvfeed_working_zfr.xlsx
Mvfeed_working_1r4.xlsx

And so on.

And off course the master Workbook is saved as MASTER WORKBOOK.xlsx as mentioned previously.

Thank you so much again.

 

[Nick G]

Awaiting your reply.

 

[mumps]

This macro will have to be placed in a separate workbook. Change the folder paths to suit your needs.

Sub CombineSheets()
    Application.ScreenUpdating = False
    Dim lCol As Long, srcWB As Workbook, mastWB As Workbook, mastWS As Worksheet
    Set mastWB = Workbooks.Open("C:\Users\username\Test_Data\Master_Workbook.xlsx") 'change folder path to suit your needs
    Set mastWS = mastWB.Sheets(1)
    Const strPath As String = "C:\Users\username\Test_Data\Data_Dump\" 'change folder path to suit your needs
    ChDir strPath
    strExtension = Dir(strPath & "*.xlsx")
    Do While strExtension <> ""
        Set srcWB = Workbooks.Open(strPath & strExtension)
        With srcWB.Sheets(1)
            lCol = mastWS.Cells(1, mastWS.Columns.Count).End(xlToLeft).Column
            If WorksheetFunction.CountIf(mastWS.Columns(lCol), srcWB.Name) = 0 Then
                lCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
                lastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                .Cells(1, lCol + 1).Resize(lastRow) = srcWB.Name
                .Range("A1").Resize(, lCol + 1).Interior.ColorIndex = 6
                .UsedRange.Copy mastWS.Cells(mastWS.Rows.Count, "A").End(xlUp).Offset(1, 0)
            End If
        Else
            srcWB.Close False
        End With
        strExtension = Dir
    Loop
    mastWB.Close True
    Application.ScreenUpdating = True
End Sub

 

[Nick G]

Thank you for your reply

The macro is showing following error

Compile error
Else without If

Kindly provide solution for the same.

 

[mumps]

Oops! Try:

Sub CombineSheets()
    Application.ScreenUpdating = False
    Dim lCol As Long, srcWB As Workbook, mastWB As Workbook, mastWS As Worksheet
    Set mastWB = Workbooks.Open("C:\Users\username\Test_Data\Master_Workbook.xlsx") 'change folder path to suit your needs
    Set mastWS = mastWB.Sheets(1)
    Const strPath As String = "C:\Users\username\Test_Data\Data_Dump\" 'change folder path to suit your needs
    ChDir strPath
    strExtension = Dir(strPath & "*.xlsx")
    Do While strExtension <> ""
        Set srcWB = Workbooks.Open(strPath & strExtension)
        With srcWB.Sheets(1)
            lCol = mastWS.Cells(1, mastWS.Columns.Count).End(xlToLeft).Column
            If WorksheetFunction.CountIf(mastWS.Columns(lCol), srcWB.Name) = 0 Then
                lCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
                lastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                .Cells(1, lCol + 1).Resize(lastRow) = srcWB.Name
                .Range("A1").Resize(, lCol + 1).Interior.ColorIndex = 6
                .UsedRange.Copy mastWS.Cells(mastWS.Rows.Count, "A").End(xlUp).Offset(1, 0)
            Else
                srcWB.Close False
            End If
        End With
        strExtension = Dir
    Loop
    mastWB.Close True
    Application.ScreenUpdating = True
End Sub