Change the worksheet codemodule names

When you copy a worksheet in a workbook, it's codemodule gets a name like Sheet1, Sheet11, Sheet111, Sheet1111, Sheet11111 and so on. The macro below can be used to rename the codemodules using names like Sheet1, Sheet2, Sheet3, Sheet4, Sheet5 and so on.

Sub ChangeAllWorksheetCodenames()
' requires a reference to the Visual Basic Extensibility library
Dim ws As Worksheet, i As Integer
    If TypeName(ActiveWorkbook) = "Nothing" Then Exit Sub
    ' assign a temporary name to avoid naming conflicts
    i = 0
    For Each ws In ActiveWorkbook.Worksheets
        i = i + 1
        On Error Resume Next
        ws.Parent.VBProject.VBComponents(ws.CodeName).Properties("_CodeName") = _
            "fubar" & i
        On Error GoTo 0
    Next ws
    ' assign the proper name
    i = 0
    For Each ws In ActiveWorkbook.Worksheets
        i = i + 1
        On Error Resume Next
        ws.Parent.VBProject.VBComponents(ws.CodeName).Properties("_CodeName") = _
            "Sheet" & i
        On Error GoTo 0
    Next ws
    Set ws = Nothing
End Sub

Document last updated 1999-12-20 12:51:17 Printerfriendly version