Create Loop that runs until Last Worksheet

[cbramsey1979]

Ok, I have a simple code here that inserts columns and does simple lookups then moves to the next sheet. I have approximately 800 workbooks I have to run this code on and they range from 20 worksheets to 759 worksheets. What I would like to have it do is to loop through until it gets to the last sheet and then stop.

Thanks for the help

Please Login or Register  to view this content.

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #3 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer

[ELeGault]

Yikes - First let me suggest that you evaluate why you have so many tabs ... there is never a good reason to have more than 5 sheets in a given project. There is always a much more efficient way to produce your reports or track your data!

That aside, please post a sample file with a small set of data that this applies to... It looks as if you have some errors in applied formulas but this may be working with data somehow... Speculation gets dangerous though when attempting to offer solutions... Should you post any form of code again, be sure to wrap the text here in a code box that it appears like this...

Please Login or Register  to view this content.

Make it fake data, and only make it like 30 lines of data so that it is a relatively small workbook with no more than 3 sheets to test on!

Cheers

[Olly]

Please use [ CODE ] tags when you post code.

Try something like this:

Please Login or Register  to view this content.

This will loop through every worksheet on every file in a directory you select, and perform your insert columns / formulas / values operation. It then saves and closes each file.

WARNING: this will be slow. It will be faster than doing it manually, but it will still take a long time...

[6StringJazzer]

Your code appears to have macro record bloat. It is almost never necessary to select cells then operation on ActiveCell, and using ActiveSheet can lead to ambiguity and errors.

I assume your code is intended to work on a single workbook. I have modified it to work on the active workbook, and loop through all worksheets in the current workbook. If you need it to also automatically loop through 800 workbooks you need to provide information about how those workbooks are stored.

I think that ELeGault has a point. Although I don't necessarily agree that 5 is a magic number, if you have files with 759 worksheets you may have a data design issue (not to mention 800 workbooks!).

Because I do not have your files I cannot test this reliably. When you test it, make sure you are testing on a copy of a file so you do not lose the original file in case there is an error.

Please Login or Register  to view this content.

[6StringJazzer]

While I was composing my response I see that Olly provided a much more comprehensive solution. I would suggest going with that. If you use mine, you could run into some issues farther on down the road that Olly has addressed up front, plus he has addressed the multiple-file issue.

[cbramsey1979]

while I agree the number of worksheets in each of these is out of control. I am cleaning up old data, my ultimate goal is to get all of this data into access or MYSQL at which point I can work with it more efficiently. thank you all for your help

[ELeGault]

Agreed 6StringJazzer, 5 is an average workbook that I find for even the large projects that I run into... There may be some exceptions but most of the time if you have more you are likely repeating information or creating a view that could have been completed in the main sheet instead of making a locked in view... e.g. Monthly vs Daily... But you are right 5 is not "The Magic Number" I just recommend really looking at a project once you get over that as you could probably condense and keep the processing down.

Nice Olly I think I would only modify the part in which you apply the worksheet functions (Active formulas) with such a large set of data...it would be best to let VBA handle the calculations...

If I read the code right it would be this... I did not push the VLookup as that is on a set file and I am not sure what it is actually looking for...probably a set list in which could be coded instead of going to this outside sheet... Again to reduce the overhead (Normally I would not recommend hardcoding a lookup... but you are talking about a large set of data)

And without knowing what you are doing... I do find it odd that you are pulling 3 records prior in your C column if you find that Section is present in the Newly presented E column (Originally the A column) of these sheets... But without really knowing the project, I allow this to happen...

Please Login or Register  to view this content.

May not need the above code, but I do suggest letting VBA do the work over active formulas... Excel is a great and powerful tool but even on the fastest computer it is throttled when it comes to its computing power.

To Be clear... I would adjust this section

Please Login or Register  to view this content.

To Be this

Please Login or Register  to view this content.

- Added LastRow, Adjusted to make it insert 4 columns as I was ignoring the Vlookup still Also adjusted the lastrow to be based on the 5th column

[Olly]

Just a quick update - spotted a debug line left in the code, so please amend the following section:

Please Login or Register  to view this content.

Just to make sure that the application is returned to the default state, if no directory is selected.