Change the worksheet codemodule names
1999-12-20 VBE 0 199
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