Copy Data from one sheet to another using header as key

Holla,


I have been trying to append multiple sheet data into a single one by sorting them with Header as a Key.

Problem : I have a worksheet with Sheets : Sheet1, Sheet2, Sheet3, Sheet4,...... Sheet(n)

Note: There is only one worksheet with multiple sheets.

Every sheet has multiple columns (not fixed) but all of them HAVE same header name but not in the same order.

Total number of rows in each sheet are not fixed.

eg.:-
Sheet1_Columns_Headers (Name, Age, ***, Roll No, Subject1, Subject2, Subject3,.....)

Sheet2_Columns_Headers (Roll No,name,***,age,subject4,subject3,subject1,subject2,......)

Sheet3_Columns_Headers (***,age,Roll No,name,subject4,subject3,subject1,subject2,......)
.
.
.


Output : I want all the data to be appended in Sheet1 with all the entries placed exactly below the appropriate columns or header titles.

I would really appreciate your help as I am at a crucial stage and badly stuck at this part.

I have also attached the worksheet.

Hello Watersev,

Thank you very much for your prompt reply.

Well, the code is not working as it is intended to. The Rows are getting all mixed up due to sorting and the data is appearing in an inconsistent format.

Also the data in sheet2 and sheet3 has also been changed (updated) by the code which should not have been the case.

Please look into it once again and i am attaching the sheet again with the output I got after runnig the macro.

Awaiting response. :-)

check attachment

Thank You Watersev. The code worked perfectly for the file I mentioned.

i appreciate your effort.

But I want this code to be generic.

If the number of columns change the code does not work.. I tried modifying it but the results are haphazard.

Can you provide me a code for this...

Search for the first header name in the "output" sheet... compare this name with all the headers in other sheet... if a match is found.. copy all the cells underneath this matched header.. and paste below the same header in output file...

this will make the code usable everywhere...

And i would be finally able to put it in my module..

Do help..

check attachment, I've added three new columns, try it yourself

Hey WaterSev,

That code worked 99% as per required. there are still some pitfalls.

The contents dont get copied if the output file is empty..

And it is not possible to append the files with more than 25 headers simultaneously..

And if the output file is non empty then the contents of the output file are redundantly copied..

Please try to adress these issues.

Also remove the sorting code and the things will be smoother.

Cheers Mann...

check attachment

Hi WaterSev,

the code works fine with the given workbook... but when i try it on anyother work book it gives a bug at tis line:

Please Login or Register  to view this content.

plz help me resolve this.

Originally Posted by nihitsk

Hi WaterSev,

the code works fine with the given workbook... but when i try it on anyother work book it gives a bug at tis line:

Please Login or Register  to view this content.

plz help me resolve this.

Problem Solved : Some of the columns are in the dte format...

Is there any way we could check for dates also... or convert whole doccument into Text.

I will try that....

If i couldnt... this forum will surely help

if the offered solution resolves your issue, please mark the thread as solved

i dont know how to do that. Pl tell me hw to mark a thread as "Solved."