Combining Multiple Sheet to One Workbook - Loop Function - Urgent !!! Please Help !!!

Hi Friends,

I am struggling to search a piece of excel macro which can do the following steps for me :

1. I have individual excel sheets in 3 folders where the name of the file is the same and an unique identifier
for example : 1_test1.xlsx in folder C:\Macro\test1; 1_test2.xlsx in folder C:\Macro\test2; 1_test3.xlsx in folder C:\Macro\test3

2. Such a way i have excel sheets which are ranging from 1 to 4000

3. Each excel sheet has only 1 sheet named test1; test2; test3

4. Macro is needed to
a. Open the directory
b. create a new workbook with is 1.xlsx
c. open the worksheet 1_test1.xlsx
d. copy all data in test1 sheet from 1_test1.xlsx and paste it into 1.xlsx in sheet test1
e. close the worksheet 1_test1.xlsx
f. open the worksheet 1_test2.xlsx
g. copy all data in test2 sheet from 1_test2.xlsx and paste it into 1.xlsx in sheet test2
h. close the worksheet 1_test2.xlsx
i. open the worksheet 1_test3.xlsx
j. copy all data in test3 sheet from 1_test3.xlsx and paste it into 1.xlsx in sheet test3
k. close the worksheet 1_test3.xlsx
l. close 1.xlsx

re-run this from 1 to 4000

Please help with a code, since i am breaking my head over it and still not found any thing related to this.

Thank You

Hi friends, please help me with this code.

Please help !!!

Hi Alf

Thank you soo much for the reply.

What i meant by 4000 was not worksheets but rather workbooks. Explaining the detailed logic again, i guess, there might be some tweaking required in the code

___________________________________________

For example: the following workbooks with sheet names are stored in a folder C:\Macro\

<the nomenclature of the workbook is "Sr.No._Unique Name" Sr No will be from 1 to 4000 and Unique Name will be constant test1, test2, test3

1_test1.xlsx Sheet Name - test1
1_test2.xlsx Sheet Name - test2
1_test3.xlsx Sheet Name - test3


Then there are other workbooks where the sheet name are the same as above but they are from 2 to 4000 i.e.

2_test1.xlsx Sheet Name - test1
2_test2.xlsx Sheet Name - test2

etc...

The Macro should create a new work book which is 1.xlsx; 2.xlsx;3.xlsx etc.. till 4000.xlsx

Each of these workbooks should have sheets test1, test2, test3 from the resp excel workbooks for example:

1.xlsx with sheets
test1 from 1_test1.xlsx
test2 from 1_test2.xlsx
test3 from 1_test3.xlsx


This is the solution i am looking for ...

Before I spend more time on your problem I would like you to test my macro and see if the result from the first three files is what you are looking for.

If that works the way you need then I could have a go at the rest of the problem.

Alf

Originally Posted by Alf

Before I spend more time on your problem I would like you to test my macro and see if the result from the first three files is what you are looking for.

If that works the way you need then I could have a go at the rest of the problem.

Alf

hi Alf,


tried running the macro, 1.xlsx is getting created, but after that there is a error which says - subscript out of range (error 9).

thanks. will await your feedback

The macro expects this layout of folder structur:

"C:\Macro\" and then there should be 3 sub directories named "test1", "test2" and "test3"

In directory "C:\Macro\test1\" macro expexcts to find a called file "1_test1.xlsx" with a sheet named "test1" and the error message "subscription out of range" means that either there is no sheet named "test1" or the there is no file named "1_test1.xlsx" or the folder structure is not "C:\Macro\test1\"

So check those paramertes in order to see where the problem lays.

Alf

Hi Alf, definitely, would check this and confirm rightaway.. thank you very much for the clarification.

screenshot 1.pngscreenshot 2.pngscreenshot 3.pngscreenshot 4_error.png

Hi Alf, the error is still the same -

Not sure you put macro in "Module". Just to test I upload a file "Mast2.xlsm" where you can run macro from.

Looking ay picture it seems like there are two files in sub folder "test1"? According to your previous posing there should only be 1 file in each folder so in this case macro will inly copy from file "1_test1"

Alf

Originally Posted by Alf

Not sure you put macro in "Module". Just to test I upload a file "Mast2.xlsm" where you can run macro from.

Looking ay picture it seems like there are two files in sub folder "test1"? According to your previous posing there should only be 1 file in each folder so in this case macro will inly copy from file "1_test1"

Alf

hi alf, this works perfect ! i am sorry, i didnt mention in my earlier post related to multiple workbooks - that is 1 to 4000 - plz help me with that too - plz

hi alf, i managed to prepare the loop in and now its completely solve - just needed one help - how do i close the workbooks that are opened during the processing of macro and can it also run in the back end - because now it keeps opening all file -

how do i close the workbooks that are opened during the processing of macro and can it also run in the back end - because now it keeps opening all file

First of all macro has to open all files in order to extract data but when I run the macro in my environment after moving the sheet "test?" from the "?_test?" to the "1.xlsx"
all the file "?_test?" is closed automatically.

So what is the name of the files that is kept open?

can it also run in the back end

???? Not sure what you mean by that, as long as a macro runs excel is locked and you have to wait until the macro is finished before you can start working with excel again.

Unless you can start two instances of excel:

http://www.online-tech-tips.com/ms-o...-new-instance/

What is missing in the macro is a bit of error handling. If a folder, a file or a sheet is missing when running the macro it will throw an error and stop. I'll have a go of how to implement this.

Alf

Ps Why did the macro throw a "subscription out of range" error message on your first test? Need to know as "long distance" troubleshooting can be a problematic and knowing what went wrong makes it easier for me the next time I need to fix a problem.

Originally Posted by Alf

First of all macro has to open all files in order to extract data but when I run the macro in my environment after moving the sheet "test?" from the "?_test?" to the "1.xlsx"
all the file "?_test?" is closed automatically.

So what is the name of the files that is kept open?

hi alf, the files which remain opened are the 1_test1.xlsx, 1_test2.xlsx, 1_test3.xlsx - 1.xlsx gets closed.



???? Not sure what you mean by that, as long as a macro runs excel is locked and you have to wait until the macro is finished before you can start working with excel again.

Unless you can start two instances of excel:

http://www.online-tech-tips.com/ms-o...-new-instance/

i meant, if it can run in the taskbar - anyways, this shouldnt be a problem.

What is missing in the macro is a bit of error handling. If a folder, a file or a sheet is missing when running the macro it will throw an error and stop. I'll have a go of how to implement this.

yes, to resolve this issue i had to introduce sheets even if its blank - also, there is a possibility that few sr.no.s will not be available - if there is a possibility of identifying a missing value and skip to next, should resolve, but i am too novice to understand how will this get coded !

Alf

Ps Why did the macro throw a "subscription out of range" error message on your first test? Need to know as "long distance" troubleshooting can be a problematic and knowing what went wrong makes it easier for me the next time I need to fix a problem.

the issue as i understand was i had pasted the macro in a workbook and not in a module, maybe because of that the error happened. but with your maste.xlsm it worked absolutely fine.

P.S. you have been a saviour for me, i was struggling and spend days to know how i would be able to crack it, but with your help, its feeling great to receive the solution - and that too perfect one !! thank you soo very much.

Originally Posted by Alf

Hmm don't know why files 1_test1.xlsx, 1_test2.xlsx and 1_test3.xlsx are open after the macro run. I've written and tested this macro using Excel 2007 as this is the Excel version you are using according to your signature.

So when I run this macro every files mention above is closed as soon as the worksheet is moved from the opened file to file 1.xlsx

Well I've changed the macro a bit, test and see if this modefication closes the 1_test1.xlsx, 1_test2.xlsx and 1_test3.xlsx files.

I would like to know how this from 1 to 4000 files are organized i.e. is the files going from 1_test1.xlsx to 1_test4000.xlsx. And how about folders? Same pattern as well as the naming convention for sheets.

I need to know this in order to write the error trapping parts.

Find modified macro in uploaded "Mast3.xlsm", click button to run.

Alf

hi alf

the folders would be test1, test2, test3 only within them the files would 1_test1.xlsx to 4000_test1.xlsx.

the issue is there might be a few files which wont b present like 200_test1.xlsx maynot be there -

sheets should be named as test1, test2, test3 - as per point above in 200.xlsx there might not be test1 - but test2,test3 will be there - atleast 1 sheet will always be there .

hope this explanation helps, if incase there's anything else, please let me know. will test the revised macro and provide you with feedback.thanks.

hi alf, please let me know, if you were able to understand the problem

Not sure but could it be like this?

You have a master folder "C:\Macro\" and then you have 3 subfolders "\test1", "test2" and "test3".

In these subfolders you have files like 1_test1.xlsx up to 4000 but some file numbers may be missing. The files should have sheets name like test1 or test2 or test3 or some other name but every file should contain one sheet.

To conclude neither the file structure nor the naming convention has a regular structure so what should one use to extract the data you are looking for? Have you any idea because I've not got any at the moment.


Alf

hi alf,
1 to 4000 will always be there , what will not be there is test1 or test2 but atleast 1 sheet will always be there

for example in the code it should be somethin like this -

search 1_test1.xlsx, then move test1 sheet to 1.xlsx ; if 1_test2.xlsx is not present, then skip and move to 1_test3.xlsx, if present then add test2 sheet to 1.xlsx

hope this clarifies

I'm still don't know if you do have three subdirectories i.e. "C:\Macro\test1", "C:\Macro\test2", and "C:\Macro\test3"

If you don't know the numbers of files in a subdirectory one could use the "Dir" command to loop through all the files in a subdirectory, open every file and copy the used range in the opened file to the "1.xlsx" file to a sheet named after the opened file i.e. the sheet with the value from the "1_test2.xlsx" file will be named "1_test2"

Then after copying the "1_test2.xlsx" file is closed and the next file opened .....

Alf

hi alf, this should work. plz share some code so that i can run and test the scenario. regards

Ok, here we go. Again this macro assumes that the files are found in subdirectories "test1", "test2" and "test3". The loop for subdirectories is set by the i value gong from 1 to 3.

As a subdirectory is activated, the dir and loop command opens every file in that particular folder, copies the result from the active sheet to a new sheet in the "1.xlsx" file.

After looping through all files in a subdirectory the next subdirectory gets activated as i changes from 1 to 2 and the cycle repeats. I've tested this on a structure with 3 subdirectories and 3 files in every subdirectory and for me it works without any problem so theorecically it should work for 4000 files as well.

Please Login or Register  to view this content.

Alf