Consolidate Data From Multiple Worksheets in a Single Worksheet in Excel

[layachowdary]

Hi,

I wanted to consolidate Excel files into one new Excel worksheet. Every month I will receive Excel files from different states with monthly data with two sheets, I wanted to compile all those Excel files into one new Excel file in two sheets (Sheet 1 to Sheet 1 and Sheet 2 to Sheet 2), every month data need to be appended to old data (not overwritten). Please can anyone help me with this? Every month manually doing this work is taking so much time, I am looking for a VBA code that will help me to consolidate in a single click.

I have attached sample files of 3 states of April month, like this every month I will get the files from 10 states. Every month it has to be consolidated in the Master.xlsx file.
in each state excel file is having 3 sheets, one is instructions (no need to consolidate in Master.xlsx file), the second sheet is 'Section-I VL testing' and the third sheet is Section-II Stock.
I want the Second sheet consolidated to Sheet 1 in Master.xlsx and the Third sheet to Sheet 2 in Master.xlsx

In my Excel file, in Sheet 2 (Section-I VL testing) the column heading is there till the 4th row and actual data starts from the 5th row, and in Sheet 3 (Section-II Stock) the column heading is there till the 2nd row and actual data starts from the 3rd row. The same structure is following all the states. Each state of the Excel file is having Total in Sheet 2 (Section-I VL testing) of the last row for each Excel and in Sheet 3(Section-II Stock) Date and some other rows. But in my consolidation file, I don't want Total from Sheet 2 (Section-I VL testing) and Date and other rows from Sheet 3(Section-II Stock)

The password of the Excel file is: Naco@Shivali


Thanks in advance

[layachowdary]

Hi, can anyone help me with this, please

[Marc L]

Hi,

as your Master workbook can not contain any code so you forgot to indicate where the VBA procedure must be …
As you forgot to share the exact expected result workbook according to your source workbooks attachment …
As some fast efficient ways can't work with password protected source data workbooks …
As consolidation is a common subject so see the below Similar Threads section …

[layachowdary]

Thank you Marc, for your immediate response. I wanted to consolidate all the Excel files data into one Excel file called Master, as suggested by you I have attached the Master Excel file with the sample compiled data, but I did that manually. I want the same consolidation with VBA code.

Can you please help to work on this.

Thanks once again

[Marc L]

According to your last attachment, according to what you did not elaborate you must follow these points :

• in the workbook Master folder create a folder Data (used to move source files once imported).
  
  
• In the workbook Master folder create a folder Import (where the source files must be to be imported).
  
  
• For starters paste the below VBA workbook event procedure only to ThisWorkbook module of workbook Master.
  
  
• Save the workbook Master as binary format .xlsb then close it.

At the opening of workbook Master, a message box asks to confirm the import only if
- the folder Data exists in the workbook Master folder,
- Master is the only workbook opened,
- source workbooks .xlsx exist in the folder Import of workbook Master folder.

Private Sub Workbook_Open()
        D$ = Path & "\Data\":        If Dir(D, 16) <> "." Or Workbooks.Count > 1 Then Exit Sub
        P$ = Path & "\Import\"
        F$ = Dir$(P & "*.xlsx"):     If F = "" Then Exit Sub
        If MsgBox("Execute", 33, "Import") = 2 Then Exit Sub
        Application.ScreenUpdating = False
    Do
        Bill$ = P & F
        FileCopy Bill, D & F
    With Workbooks.Open(Bill, 0)
        .Sheets(1).Range("B5", .Sheets(1).[I4].End(xlDown)(1, 14)).Copy Sheet1.Cells(Rows.Count, 2).End(xlUp).Offset(1)
        .Sheets(2).Range("B3", .Sheets(2).[B2].End(xlDown)(1, 24)).Copy Sheet2.Cells(Rows.Count, 2).End(xlUp)(2)
        .Close
    End With
        Kill Bill
        F = Dir$
    Loop Until F = ""
        Application.ScreenUpdating = True
End Sub 

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

[layachowdary]

Thanks for your reply, but the code is not working for me, it's not showing any error but not working. Now I have attached the files without any password and what I am expecting in the Master file. Please help me with this.

Thanks in advance

[beyond Excel]

Hello. In the variant that I attached:

a) Workbooks located in the 'Files to process' folder are opened by the macro,

b) The information from those workbooks is transferred to the 'Master.xlsb' workbook, and

c) Finally, these workbooks are moved to the 'Files Already Processed' folder (so they won't be processed again by mistake).

Take a look and comment, ok?...

[layachowdary]

Working perfectly; thank you so much for your help

[beyond Excel]

Thanks for the +Rep.

[jindon]

[SOLVED] Consolidate Data From Multiple Worksheets in a Single Worksheet in Excel

For the files uploaded in post #4.
Assuming Headers in both worksheets in MASTER.xlsx are prepared like attached.

[layachowdary]

Thank you Jindon, This also working perfectly, thank you so much for your support