Merging of excel files which has 10 sheets in each

[salma2009]

Hi All,

I am in a difficult position.
I have two workbooks Workbook1 & Workbook2. Both the workbooks have the same sheet names.I have twenty sheets in each workbook.
My problem lies here i want to match the sheet name of workbook1 with the sheetname of workbook2 if they are same then copy the data of woksheet of workbook2 into workbook1 as the data is different in both the workbooks or copy the data of into a new workbook.

SAY EXAMPLE
Workbook1 has sheets with NAMES Age, location,Hobby etc,,,,,,,,,,,,,
Workbook2 has sheets with NAMES Age, location,Hobby etc,,,,,,,,,,,,,

I want a new workboo3 Which will have the data of workbook1 Age sheet and data of Workbook2 Age sheet in new sheet of workbook3 similarly for the remaining twenty sheets.

or it has to copy the data of worksheet2 Age sheet data in Workbook1 Age sheet.


Attached are the sample files

Please help me on this as early as possible.

I have serached the web it gives the solution to merge only the first worksheet not the remaining sheets



Regards,
Salma

[ravishankar]

Hi
It is not clear from your example how you want the data consolidated. book3 has same data as book2. Book1 shows Pivot table. Pl give us a better picture of what you would like to see in the merged file
Ravi

[jmaocubo]

Please Login or Register  to view this content.

Try this...

[salma2009]

Its working absolutely fine.Thanks a lot!!!!!!!!!!! Kudos to you!!!!!!!!!!!!!!!

But I need a small help on the same i want to add some empty rows in each worksheet of main work book so that i can differentiate the data of book1 and book2. IS IT POSSIBLE?
The second book data is from pivot table and i want to remove the structure of the pivot table data while copying them to main workbook i just want to paste the data as values..
How to do that?

Thanks,
Salma

[salma2009]

Can you pls help me in modifiing the code jmaocubo.