Copying VBA Modules

[Rusty_The_Kid]

I am relatively new to Excel VBA, and I began using AI to get started this morning, but I figure you all have AI out-gunned. I want to use VBA code to copy two existing modules, module1 and module1, within my workbook, and create two new duplicate modules, except or course with the next two available higher module numbers; perhaps module13 and module14, but maybe higher module numbers depending on how many modules exist in my workbook at the time. Below is the VBA code I tried running:

Sub CopyModules()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim i As Integer
    Set wb = ThisWorkbook
    Set ws = wb.Sheets(wb.Sheets("Auto-Cloning").Range("B3").Value)
    For i = 1 To 2
        wb.VBProject.VBComponents("Module" & i).Export "C:\Temp\Module" & i & ".bas"
        wb.VBProject.VBComponents.Import "C:\Temp\Module" & i & ".bas"
        wb.VBProject.VBComponents("Module" & i + 2).Name = "Module" & i + 4
    Next i
End Sub

I get "Run-time error '50035': Method 'Export' of object '_VBComponent' failed, with the "wb.VBProject.VBComponents("Module" & i).Export "C:\Temp\Module" & i & ".bas"" line highlighted.

I've already enabled all macros and checked the box next to "Trust access to the VBA project object model".

Thanks in advance!

 

[Rusty_The_Kid]

I thought that I would try a different approach of creating a new module, then copy everything from module1 into the new module. I thought that way the code wouldn't need to determine what the highest numbered module was. I tried the following code:

Sub CopyModule()
    Dim NewModule As Object
    Dim OldModule As Object
    Set OldModule = ThisWorkbook.VBProject.VBComponents("Module1")
    Set NewModule = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_StdModule)
    NewModule.Name = "NewModule"
    NewModule.CodeModule.AddFromString OldModule.CodeModule.Lines(1, OldModule.CodeModule.CountOfLines)
End Sub

I got "Run-time error '440': Method 'Add' of object '_VBComponents' failed, with the following line of code highlighted: Set NewModule = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_StdModule)

 

[MARK858]

I've already enabled all macros and checked the box next to "Trust access to the VBA project object model"

And added the reference to Microsoft Visual Basic for Applications Extensibility 5.3?

 

[Rusty_The_Kid]

That did it! I opened up the VBA Editor, Tools, References, and selected Microsoft Visual Basic for Application Extensibility 5.3, OK

And, the code created a new module titled "NewModule", and copied all of the Module1 contents into it.

Thanks much! I didn't even know such references existed. I suppose I will mark this as solved, and start new threads as I run into my upcoming roadblocks during this VBA project.

 

[MARK858]

You're welcome. It might be worth you having a read of the article in the link below written by Chip Pearson

Programming In The VBA Editor

www.cpearson.com

 

[Rusty_The_Kid]

Great! I will definitely read the article. I sure appreciate the help and patience offered to us newbies who may have a tendency to dive-into VBA programming, without prior associated classes or research! You facilitate important efficiency.