|
Anthony's VBA Forum Where the knowledge is shared |
|
|
View previous topic :: View next topic |
Author |
Message |
jc_bowyer
Joined: 04 Dec 2008 Posts: 1
|
Posted: Thu Dec 04, 2008 4:19 pm Post subject: Using String Manipulation change wroksheet name |
|
|
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 |
|
|
Kenneth Hobson Gold Member
Joined: 25 Mar 2007 Posts: 21 Location: Tecumseh, OK
|
Posted: Fri Dec 05, 2008 11:14 am Post subject: |
|
|
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 |
|
|
|
|
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
|