Transfer some modules from one workbook to another one

[Doflamingo]

Hi all,

I would like to transfer some moduls from a opened workbook to another one

Here is the code to create a new workbook ‘’workbook2’’ from an active worksheet of an other workbook ‘’workbook 1’’

Does someone know how to transfer the ‘’modul1’’ and the ‘’modul2’’ from the workbook1 to the workbook2

I found this on the forum

Public Sub CopyModule()    Const strModName As String = "C:\TempMod.BAS"        ActiveWorkbook.VBProject.VBComponents("MyModule").Export fielname:=strModName    Workbooks(2).VBProject.VBComponents.Import Filename:=strmodnamme    Kill strModNameEnd Sub

[h=2]From that thread Copy a code module from one workbook to another[/h]

but I tried to adapt it with my code and that does not work -lines of code in red-

Sub sb_Copy_Save_ActiveSheet_As_Workbook()
  Dim wname As String, wfolder1 As String, wfolder2 As String
  wname = ActiveSheet.Name
  wfolder1 = "C:\Users\AtivBook9\Downloads\Reims\"
 
  If Right(wfolder1, 1) <> "\" Then wfolder1 = wfolder1 & "\"
  If Dir(wfolder1, vbDirectory) = "" Then
    MsgBox "The folder does not exist! " & wfolder1, vbCritical
    Exit Sub
  End If
  wfolder2 = wfolder1 & wname & "\"
  If Dir(wfolder2, vbDirectory) = "" Then
    MkDir wfolder2
  End If
 
Range("E5").Value = wfolder2
 
  ActiveSheet.Copy
  ActiveWorkbook.SaveAs wfolder2 & wname & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled
 
 
  [COLOR=#ff0000]Const strModName As String = wfolder2 & wname & ".xlsm"
   
    ActiveWorkbook.VBProject.VBComponents("Module3").Export fielname:=strModName
    Workbooks(2).VBProject.VBComponents.Import FileName:=strmodnamme
    Kill strModName[/COLOR]
 
End Sub

Any idea ?

 

[mikerickson]

The const line won't compile, because constants can be built from sub strings change that to

Dim strModName As String
strModName = wfolder2 & wname & ".xlsm"

The next issue that I see is that the component that you are exporting comes from the ActiveWorkbook. This comes right after the ActiveSheet.Copy so the ActiveWorkbook is the new workbook that has no modules.
I also notice that the file name you chose for your exported module is the same as the name of the newly saved workbook. The OS won't like two files with the same name in the same place.

I'd try something like

Dim sourceBook as Worbook, newBook as workboook
Dim strModName As String 

strModName = wfolder2 & wname 
Set sourceBook = ActiveWorkbook

ActiveSheet.Copy
ActiveWorkbook.SaveAs wfolder2 & wname & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled

Set newBook = ActiveWorkbook
 
sourceBook.VBProject.VBComponents("Module3").Export fielname:= strModName

newBook.VBProject.VBComponents.Import FileName:=strmodnamme