resequence my worksheets

[terilad]

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

 

[FreeBird]

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)
' (e-mail address removed)
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.CodeName).name
varSheetCompName
End Sub

HTH