asked on

How to Merge all Excel Tables into a single Table.

I have excel files in a single folder. each of those excel files has multiple sheets with data, the data are in form of simple data and "Excel Tables" like shown in the screenshot.

I need to marge all of the Tables only into a single Table in a master Sheet.

So, I need help with a code that loops through all workbooks in a folder and searches for Tables and then combines all of the table into one giant table and puts this giant table into a new workbook. all of the tables has the identical number of columns and column headers. the column headers should be duplicated in the merged Table.

I have also attached the two example excel files that have tables in them.

I am doing this manually. I got some codes from searching, but they are all combining everything and I could not find something particular that combines only Tables i would greatly appreciate any help to automate this using VBA.
Book1.xlsx
Book2.xlsx

Roy Cox

Will the master file be in the same folder?

Will each sheet contain only one Table?

ASKER CERTIFIED SOLUTION

Roy Cox

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Skylar

ASKER

Roy,

Thank you so much. It worked.

Only it did not put the headers at the top, which i can put manual. but would have been great if the code would have put the header from any of the tables. because all headers are the same in all tables.

Roy Cox

I could change the code to copy the first Table with headers, I just thought it simpler to prepare the master sheet manually first.

Skylar

ASKER

Thank you Roy.

Roy Cox

Do you want to copy the first table completely so that the headings are imported?

Skylar

ASKER

Thanks Roy.

Yes, I want to copy the first table completely so that the headings are imported

SOLUTION

Roy Cox