resequence my worksheets
 

Hi,

I have over 100 worksheets in a workbook, through time they have been
reordered about and are now in order alphabetically but the sheet numbers are
all over the place, can I resequence the sheet numbers to sheet1, sheet2 and
so on by the order the sheets are in my workbook, keeping the sheets
alphabetically but change the default sheet numbers to 1, 2, 3, 4 and so on.

Any help would be great.

thanks

Mark

resequence my worksheets
 

Hello

If i did catch well, you want to rename the "VBA" name of sheets.
I wrote this below for personal use.
You can simplify it, the important codeline is the last one.


Sub vbp_SheetCodeName_DEMO()
Call vbp_SheetCodeName(ActiveSheet, "test") ', Workbooks("Classeur1"))
End Sub


Sub vbp_SheetCodeName(varSheet As Variant, varSheetCompName As String,
Optional varWorkbook As Variant)
'
If TypeName(varSheet) = "string" Then
Set varSheet = ActiveWorkbook.Sheets(varSheet)
End If

If Not IsMissing(varWorkbook) And TypeName(varWorkbook) = "string"
Then
Set varWorkbook = Workbooks(varWorkbook)
ElseIf IsMissing(varWorkbook) = True Then
Set varWorkbook = ActiveWorkbook
End If

' the important line :
varWorkbook.VBProject.VBComponents(varSheet.CodeNa me).name =
varSheetCompName
End Sub

HTH


--
FreeBird
------------------------------------------------------------------------
FreeBird's Profile: 1701
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=193483

http://www.thecodecage.com/forumz