Reset New Worksheet Numbering in Macro

[Colin_S]

I have a large macro doing multiple things only problem is that i can only run it once then have to shut down and open again if i want to run it again. The reason for this is that part of the macro creates new worksheets, and when the new worksheet is created it is automatically numbered as Sheet 1, Sheet 2 etc for a few new worksheets.

My problem is when i run it a second time the new work sheets will be created as the next number in the sequence.

i.e. the first round creates 2 new worksheets nameed Sheet 1 and Sheet 2, my macro references Sheet 1 and Sheet 2. How ever when i run it agian, it deletes Sheet1 and Sheet 2 and creates Sheet 3 and Sheet 4. My macro does not reference Sheet 3 and Sheet 4. You see my problem.

Everytime i shut excel down it resets the numbering, is there anyway to do this in my macro without shuting down excel?

[ReportMaker]

I have a large macro doing multiple things only problem is that i can only run it once then have to shut down and open again if i want to run it again. The reason for this is that part of the macro creates new worksheets, and when the new worksheet is created it is automatically numbered as Sheet 1, Sheet 2 etc for a few new worksheets.

My problem is when i run it a second time the new work sheets will be created as the next number in the sequence.

i.e. the first round creates 2 new worksheets nameed Sheet 1 and Sheet 2, my macro references Sheet 1 and Sheet 2. How ever when i run it agian, it deletes Sheet1 and Sheet 2 and creates Sheet 3 and Sheet 4. My macro does not reference Sheet 3 and Sheet 4. You see my problem.

Everytime i shut excel down it resets the numbering, is there anyway to do this in my macro without shuting down excel?

Assuming I understand you right, the simplest thing to do would be to give the sheets a name when they're created.

E.g. when you create the sheets:

Please Login or Register  to view this content.

Then instead of referencing Sheet1 and Sheet2, refer to Colin1 (or whatever you want to call it) and Colin2.

E.g.

Please Login or Register  to view this content.

etc

[Colin_S]

Thanks but the problem with that is that they are created from a pivot table i.e.

Please Login or Register  to view this content.

[royUK]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

[Colin_S]

Ok attached a dummy file. This is a very much cut down version of what i'm trying to do and the information on the detail page changes every time i run the full macro.

[royUK]

You will keep getting problems you are hard coding sheet names & ranges, which may change

I can't really follow what you are trying to do

[Colin_S]

I wouldn't normally hard code sheet names but the way you generate new sheets from a pivot table I can't see another option.

What I’m trying to achieve is that the first part of my macro pulls data from 6 different workbooks into one sheet. I have 9 people who own the data and need to do work on it so am using a pivot table to create a summary of the detail and then creating 8 new worksheets for each person.

It works fine the first time i open the file, and maybe that's how it will have to stay, but would prefer if there was some way to recent the automatic number of new worksheets.

[romperstomper]

Store activesheet in a worksheet variable immediately after it is created.

[Colin_S]

Thanks romperstomper, that's not what i did but it made me realise how easy it was to fix. I just renamed the active sheet when it is created, sorted!!!!

Cheers for everyone's help