How to zero worksheet index?

[GIS2013]

Hello,

My macro creates (and names) and then deletes rather large number of worksheets in each iteration.

Recently I've noticed that although names are given properly (through a loop) the index is not zeroing but keep growing and growing.

I will add that the macro works just fine but I'm not sure it's healthy that the sheet's index getting so big (I assume so at least).

Any idea how to set the index to 0?

Here is the code that deletes each iteration active worksheets.

Please Login or Register  to view this content.

[nathansav]

Do you need L defined?

Please Login or Register  to view this content.

[Norie]

What index are you referring to?

[GIS2013]

@Norie to your question, nathan:

This is the "core" of the code, where P column is a result of remove duplicates operation, and for each unique value in column P after the removal a new worksheet should be created, and named.

The delete macro (in my first post) just needs to "clear" after closing.

***As I'm sure Norie at least knows by heart LIN is just the name of a worksheet***

Please Login or Register  to view this content.

[nathansav]

The code i have supplied, is triggered on adding a new sheet to Excel workbook, so just needs to be put in the thisworkbook code module, when you add a sheet, if there are 3 already then it will be called sheet(3+1)=sheet4.

[GIS2013]

@nathan, could you tweak my code in accordance? I'm a bit confused how it would not be problematic for me as I need the new sheets' names to be P values.

For example if P column contains P2:P7 = 100:105, then need to create 6 new worksheets, named 100,101,...,105.

[Norie]

Why not create a reference to the worksheets as you add them?

Please Login or Register  to view this content.

[GIS2013]

Thanks Norie: your code is simpler than mine .

Results: when I open the workbook and run the "fixed" "core" code the sheets' count is from 0 (sheet 1,..,sheetX), actually X is the value of the last cell in column P (after removing duplicates).

But then after using the delete macro (first post here) and then the "core" again, in the porject window I see that the sheets count begins in sheetX+1.

Nevertheless closing the workbook sets the count to 0. And since I assume the end-user will close the workbook from time to time, I hope the index is not going to go hysterical.