Number of new worksheet

  Desert Andy 15:21 14 Dec 2006

I am writing a program in VBA in Excel, which allows the user to insert a new worksheet. Is there any way of knowing what the new sheet number will be if the user can insert a number of new sheets at any time. Obviously, once Excel is opened a new sheet starts as Sheet1, however I need to know after a number of new insertions what the sheet will be called.

Thanks for your help

  Terry Brown 15:32 14 Dec 2006

The best option would be to right click on the Sheet tab and rename it, so you would always know what it is.
You could then use a function like =sum(sheet name,!a10:!x70). Lok under Link in excel help for more information

  VoG II 15:32 14 Dec 2006

You can easily find out after adding a sheet

Sub tst2()
Sheets.Add after:=Worksheets(Worksheets.Count)
MsgBox Worksheets(Worksheets.Count).Name
End Sub

To find out before adding the sheet you would need to use


find the number at the end and replace with that number + 1.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

OnePlus 5 review

Alice Saey's mesmerising animation for Dutch singer Mark Lotterman

iPad Pro 10.5in (2017) review

Comment booster votre iPhone ?