create named worksheet based on existing numeration
 

Greetings all.
Hope your respective weekends were enjoyable.
I have recorded, and modified a macro that creates a worksheet within an
existing workbook. For now, I've just set the name of the worksheet to be "A."
I would like to now have this new worksheet named within the existing
framework of the workbook.
I.e., for this type of worksheet, we typically name them by a ordinal
number-- 1 through N.
In one workbook we may only be up to 5, and another could easily be upwards
of 70, or more.
Presently, the code for my worksheet name is:

Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = "A"

What would I use to make the name of the worksheet the next number inline
for the respective worksheet?
I appreciate your responses.
Best.

create named worksheet based on existing numeration-- addendum
 

Sometrhing I forgot to add.
I've tried

Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = sheets.count

And

Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = sheets.count+1

Only to realize that this also counts the hidden worksheets.
I've like to have it not count the hidden worksheets.

Again, thank you.

create named worksheet based on existing numeration
 

Hi
If the first worksheet is "A1", the second "A2" and so on then use

Sheets(Sheets.Count).Name = "A"&Sheets.Count

If there are other worksheets in the workbook without this naming
structure then you will have to keep track of the last sheet number
and any deletion of numbered sheets. Could be a bit tricky in that
case, so get back if that is what you need.

regards
Paul

On Feb 25, 3:29*pm, SteveDB1
wrote:
Greetings all.
Hope your respective weekends were enjoyable.
I have recorded, and modified a macro that creates a worksheet within an
existing workbook. For now, I've just set the name of the worksheet to be "A."
I would like to now have this new worksheet named within the existing
framework of the workbook.
I.e., for this type of worksheet, we typically name them by a ordinal
number-- 1 through N.
In one workbook we may only be up to 5, and another could easily be upwards
of 70, or more.
Presently, the code for my worksheet name is:

Sheets.Add After:=Sheets(Sheets.Count)
* Sheets(Sheets.Count).Name = "A"

What would I use to make the name of the worksheet the next number inline
for the respective worksheet?
I appreciate your responses.
Best.

create named worksheet based on existing numeration
 

Hi SteveDB1,

There are a number of topics on this website if you do a search for
"count specific sheets"

However if ALL your sheets are numbered 1,2 ,3 4, etc then your next
sheet will be Sheet.Count + 1 if you do the count before creating the
new sheet, or simply Sheet.Count if after.

If you have other types of sheet names you might want to set up a
'counter & loop thru all the sheets testing to see if the name =
integer (you will have to convert the name to an integer as sheet
names are strings)

Hope this helps. Repost if you get suck

Regards

Michael.