» Change the worksheet codemodule names using VBA in Microsoft Excel

VBA macro tip contributed by Erlandsen Data Consulting offering Microsoft Excel Application development, template customization, support and training solutions 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