Anthony's VBA Forum Index Anthony's VBA Forum
Where the knowledge is shared
Menu
 Anthony's VBA Forum IndexHome Page
 Anthony's VBA Forum IndexForum Index
FAQFAQ
MemberlistMemberlist
UsergroupsUsergroups
RegisterRegister
ProfileProfile
Log in to check your private messagesMessages
Log inLogin/Out

Quick Search

Advanced Search

Links
Consulting
Products

Who's Online
[ Administrator ]
[ Moderator ]


Using String Manipulation change wroksheet name

 
Post new topic   Reply to topic     Anthony's VBA Forum Index -> General Excel VBA
View previous topic :: View next topic  
Author Message
jc_bowyer



Joined: 04 Dec 2008
Posts: 1

PostPosted: Thu Dec 04, 2008 4:19 pm    Post subject: Using String Manipulation change wroksheet name Reply with quote

hi, i have a worksheet called 'week 1' and want to write vba code so that a new sheet called 'week 2' can be created. However i want to write code so that it doesn't matter what sheet i am on, a new sheet would be created with the name week and the next number up. ie. the last worksheet is 'week 7' then the code should be able to create a new worksheet called 'week 8' ect. anyone know how i could do this???

Cheers
Back to top
View user's profile Send private message
Kenneth Hobson
Gold Member
Gold Member


Joined: 25 Mar 2007
Posts: 21
Location: Tecumseh, OK

PostPosted: Fri Dec 05, 2008 11:14 am    Post subject: Reply with quote

This won't work for standard Sheet names as Excel keeps its own index. Be sure to include the space character as I did if needed for the suffix name.

[code]Sub AddNewSheet()
Worksheets.Add After:=Worksheets(Worksheets.Count), Type:=xlWorksheet
ActiveSheet.Name = "week " & NextSheetSuffixNumber("week ")
End Sub

Function NextSheetSuffixNumber(sPrefix As String) As Integer
Dim sht As Worksheet
Dim lastNum As Integer, nextNum As Integer
On Error Resume Next
lastNum = 0
nextNum = 0
For Each sht In Worksheets
If Left(sht.Name, Len(sPrefix)) = sPrefix Then
lastNum = CInt(Right(sht.Name, Len(sht.Name) - Len(sPrefix)))
If lastNum > nextNum Then nextNum = lastNum
End If
Next sht
NextSheetSuffixNumber = lastNum + 1
End Function
[/code]
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic     Anthony's VBA Forum Index -> General Excel VBA All times are GMT - 4 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2002 phpBB Group