Next in Series

[Howard]

Running Excel 2000
I have three worksheets: 2001, 2002 and 2003. I can add a
new worksheet to the end of those worksheets by using
Worksheets.Add Worksheets(Worksheets.Count). Is there a
way to automatically name it with the next year in the
series (2004) at the same time that I add it?

Thanks,
HF

 

[Leo Heuser]

Howard

Here's one way to do it:

Sub AddSheet()
'Leo Heuser, 20 Oct. 2003
Dim LastSheet As Worksheet
Dim SheetName As String

With ActiveWorkbook
Set LastSheet = .Worksheets(.Worksheets.Count)
SheetName = LastSheet.Name
.Worksheets.Add after:=LastSheet
ActiveSheet.Name = LastSheet.Name + 1
End With
End Sub


--
Best Regards
Leo Heuser
Excel MVP

Followup to newsgroup only please.

 

[Bernie Deitrick]

Howard,

Worksheets.Add After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = CStr(CInt(Worksheets(Worksheets.Count - 1).Name) +
1)

HTH,
Bernie

 

[Howard]

Thanks to all three of you for your replies. Now I have
some options!
HF

 

[Dana DeLouis]

Just another similar idea:

Sub Demo()
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name =
Worksheets(Worksheets.Count).Name + 1
End Sub

Using "Sheets" may also work for you:

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

HTH

 

[Dana DeLouis]

And just another idea that doesn't use variables. (Assumes your sheet names
are numeric).

Sub Demo()
Sheets(Sheets.Count).Activate
Worksheets.Add , ActiveSheet
ActiveSheet.Name = ActiveSheet.Previous.Name + 1
End Sub

HTH.

 

[Dana DeLouis]

Ups! Make that two lines instead. :>)

Sub Demo()
Sheets(Sheets.Count).Activate
Worksheets.Add(, ActiveSheet).Name = ActiveSheet.Name + 1
End Sub

 

[Howard]

Thanks, again. Interesting how many ways there are to do
something.
HF