Forum Discussion
several tabs grouped together in a different file and updated automatically
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.).
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
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- Thank you ! I don't master the coding very well. Can you help me with the manual way ?
- replace Environ("userprofile") & "DesktopTest" with your folder address
Example:
"C:\Users\luthi\Documents"
FolderPath="C:\Users\luthi\Documents"
