Copy code between excel modules (Microsoft Excel Objects)

August 29, 2023

How to access Microsoft Excel Objects by name not by Sheetname ?
I want to access in the code below by name for examle: "Akquise2024" and "Akquise2023",
but not by "Sheet1" and "Sheet10"

Please see this image

Sub CopyCodeBetweenModules()
    Dim sourceModule As Object
    Dim targetModule As Object
    Dim sourceCode As String
    
    ' Set the source and target module names
    Set sourceModule = ThisWorkbook.VBProject.VBComponents("Sheet10") '
    Set targetModule = ThisWorkbook.VBProject.VBComponents("Sheet1") '
    
    ' Get the source code from the source module
    sourceCode = sourceModule.CodeModule.Lines(1, sourceModule.CodeModule.CountOfLines)
    
    ' Add the source code to the target module
    targetModule.CodeModule.InsertLines targetModule.CodeModule.CountOfLines + 1, sourceCode
    
    MsgBox "Code copied successfully!"
End Sub

I can access the code by "Sheet10" and by "Sheet1"

    Set sourceModule = ThisWorkbook.VBProject.VBComponents("Sheet10") '
    Set targetModule = ThisWorkbook.VBProject.VBComponents("Sheet1") '

I can not acces the code by "Akquise2023" and "Akquise2024"

    Set sourceModule = ThisWorkbook.VBProject.VBComponents("Akquise2023") '
    Set targetModule = ThisWorkbook.VBProject.VBComponents("Akquise2024") '

>Solution :

You can get the codename from those worksheets like so:

Sub test()
    Dim sShStr As String, tShStr As String
    Dim sourceModule As Object
    Dim targetModule As Object
    sShStr = ThisWorkbook.Worksheets("Akquise2023").CodeName
    tShStr = ThisWorkbook.Worksheets("Akquise2024").CodeName
    'Then use it in your setting:
    Set sourceModule = ThisWorkbook.VBProject.VBComponents(sShStr) '
    Set targetModule = ThisWorkbook.VBProject.VBComponents(tShStr) '
End Sub

It’s what I used (well, I just used ActiveSheet.CodeName) for a similar issue.