New Worksheet with sequence number

[kamila1704]

Hi,

I have a problem to name correctly my new worksheet in order to obtain a sequence.
So my first worksheet' name is 3114
my macro is

Please Login or Register  to view this content.

And I don't know what I have to put after Name = ... to obtain the sequence, what means that the next worksheet is 3115, the next one 3116 etc.

I tried with Worksheet.Count but it puts the number of sheet starting from 1 ...
Thank you ,
Regards,

[zbor]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[ThirtyTwo]

try this

Please Login or Register  to view this content.

[Richard Buttrey]

Hi,

I'd be inclined to hold the next sheet number as a variable in a named cell. Enter 3115 in a cell and name the cell 'NextSheet'

Now use the following macro

Please Login or Register  to view this content.

[kamila1704]

It's working only for 3115, but I wanted the sequence 3115,3116 etc...

[ThirtyTwo]

use this code then

Please Login or Register  to view this content.

it will go find the highest value sheetname, create a copy, rename the new sheet with an increment

[kamila1704]

It's working ! Thanks !
My God it's too complicate this code ! I spent half of day to try this one...

[kamila1704]

I have another question... if in my worksheet 3114 was a cell name 1, if it's possible that in new created worksheet 3115 this cell change to 2 ?

I tried this, but it doesn't work

Please Login or Register  to view this content.

[ThirtyTwo]

think this will work

Please Login or Register  to view this content.

[Richard Buttrey]

Hi,

Are you sure you have named a cell somewhere "NextSheet" and populated it with the initial value of 3115.
When I do that and run the macro, each time its run I see a new sheet, 3115, 3116, 3117 etc..

Please Login or Register  to view this content.

But let me ask a more fundamental question. What are you going to use the information on the sheet for. At some stage are you going to want to know say the totals for many sheets, or be able to select specific records from several sheets? In which case I would suggest you capture your data on a single sheet but with a new column added that contains the 3114, 3115 reference on each record row.

This will allow you to build a proper database that will be far more useful and allow you to use the powerful pivot table fiunctionality.

[kamila1704]

Not really ... it gives the numbers : 1, 3, 5 and in one click it creates two worksheets

[ThirtyTwo]

I have another question... if in my worksheet 3114 was a cell name 1, if it's possible that in new created worksheet 3115 this cell change to 2 ?

I tried this, but it doesn't work

Please Login or Register  to view this content.

ignore my latest reply, misunderstood your question
in the sheet you are copying, it will have data, 2 secs and i'll post some new code

[ThirtyTwo]

this does what you wanted now

Please Login or Register  to view this content.

[kamila1704]

I tried also this but with this method you can't create 3116, 3117 etc

Hi,

Are you sure you have named a cell somewhere "NextSheet" and populated it with the initial value of 3115.
When I do that and run the macro, each time its run I see a new sheet, 3115, 3116, 3117 etc..

Please Login or Register  to view this content.

But let me ask a more fundamental question. What are you going to use the information on the sheet for. At some stage are you going to want to know say the totals for many sheets, or be able to select specific records from several sheets? In which case I would suggest you capture your data on a single sheet but with a new column added that contains the 3114, 3115 reference on each record row.

This will allow you to build a proper database that will be far more useful and allow you to use the powerful pivot table fiunctionality.

[Richard Buttrey]

Not really ... it gives the numbers : 1, 3, 5 and in one click it creates two worksheets

Then I suspect you must be doing something different.

See attached.

Tell me what you see when you click the 'Run Sheet Add Macro' button

[kamila1704]

It's ALMOST working , just there is a little problem and I can't see it in your code.
In example my worksheet is 3152, when I make working my macro , it creates in the same time 3153 and 3154 and only 3154 change the number on the cell H2..

this does what you wanted now

Please Login or Register  to view this content.

[kamila1704]

Sorry !! It's working!! You are the best !!