Next in Series
 

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

Next in Series
 

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" skrev i en meddelelse
...
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

Next in Series
 

Howard,

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

HTH,
Bernie


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

Next in Series
 

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

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




----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---

Next in Series
 

Thanks to all three of you for your replies. Now I have
some options!
HF
-----Original Message-----
Howard,

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

HTH,
Bernie


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


.

Next in Series
 

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
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


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

Next in Series
 

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
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


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

Next in Series
 

Ups! Make that two lines instead. :)

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

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"Dana DeLouis" wrote in message
...
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
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


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

Next in Series
 

Thanks, again. Interesting how many ways there are to do
something.
HF
-----Original Message-----
Ups! Make that two lines instead. :)

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

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"Dana DeLouis" wrote in message
...
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
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


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




.