create named worksheet based on existing numeration

[SteveDB1]

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.

 

[SteveDB1]

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.

 

[paul.robinson]

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

 

[michael.beckinsale]

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.

 

[Tim Williams]

How about working backwards, looking for the last sheet, and then adding the
new one after that ?

Tim

'****************************************
Sub Tester()
Dim s As Worksheet, i As Integer
For i = 1 To 10
Set s = ActiveWorkbook.Sheets.Add
NameAndPos s
Next i
End Sub

Sub NameAndPos(sht As Worksheet)

Dim i As Integer, i2 As Integer
i2 = 1
For i = 100 To 1 Step -1
If SheetExists(sht.Parent, CStr(i)) Then
i2 = i + 1
Exit For
End If
Next i

With sht
.Name = CStr(i2)
If i2 <> 1 Then .Move After:=sht.Parent.Sheets(CStr(i))
End With

End Sub

Function SheetExists(wb As Excel.Workbook, s As String)
Dim o As Worksheet
On Error Resume Next
Set o = wb.Sheets(s)
On Error GoTo 0
SheetExists = Not o Is Nothing
End Function
'************************************