Adding, naming Worksheets sequentially

[Sol Apache]

I have workbooks with worksheets numbered sequentially 1, 2, 3 etc. -
sometimes over 100 worksheets.

I'd like to be able to add a worksheet, put it at the end, and name it last
name + 1, so if the last old worksheet was named 50, the new one will be 51.

I could not find any help in VB on this.

Thanks for any help




Sol

 

[Guest]

Alt + F11 and insert a new module and paste this in and it will create your
sheet sequentially.

Sub addatend()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
last = ws.Name
Next ws
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = last + 1
End Sub

Mike

 

[Incidental]

You could try something like this also though it's not as nice as
Mikes version

Option Explicit
Dim Ws As Worksheet
Dim i, WsCnt As Integer

Private Sub CommandButton1_Click() ' Rename all existing sheets
i = 1
For Each Ws In Worksheets
Ws.Name = "test" & i
i = i + 1
Next Ws
End Sub

Private Sub CommandButton2_Click() 'Add sheet to end and name it
WsCnt = Worksheets.Count
Sheets.Add After:=Sheets("test" & WsCnt)
ActiveSheet.Name = "test" & WsCnt + 1
End Sub

S

 

[Chip Pearson]

Try something like

With ThisWorkbook.Worksheets
.Add(after:=.Item(.Count)).Name = CInt(.Item(.Count).Name) + 1
End With


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email on the web site)

 

[Sol Apache]

Belated but many thanks for these macros - Incidental and Mike

Sorry, got involved in something else which has taken up all my time.

Mike's macro works instantly. Still figuring out Incidental's, and learning
more about Excel.

Sol