Copying data from multiple sheets of multiple excel.

[BenTenison]

Hi Friends,
I'm working itna Pharma company HR Dept. On a monthly basis, we receive atendance file from different locations. Approximately 27-28 excel files on end of the month count of files may vary month on month. Each excel files contains 7 sheets. Everytime I have to club all the 28 excel files & make it into single excel files with 7 sheets. There is always a chance of missout of data. Is there anyway by using macro I can get this done.

Pls help.I'm not much familiar with macros.

[Vikas_Gautam]

Hi,
Welcome to the forum,
One Clarification.!
So 28 workbooks with 7 sheets each are to be clubbed in one workbook having seven sheets
so Sheet 1 in all 28 workbooks are to be clubbed one after one in sheet 1 of Resultant workbook.

[HaHoBe]

Hi, BenTenison,

please have a look at WBs to Sheets or Merge data from all workbooks in a folder or RDBMerge, Excel Merge Add-in for Excel for Windows.

HTH,
Holger

[Vikas_Gautam]

Hi Here is the code.
Untested though.
Put all your 28 Files in one Folder in Drive D: Folder Extracts
Open the Main File and run this code on it..

Please Login or Register  to view this content.

Keep an Eye over Status bar on the left bottom of the screen.
I have tried something with that.

EDIT:- Sorry Ben for that small mistake. I have taken care of that. So now you can try it.
Hi Holger, I do think that third one is a biggi. so I have corrected that.

[BenTenison]

Thanks Vikas.

That was awesome.....

I did a dummy check & it worked. Truly awesome

Thanking you again for the support provided

[Vikas_Gautam]

Thanks for the feedback..
It was totally untested.
Actually I wrote it right here..
I am glad that it worked for you.
Good Luck

[HaHoBe]

Hi, BenTenison,

some weaknesses of the Vikas macro:
- relying on the position of the sheets (not even worksheets) so any change of order will lead to a wrong compilation of data.
- switching of workbooks which is unnessary
- having all workbooks still open at the end of the macro.

I would rather go with the fourth macro from Jerryīs link (the first link I supplied) relying on the sheetnames rather than the index.

Please Login or Register  to view this content.

Ciao,
Holger

[BenTenison]

Hi,
That a very good suggestion. But is it possible to copy data based on the sheet names.

[HaHoBe]

Hi, BenTenison,

have a look at PART 4 - Appending Data from All Existing Sheets in all workbooks into a Master Workbook matching the sheetnames. The only thing you might need to add is the addition of a worksheet intoi the masterbook if the names donīt match, I would ask you to clarify on that.

Ciao,
Holger

[BenTenison]

Hi Ciao,
Thank you,
It helped a lot. Really superbbb......

[BenTenison]

Have deleted the post & started a new thread

[Vikas_Gautam]

Here is the code you are looking for:-
It is totally untested.

Please Login or Register  to view this content.

Edit:- As suggested by Holger.

[HaHoBe]

Hi, Vikas,

this is a public forum and as such any information about some errors should be reported here and not being aslked to be reported via PM.

I think that you respneded to the deleted püost (and the answer should be in the other thread).

Here is the code you are looking for:

You should restraint the loops in both workbooks and leave the loop for the new workbook once a Match was found. Iīd rather had used a Set for the corresponding worksheet in the masterbook (new book delivering the sheetname).

Ciao,
Holger

[FDibbins]

Have deleted the post & started a new thread

Why did you feel the need to start a new thread on this?

[BenTenison]

It was a different topic not related to above hence deleted & started a new thread.
W.r.t above thread "Copying data from multiple sheets of multiple excel." I raised the issue directly with Vikas since he made the codes.
Pls correct me if there is any issue in my way of approach.

[BenTenison]

When clicked on macro, in background it shows working & at the end it confirms “Finished”. But nothing is copied in excel file. Its totally blank.

[FDibbins]

OK, not problem. It sounded like you got stuck and just started over

[Vikas_Gautam]

Hi Ben Ten,
I have just tested the Code:-
It working perfectly for me without any changes.

Please Login or Register  to view this content.

1. The possible reason for the failure can be that the column headers in the MainBook is not matching exactly with headers in the Sub sheets.
If I comes out to be correct, then we will proceed to correct it as well.

[BenTenison]

Extracts.zipExtracts.zip
Pls find the file

[BenTenison]

Tried again. But still the same. Pls help

[Vikas_Gautam]

I have made some changes...
and its working now..

Please Login or Register  to view this content.

[BenTenison]

Hi Vijay, Error is showing with msg as
Runtime Error '1004'. Application-defined or object-defined error

[BenTenison]

Hi guys pls help!!

[Vikas_Gautam]

I am sorry Ben,
It was my mistake..
Its working now..

Please Login or Register  to view this content.

There is a MainBook.xlsm file in Mainbook folder in the attachment.
Check the attached zip attachment

[BenTenison]

Error.jpgHi Vijay, Th code is working properly but nothing is copied in the file

[BenTenison]

Code runs properly but nothing is getting copied in th file

[Vikas_Gautam]

Have you changed the Folder Path...?
you have to change it with your actual path..

Edit:- I have Completely rebuild the Code. You can give it a try if you want.

Please Login or Register  to view this content.

[BenTenison]

Sorry was on paternity leave, hence couldn't get in touch with you. I'm blessed witha Baby Boy. Will try the code & confirm to you