More efficient loops or alternatives

I'm using Excel for a large project and am looking to get the loops to be quicker and more efficient. I am by no means an expert, but I've run in to roadblocks where my code is taking minutes to execute because of some inefficient loops (or vba/formulas in general). While not all my examples I am outlining are loops, I do want to hone in on my skill. I apologize in advanced if I'm terrible at explaining.

1. This one takes 30 or more seconds and it probably one of the most memory intensive iterations I have in my file with 3 loops total. Essentially I have a few things going on. I'm inserting formulas into 75 rows looping between sheet names C1_Team1 to C7_Team7 . C1_Team1 would be on the first 75 rows, then C2_Team1 would be on rows 75-150 and so on.

Please Login or Register  to view this content.

2. This takes about a minute. So I had to split this one up into two parts, the firt loop rebuilds C1_Teams2-4, based off of C1_Team1 as the template. The seconds set of code rebuilds C2_Team1 to C7_Team4 based off of C1_Team1. I couldn't for the life of me figure out how to combine the two sets of code efficiently without screwing up or deleting C1_Team1 which is the template all of the other sheets are built off of

Please Login or Register  to view this content.

3. This is probably my fasted loop in the ones I'm trying to make more efficient. I've tried creative ways of making this loop occur quicker, across C, E, & F:I, but I couldn't figure out how to efficiently do a ascii (column) loop. The code is quite quick, but inefficiently written:

Please Login or Register  to view this content.

4. This one I couldn't even figure out because I need on Range("D85") to Range("I85") & then Range("M85"), in addition I needed Categories!$A$1 to Categories!$D$1, but just couldn't figure it out. Filldown seemed to be the quickest way to replicate, however these do run quite fast:

Please Login or Register  to view this content.

Thank you and all help would be appreciated

Originally Posted by dangelor

Without seeing the workbook, it's hard to say...

1. Formulas will slow you down. You're writing over 20K of them. Could you not use Autofilter/copy or AdvancedFilter instead of the formulas?

The way it is designed, that isn't a possibility because it's built off a "Live" tracker that is constantly changing all day, typically from Office365 Online. So macros are only useful when I'm building/rebuilding or fixing the data people may delete, which is why 1 - 4 are designed. If there was a way to lock worksheets to where ONLY data can be inserted (including data validation, etc), then I would do it that way and most of this code would not be required. (2 would be the only one that would be required because there are 50+ sheets being built off of C1_Team1).

For 1, If I could reduce 112k+ calculations to 4200 total, do you think this would be helpful or useful?

The reason C1_Team1 is a template is because it's easier to edit 1 sheet and then have dynamic arrays or other VBA calculations to differentiate the sheets, I don't have to manually edit 50 sheets any time a change is made