Help! trying to consolidate multiple workbook to single workbook

[it_help]

1. There are 26 workbooks with 13 sheets in it. All the workbooks has same tabs and same columns but different data.
2. I am trying to consolidate 8 out of 13 sheets into one master workbook for some data gathering purposes.
3. My expectation is to create one master workbook which contains all the 8 required sheets and each sheet should contain data for all the 26 workbooks. Also, these workbooks are password protected. For example: if there are 3 sheets in 10 workbook : Travel , rent and training. Now i want to get travel data for all the 10 workbooks in one sheet and same for rent and training. basically it should merge all the tabs with same name in the master workbook

I have tried a VBA, it is consolidating all the files but not merging the data of the sheets with the same name. It is just bringing in all the sheets together in one workbook

Sub GetSheets()
'Update ExcelJunction.com
   
    Path = ""
    Filename = Dir(Path & "*.xlsx")
    Do While Filename <> ""
    Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
   
    For Each Workbook In Workbooks
       If Workbook.Name <> ThisWorkbook.Name Then
    Workbook.Worksheets(1).Copy Before:=ThisWorkbook.Sheets(1)
    ActiveSheet.Name = Workbook.Name
    End If
    Next
    Set Workbook = Nothing
    Workbooks(Filename).Close
    Filename = Dir()
    Loop
End Sub

Hope this make some sense.

Please let me know any additional details are required.

Thanks!

 

[AlanSidman]

crosspost: https://www.mrexcel.com/forum/excel...e-workbook-into-one-workbook.html#post5284801

and crosspost: https://www.excelguru.ca/forums/sho...-workbook-into-one-workbook&p=41251#post41251

 

[vletm]

it_help
Please, ReRead https://chandoo.org/forum/threads/site-rules-new-users-please-read.294/

 

[Marc L]

​

See the tutorial on Chandoo …​

​