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

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

"Sol Apache" wrote:

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

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

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" wrote in message
...
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

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


On 14/3/07 13:15, in article
, "Incidental"
wrote:

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