Next in Series

  • Thread starter Thread starter Howard
  • Start date Start date
H

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
 
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.
 
Howard,

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

HTH,
Bernie
 
Howard,

lastsheet = Sheets(Sheets.Count).name ' store the last sheet number
Set Newsheet = Worksheets.Add 'create a newsheet
Newsheet.name = lastsheet + 1 'rename it eg 2003 + 1

The above assumes you added them in order, if not you might consider reading
each sheet name and choosing the highest value before naming the next sheet
after the add

Cheers
Nigel
 
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
 
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.
 
Ups! Make that two lines instead. :>)

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