ERLANDSEN DATA CONSULTING Excel & VBA Tips   Informasjon på norsk / Information in Norwegian

These pages are no longer updated and are only available for archive purposes.

Click here to visit the pages with updated information.

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

 

Erlandsen Data Consulting     http://www.erlandsendata.no/   
Excel & VBA Tips   Copyright ©1999-2017    Ole P. Erlandsen   All rights reserved
E-mail Contact Address