Loop through all sub-folders of a main folder and rename sheet same as workbook name

**sans** · 11-27-2012, 01:47 PM

Hi to everyone,

I have been looking for a solution for the above problem for the past few days but haven't found a solution so I am turning for help. I have found some relevant macros but haven't been able to modify them successfully.

I am looking for a macro that loops through all sub-folders of a main folder, the levels of sub-folders can be anything from 1 level to sometimes more than 5 levels, and perform an action. In this case I would like to rename the first sheet of each workbook the same as the workbook name.

Any help is greatly appreciated, many thanks!

**tigeravatar** · 11-27-2012, 02:48 PM

sans,

The following macro calls a custom subroutine I created awhile ago which is used to loop through all subfolders in a main folder and get all of the full file paths and names that meet a file extension criteria. In this case, I set the file extension criteria to be "xls*" to that it will pick up only Excel files (xls, xlsx, and xlsm). Then it uses that file's workbook name to rename the first sheet to be the same name as the workbook. In some cases, this may not be strictly possible due to sheet name character limit and illegal characters in the sheet name, but the macro will get it as close as possible (and in the majority of cases it should be an exact match). Just change the "C:\Test" (in red in the code) to the correct folder path of the main folder.

Please Login or Register  to view this content.

Here is the subroutine I created that is called in the above macro. It is a recursive subroutine (it calls itself) so it doesn't matter how many subfolders there are, nor how many subfolders each subfolder has, and so on:

Please Login or Register  to view this content.

**sans** · 11-27-2012, 04:20 PM

Hi tigeravatar,

The macro works absolutely fantastic. Thank you very much! I tested it with a few different folders structures with various levels of subfolders and I haven't encountered any errors. With the last attempt though, it simply stopped calculating without giving any errors or any window popping up, it just stopped calculating. Is there a limit on how many files it can process at any one time?

Can the above macro that calls all sub-folders be modified and to be used to carry out any other simple functions, i.e. open files in all sub-folders and perform another action in each file?

Many thanks for your help!

**tigeravatar** · 11-27-2012, 06:18 PM

The GetAllFiles subroutine can return a maximum of 65,000 files. If you have more than that, it will probably stall out, though I would expect an "Out of Memory" or "Subscript out of range" error. Also, the more files it proccesses, the longer it will take. So if it is processing 10,000 files, it may look like it has stalled, when its actually just taking awhile to complete.

As for changing the tgr macro, yes that can be changed to do whatever you need for each file found.

**sans** · 11-28-2012, 01:29 PM

Hi tigeravatar,

No problem, thank you for the additional details.

The macro was fast as I could see it working in the background in the vb editor window running through all the workbooks. Very fast actually. It works absolutely great.

About changing the macro to perform other actions, as I understand I should leave the Public Sub GetAllFiles as it is and simply alter the tgr.

For example, if I have separate workbook named "Data Workbook" and would like to copy a range from this workbook and paste it in all the sheets of the workbooks in the folders/sub-folders, would this be achievable by simply changing the tgr macro? Can the tgr be changed to a kind of "template" where I can use it to insert other macros to perform different functions?

Thank you for all the help.

**tigeravatar** · 11-28-2012, 01:46 PM

Here is the generic code for tgr - note that you can name it to whatever you want by changing the "tgr" in the very first line: Sub tgr()
As for what the code will be, that depends on the specific task you have in mind.
Some tasks will require new variables, like the renaming worksheet originally shown. Your request to use a workbook named "data workbook" to copy a range from there and paste it in all sheets of all workbooks returned with GetAllFiles will take a few new variables. I can provide that code if you'd like, or you can try to work it out from here.

Please Login or Register  to view this content.

**sans** · 11-28-2012, 02:10 PM

That is amazing, thank you very much!!!
If you could help me with the second function as well it would be simply great as I simply have no idea how to achieve this - or the rest of the functions I would like to perform

I will need to find some other macros and try to edit them to the best of my understanding.
Thank you very much for all your help!

**tigeravatar** · 11-29-2012, 05:38 PM

sans,

It would look something like this:

Please Login or Register  to view this content.

**sans** · 11-29-2012, 06:17 PM

Hi tigeravatar,
Thank you so very much for your reply! When I hit alt+F8 and point to the macro to run it, the Run button is greyed out. I changed the path,file extension and source path. I am sure I am doing something wrong, is there anything else should be aware of?
A million thanks for the help.

**sans** · 11-29-2012, 06:25 PM

Yep, my bad. There was something pasted wrong in the file path, (I think an additional space), after I recopied it everything works absolutely amazing! Thank you very much for your help!!!

I can now compare the template in post #6 and the latest macro and try and edit it for all the few other functions I have in mind. Again, a millions thanks for all your help!!!

**tigeravatar** · 11-29-2012, 06:27 PM

You're very welcome

**sans** · 12-09-2012, 05:47 AM

The above macro copies a range from a specified sheet in workbook and pastes it in all the workbooks in all the subfolders.

I am trying to edit the above macro to copy a Sheet named "Summary" in all the workbooks in the subfolders. This is what I came up with:

Please Login or Register  to view this content.

For some reason, when running the code, it copies the sheet named summary from the Updates folder, but instead of copying the sheet in each of the workbooks in the subfolders, it creates extra workbooks and copies the worksheet summary there. After 2 days, I can't figure out where I am going wrong

**tigeravatar** · 12-10-2012, 02:43 PM

When you do a Sheets.Copy method, you have to specify where you want the copied sheet to be placed, otherwise a new workbook gets created with the copied sheet. So basically:

If you want the Summary sheet to be at the beginning of the workbook:

Please Login or Register  to view this content.

If you want the Summary sheet to be at the end of the workbook:

Please Login or Register  to view this content.

**sans** · 12-11-2012, 12:45 PM

Hi ta,

Absolutely great, worked like a charm! Thank you very very much for the help once again!

As a side note, I have learned a lot from studying the help you have been providing here on the forum!

Thank you!

**tigeravatar** · 12-11-2012, 12:55 PM

Glad to hear it! If you look at some of my earliest posts, you can see just how much of a noob I was back then, lol

**sans** · 12-11-2012, 01:52 PM

ta, should I assume that all of your vba skills are self taught?! Your "style" of coding has a simplicity to it which makes it in a way very understandable (to newcomers in vba)!

**tigeravatar** · 12-11-2012, 02:01 PM

Yea, I am entirely self-taught. Lots of practice and google, hehe. I appreciate your compliment regarding my style of coding. I generally try to make it as readable as possible so I don't have to comment everything

Loop through all sub-folders of a main folder and rename sheet same as workbook name

LinkBack

Thread Tools

Rate This Thread

Display

Loop through all sub-folders of a main folder and rename sheet same as workbook name

Re: Loop through all sub-folders of a main folder and rename sheet same as workbook name

Re: Loop through all sub-folders of a main folder and rename sheet same as workbook name

Re: Loop through all sub-folders of a main folder and rename sheet same as workbook name

Re: Loop through all sub-folders of a main folder and rename sheet same as workbook name

Re: Loop through all sub-folders of a main folder and rename sheet same as workbook name

Re: Loop through all sub-folders of a main folder and rename sheet same as workbook name

Re: Loop through all sub-folders of a main folder and rename sheet same as workbook name

Re: Loop through all sub-folders of a main folder and rename sheet same as workbook name

Re: Loop through all sub-folders of a main folder and rename sheet same as workbook name

Re: Loop through all sub-folders of a main folder and rename sheet same as workbook name

Re: Loop through all sub-folders of a main folder and rename sheet same as workbook name

Re: Loop through all sub-folders of a main folder and rename sheet same as workbook name

Re: Loop through all sub-folders of a main folder and rename sheet same as workbook name

Re: Loop through all sub-folders of a main folder and rename sheet same as workbook name

Re: Loop through all sub-folders of a main folder and rename sheet same as workbook name

Re: Loop through all sub-folders of a main folder and rename sheet same as workbook name

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions