Macro to Extract data in all worksheets in a workbook to master sheet

[RAM72]

Have to 2 workooks with the following headers in bold

In these workings there are data in each worksheet , some columns may be blank with no data.

Looking for a macro to extract from all worksheet in the workbook to master sheet for each headers .

Note that each 2 headers are from 2 different workbooks but macro to accomplish such task for each workbook on master sheet separately.

SUPPLIER REFERENCE DESCRIPTION HS CODE DESCRIPTION BILL No

SUPPLIER EAN REF DESCRIPTION MATERIALS TARRIF NO DESCRIPTION 1

 

[Sameer.k21]

Try this and modify as per requirement.

Option Explicit
Sub loop2()
Dim fol As String, wb As String, mywb As Workbook, ws As Worksheet, lrow As Integer

Application.ScreenUpdating = False

With Application.FileDialog(msoFileDialogFolderPicker)
    .AllowMultiSelect = False:    .Show
    On Error Resume Next: fol = .SelectedItems(1): Err.Clear: On Error GoTo 0
End With

fol = fol & "\":    wb = Dir(fol & "*.xls*")
If wb = "" Then Exit Sub

lrow = 2
    Do While wb <> ""
    If Not wb <> ThisWorkbook.Name Then GoTo n
        Set mywb = Workbooks.Open(fol & wb)
            For Each ws In mywb.Worksheets
                With ws.Cells(1, 1).CurrentRegion
                    .Offset(1).Resize(.Rows.Count - 1).Copy
                    ThisWorkbook.Sheets(1).Cells(lrow, 1).PasteSpecial xlPasteValues
                    Application.CutCopyMode = False: lrow = lrow + .Rows.Count - 1
                End With
            Next
        mywb.Close 0
n:  wb = Dir
    Loop

Application.ScreenUpdating = True

End Sub

 

[RAM72]

Tried your code but a little confused see screen.
When applying the code it gives a folder , add an existing folder nothing happens.
create a new folder nothing happens.
Kindly advise how to use it

 

[Sameer.k21]

For this code:
Your master workbook should be in different folder/desktop.
The files you want to consolidate in master workbook should be in one folder(files wont be seen).
it will loop through all worksheets and workbooks move the data to master workbook in sheet specified.

that is why I said modify accordingly.

 

[RAM72]

For this code:
Your master workbook should be in different folder/desktop.
The files you want to consolidate in master workbook should be in one folder(files wont be seen).
it will loop through all worksheets and workbooks move the data to master workbook in sheet specified.

that is why I said modify accordingly.

Hi
It's ok now , I was doing it completely in the opposite direction
Thank you for this piece of code save me from a tedious task