Copy to another Workbook based on certain headers

[Dahlia]

Hi All,

I have been trying to create a template that can allow user merge multiple worksheets from multiple workbooks into one master workbook. The content being copied into the master workbook should be based on the header pre-defined in at Row 1 (not case-sensitive).

I found on the net to merge wsheets and workbooks but it doesn't do validation check on the content before it merges. it just copy the whole range that content data as per column counted in that worksheet. This gives me the hassles because in the multiple sheets and wbooks have inconsistent arrangement of columns and I have to arrange all these first one by one first before I merge them.

Eg.
Master Workbook should have all data based on the headers below:-
Name | Age | Mobile# | Occupation | address | Gender

Workbook1 sheet 1 have data based on headers below:-
Name | Gender | Age | Address | Occupation | mobile

Workbook2 sheet 4 have data based on headers below:-
Name | Occupation

Workbook 3 sheet 1 have data based on headers below:-
name | age | mobile# | occupation | address | gender


I hope I have explained my problem clearly. Would appreciate your expertise to help.

Thank you in advance.

Best Regards,
DZ

 

[Hui]

You can use an SQL based approach to merge the various worksheets
It will use the First row as Field names and hence doesn't care about the order of fields as long as they are all spelt the same

Refer: http://chandoo.org/wp/2012/04/02/using-excel-as-your-database/
or
http://chandoo.org/wp/tag/consolidation/

 

[Chihiro]

If you have Excel 2016 or PowerQuery. You can create connection to each table and then append each to create master. This method does not require VBA and will refresh with new data when source table is updated.

You may need to do bit of transformation before you append tables, based on your post, but this step will be retained in M formula in PowerQuery.

 

[Marc L]

Hi !​

I found on the net to merge wsheets and workbooks but it doesn't do validation check on the content before it merges.

If your « validation check » is just on columns headers,
there are yet some examples within threads of this forum …

Easy with MATCH Excel worksheet function !
(if same characters headers between worksheets …)

And as advised before to log in : attach some sample workbooks !
​

 

[Dahlia]

Hi,
Thanks so much for the great tips and awesome links! Surely will explore and try. Will update the outcome.

Best Regards,
DZ

 

[Dahlia]

If you have Excel 2016 or PowerQuery. You can create connection to each table and then append each to create master. This method does not require VBA and will refresh with new data when source table is updated.

You may need to do bit of transformation before you append tables, based on your post, but this step will be retained in M formula in PowerQuery.

Hi,
Am currently using MSOffice Pro 2016. I didn't know it has this PowerQuery. Will sure to try. Thanks a lot ya!

 

[Marc L]

Samples from this forum :

• VLOOKUP USING VBA

• Copying data from one workbook to another workbook …

It depends on the real data structure of your workbooks.
For huge data, faster is the SQL way like Hui wrote
and like Nebu's post in my second thread link above …
​