I am very new to programming but I do deal with excel files a lot. I have a challenge in data consolidation. Here is the situation.
I have a folder which contains all the workbooks (around 25).
Each workbook contains 10 work sheets which has similar format(table structures, some other headings which is not relevent).
Each sheet contains multiple tables that varies in range and number of tables. Ex. WB1.WS1 contains 2 tables that has ranges A5:J16 and A25: H23 respectively . Now WB2.WS1 contains same 2 tables(I mean format) that has data ranges from A5:J60 and A80: H113 respectively. The table header rows are same for respective tables. All the worksheets follow some thing similar with different ranges and increase in tables count. Basically table formats are same but the data range varies dynamically.
Now the challenge is to have a final WB with 10 sheets that contain the appended data. Ex: FinalWB.WS1 should contain 2 tables. FinalWB.WS1.Table1 data = WB1.WS1.table1 + WB2.WS1 .table1+ .. + WB10.WS1 .table1 and FinalWB.WS1.Table2 data = WB1.WS1.table2 + WB2.WS1 .table2+ .. + WB10.WS1 .table2
Can someone help with a macro or vb code to do this job. I tried to search for a suitable code in the internet but couldnt find.
I have a folder which contains all the workbooks (around 25).
Each workbook contains 10 work sheets which has similar format(table structures, some other headings which is not relevent).
Each sheet contains multiple tables that varies in range and number of tables. Ex. WB1.WS1 contains 2 tables that has ranges A5:J16 and A25: H23 respectively . Now WB2.WS1 contains same 2 tables(I mean format) that has data ranges from A5:J60 and A80: H113 respectively. The table header rows are same for respective tables. All the worksheets follow some thing similar with different ranges and increase in tables count. Basically table formats are same but the data range varies dynamically.
Now the challenge is to have a final WB with 10 sheets that contain the appended data. Ex: FinalWB.WS1 should contain 2 tables. FinalWB.WS1.Table1 data = WB1.WS1.table1 + WB2.WS1 .table1+ .. + WB10.WS1 .table1 and FinalWB.WS1.Table2 data = WB1.WS1.table2 + WB2.WS1 .table2+ .. + WB10.WS1 .table2
Can someone help with a macro or vb code to do this job. I tried to search for a suitable code in the internet but couldnt find.