From: terilad on

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.


From: FreeBird on


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)
' renascence(a)
If TypeName(varSheet) = "string" Then
Set varSheet = ActiveWorkbook.Sheets(varSheet)
End If

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

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


FreeBird's Profile: 1701
View this thread: