Need to lock formulas in a specific column so macro will not affect it

Im very new to Macros and this is my 1st time to post a question.

I have a macro that splits data(leads) by type into different sheets. I then have a different formula to input in the last column so the leads are evenly distributed (within each sheet) amongst 11 people:

=INDEX(Summary!$A$3:$A$13,INT((ROWS(Z$1:Z2)-2)*11/COUNTIF('Exotic Luxury'!$C$2:$C$10000,"Exotic Luxury")+1))

Only catch is that when I run the macro, it deletes the formula from the last column.

Is there any way I can lock my formula so i dont have to copy and paste it after every time the macro runs? I've included my macro below for reference.

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. I also recommend indenting your code to show the structure. --6StringJazzer

Any help is appreciated!

Should the formula stay exactly the same for each sheet, or does it need to be changed to reflect the new sheet names (where bolded)?


=INDEX(Summary!$A$3:$A$13,INT((ROWS(Z$1:Z2)-2)*11/COUNTIF('Exotic Luxury'!$C$2:$C$10000,"Exotic Luxury")+1))

Your macro code is kind of hard to understand without the workbook it is used on, or any in-depth description of what you want it to do....