several tabs grouped together in a different file and updated automatically

[Gestcoaubin]

Hello, I have several datasheets with a single tab each that I would like to see grouped into a master file where the data would be updated automatically.

Basically, on my computer in each folder of an X project, I have an Excel spreadsheet that lists the contracts and invoices for the project. But I would like to have a general file on my desktop where I could switch from one tab to another quickly in order to validate which company had the contract, at what price, versus which competitor, etc., in order not to always give the contract to the same companies you see.

I thought the "consolidate" command would be the right one, but it just seems to be picking up cells when I want the entire tab.

I want the tabs in the master file to be identical to the reference one (layout, sheet name, etc.).

[#2 · Accepted solution]

@Gestcoaubin 

To be honest, I would suggest you use power query

But as you mentioned, you want to check the content of each file ("tab") on a master file.

There is a manual way, but I guess you can play with vba coding.

Ps.: For your safety, make a Copy of the original folder before you run the code below.

Sub ConslidateWorkbooks()
    
    Dim FolderPath As String
    Dim Filename As String
    Dim Sheet As Worksheet
    Application.ScreenUpdating = False
    FolderPath = Environ("userprofile") & "DesktopTest"
    Filename = Dir(FolderPath & "*.xls*")
    Do While Filename <> ""
        Workbooks.Open Filename:=FolderPath & Filename, ReadOnly:=True
        For Each Sheet In ActiveWorkbook.Sheets
            Sheet.Copy After:=ThisWorkbook.Sheets(1)
        Next Sheet
        Workbooks(Filename).Close
        Filename = Dir()
    Loop
    Application.ScreenUpdating = True
End Sub

[#3]

Thank you ! I don't master the coding very well. Can you help me with the manual way ?

[#4]

replace Environ("userprofile") & "DesktopTest" with your folder address
Example:
"C:\Users\luthi\Documents"

FolderPath="C:\Users\luthi\Documents"