combine several spreadsheets into one

I am so frustrated to combine several spreadsheets into all in one, and seperate all in one spreadsheet into several with the same header frenquently. Pls check the attached example excel spreadsheet., could anyone teach me how to program in vba to achieve the result as above?

Many thanks.

Here are two simple array macros to consolidate all to one sheet, or parse all to one out to the vendor sheets.

Option Explicit

Sub Consolidate()
Dim NR As Long, ws As Worksheet, wsA As Worksheet
If MsgBox("Clear the All in one report and collate new info from Vendor sheets?", _
    vbYesNo + vbQuestion) = vbYes Then
    On Error GoTo ErrorHandler
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    Sheets("All in one").Activate
    Set wsA = ActiveSheet
    Range("A2", Range("A2").SpecialCells(xlCellTypeLastCell)).ClearContents
    NR = 2
    
    For Each ws In Sheets(Array("AEG", "ARP", "ARV", "BEA", "CPM", "EFX", "KER", "MIT", "NUO", "PTE", "SND", "TFS", "TTE"))
        ws.Activate
        Range("A3", Range("A3").SpecialCells(xlCellTypeLastCell)).Copy wsA.Range("A" & NR)
        NR = wsA.Range("A2").End(xlDown).Row + 1
    Next ws
End If
wsA.Activate
Set wsA = Nothing
ResetAll:
   Application.ScreenUpdating = True
   Application.EnableEvents = True
   Exit Sub

ErrorHandler:
    MsgBox Err.Number & " - " & Erl & " - " & Err.Description
    Resume ResetAll
End Sub

Sub Parse()
Dim LR As Long, LR2 As Long, i As Long, MyArray(), ws As Worksheet, wsA As Worksheet
If MsgBox("Clear ALL the vendor sheets and parse out new info from the All In One sheet?", _
    vbYesNo + vbQuestion) = vbYes Then
    On Error GoTo ErrorHandler
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    Set wsA = Sheets("All in one")
    wsA.Activate
    MyArray = Array("AEG", "ARP", "ARV", "BEA", "CPM", "EFX", "KER", "MIT", "NUO", "PTE", "SND", "TFS", "TTE")
    Range("A1").AutoFilter
    LR = Range("A" & Rows.Count).End(xlUp).Row
    
    For i = 0 To UBound(MyArray)
        Set ws = Sheets(MyArray(i))
        LR2 = ws.Range("A" & Rows.Count).End(xlUp).Row
        ws.Range("A3:AA" & LR).ClearContents
        Range("A1").AutoFilter Field:=9, Criteria1:=MyArray(i)
        Range("A2:R" & LR).SpecialCells(xlCellTypeVisible).Copy ws.Range("A3")
    Next i
        
        Range("A1").AutoFilter
End If

Set wsA = Nothing

ResetAll:
   Application.ScreenUpdating = True
   Application.EnableEvents = True
   Exit Sub

ErrorHandler:
    MsgBox Err.Number & " - " & Erl & " - " & Err.Description
    Resume ResetAll
End Sub

How to use the macros:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet

The macros are installed and ready to use. Press Alt-F8 and select them from the macro list.

That error usually means the macro can't find a specific sheet. If you've tried this sheet on a different one that the one you uploaded, it is expecting to find the same sheets.

Try in the attached version of your original file.

The two macros are "Consolidate" and "Parse".

Please do not delete the sheets. As stated earlier, it is expected that those sheets will be there. The macros clear away the old data on those sheets, so you do not need to delete them.

Consolidate "clears the data from the All in one sheet" without deleting the sheet itself. This way we don't have to recreate headers and stuff.

Parse clears the individual sheets of data without deleting the sheets and then parses out the data from ALL IN ONE back out to all the sheets.