Move all modules to a single module

[Excelpromax123]

Hello everyone. My file has a lot of Modules I want to move to a single Module. I need the Code to run automatically (I'm currently using Cut Paste over 40 Moudles which is very time consuming). Thank you
As the example shown below: Move all the code content of 3 Moudle 1,2,3 to Moudle Home. After moving, delete modules 1,2,3. The file only has 1 Module left. The only one is Home

As the example shown below: Move all the code content of 3 Moudle 1,2,3 to Moudle Home. After moving, delete modules 1,2,3. The file only has 1 Module left. The only one is Home

 

[JEC]

Here it’s working fine.
The if statement is really important. Also the use of an empty module from where you run this code.
Make sure that module will not be deleted.

Try running step by step with F8 and see what happens

 

[navic]

Move all the code content of 3 Moudle 1,2,3 to Moudle Home. After moving, delete modules 1,2,3. The file only has 1 Module left. The only one is Home

Based on VBA code provided by @JEC, try the following idea (maybe it will help)

In the first step, turn ON "Trust Location"
File -> Options -> Trust Center -> Trust Center Settings...-> Macro Settings (Left menu) -> 'Check ON' "Trust access to the vba project object model"

Trust access to the vba project object model

Hi I am reading about macro settings and found this link about the option [] trust access to the vba project Mine is not check and I ran many small macros without problem so far. I read what is explained below but did not understand it well. Could you please explain to me why I need this...

www.mrexcel.com

NOTE! - I think this is a potential security risk, so 'turn it OFF' after the job is done.

In the VBE (Alt+F11) - > Tools -> References -> 'Check ON' next
- "Microsoft Scripting Runtime"
and
- "Microsoft Visual Basic for Applications Extensibility 5.3"

This two VBA codes copy to the 'TEST' Module

Copy VBA code from all modules to the "Test" module.

'Option Explicit
Sub CopyCodeToTestModule()
' copy VBA code from all module to TEST module
 For Each objMdl In ThisWorkbook.VBProject.VBComponents  'loop through VBcomponents
    If objMdl.Type = 1 And objMdl.Name <> "TEST" Then 'only copy if it's a module and not named as "Test"
       With ThisWorkbook.VBProject.VBComponents("TEST") 'destination of all macro's
          .CodeModule.AddFromString objMdl.CodeModule.Lines(1, objMdl.CodeModule.CountOfLines) 'copy all lines from a module to the "test" module
       End With
    End If
 Next
End Sub

Remove all modules except the 'TEST' module.

Sub DeleteModulesExcept()
'http://www.cpearson.com/excel/vbe.aspx
   
    Dim VBProj As VBIDE.VBProject
    Dim VBComp As VBIDE.VBComponent
     
    Set VBProj = ActiveWorkbook.VBProject
     
    For Each VBComp In ActiveWorkbook.VBProject.VBComponents
        Debug.Print "Checking " & VBComp.Name
        If VBComp.Type = vbext_ct_StdModule Then
            'If VBComp.Name = "Dont_Delete" Or VBComp.Name = "Module2" Then 'delete all except this modules
            If VBComp.Name = "TEST" Then 'delete all except this module
            Else
                Debug.Print "Removing " & VBComp.Name
                VBProj.VBComponents.Remove VBComp
            End If
        End If
    Next
End Sub

Do the following: (Perform these actions separately in the specified order)

1. Run CopyCodeToTestModule
2. Run DeleteModulesExcept

Note! Pay attention to "Option Explicit". Only one may be in one module.

After the job is done, delete these two VBA codes from the TEST module, then turn OFF "Trust access to the vba project object model".

PS.
I'm not sure if everything is according to Excel rules, but this works for me.

 

[Excelpromax123]

Based on VBA code provided by @JEC, try the following idea (maybe it will help)

In the first step, turn ON "Trust Location"
File -> Options -> Trust Center -> Trust Center Settings...-> Macro Settings (Left menu) -> 'Check ON' "Trust access to the vba project object model"

Trust access to the vba project object model

Hi I am reading about macro settings and found this link about the option [] trust access to the vba project Mine is not check and I ran many small macros without problem so far. I read what is explained below but did not understand it well. Could you please explain to me why I need this...

www.mrexcel.com

NOTE! - I think this is a potential security risk, so 'turn it OFF' after the job is done.

In the VBE (Alt+F11) - > Tools -> References -> 'Check ON' next
- "Microsoft Scripting Runtime"
and
- "Microsoft Visual Basic for Applications Extensibility 5.3"

This two VBA codes copy to the 'TEST' Module

Copy VBA code from all modules to the "Test" module.

'Option Explicit
Sub CopyCodeToTestModule()
' copy VBA code from all module to TEST module
 For Each objMdl In ThisWorkbook.VBProject.VBComponents  'loop through VBcomponents
    If objMdl.Type = 1 And objMdl.Name <> "TEST" Then 'only copy if it's a module and not named as "Test"
       With ThisWorkbook.VBProject.VBComponents("TEST") 'destination of all macro's
          .CodeModule.AddFromString objMdl.CodeModule.Lines(1, objMdl.CodeModule.CountOfLines) 'copy all lines from a module to the "test" module
       End With
    End If
 Next
End Sub

Remove all modules except the 'TEST' module.

Sub DeleteModulesExcept()
'http://www.cpearson.com/excel/vbe.aspx
  
    Dim VBProj As VBIDE.VBProject
    Dim VBComp As VBIDE.VBComponent
    
    Set VBProj = ActiveWorkbook.VBProject
    
    For Each VBComp In ActiveWorkbook.VBProject.VBComponents
        Debug.Print "Checking " & VBComp.Name
        If VBComp.Type = vbext_ct_StdModule Then
            'If VBComp.Name = "Dont_Delete" Or VBComp.Name = "Module2" Then 'delete all except this modules
            If VBComp.Name = "TEST" Then 'delete all except this module
            Else
                Debug.Print "Removing " & VBComp.Name
                VBProj.VBComponents.Remove VBComp
            End If
        End If
    Next
End Sub

Do the following: (Perform these actions separately in the specified order)

1. Run CopyCodeToTestModule
2. Run DeleteModulesExcept

Note! Pay attention to "Option Explicit". Only one may be in one module.

After the job is done, delete these two VBA codes from the TEST module, then turn OFF "Trust access to the vba project object model".

PS.
I'm not sure if everything is according to Excel rules, but this works for me.

Thank you