Save as copy active workbook with all macro

[Nisshaheen]

Hello
I need to save as copy the active workbook with all macro and all subroutines in tab (this workbook) as xlsm file

I do it but the macro create new blank file with all my macro

Did have solution on my problem
Wait your reply

If you need my file will attached it soon

 

[Joe4]

Welcome to the Board!

If you turn on your Macro Recorder, and record yourself doing this manually, it will give you the VBA code that you need.

 

[Nisshaheen]

thanks joe4 for your reply
i'm not use record macro
i create macro to copy all procedure in active my file
to best explain my problem will attached my file

sorry i can't attached my file the button for attached not include

 

[Joe4]

Recording a macro is very easy, and will actually record the steps you perform. So if you record yourself saving the file as a Macro Enabled Excel file, you will have the code you need.
See here for more: https://trumpexcel.com/record-macro-vba/

to best explain my problem will attached my file

You cannot upload files to this site. But there are tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html. I am note sure how much that would help anyway though, if you just want to save the file as a Macro Enabled VBA file.

I have a feeling that there is more to this question that you may not be telling us. Is it really as simple as saving the file as a Macro Enabled Workbook, or is there some other important details? Please explain in detail.

 

[Nisshaheen]

joe4 thanks

this my code

can any one help me to edit this code copy original file alternative create new file

Option Explicit

Public Sub CopyComponentsModules() 'copies sheets/Thisworkbook/Userforms/Modules/Classes to a new workbook
Dim src As CodeModule, dest As CodeModule
Dim i&
Dim WB_Dest As Workbook
Dim Ref As Reference
Dim Comp As VBComponent
Dim sht As Worksheet


Debug.Print "Starting"
Set WB_Dest = Application.Workbooks.Add
On Error Resume Next 'needed for testing if component already exists in destination WorkBook and for cross-references
For Each Comp In ThisWorkbook.VBProject.VBComponents
Debug.Print Comp.Name & " - "; Comp.Type
Err.Clear
'Set Source code module
Set src = Comp.CodeModule 'ThisWorkbook.VBProject.VBComponents("Sheet1").CodeModule


'Test if destination component exists first
i = 0
i = Len(WB_Dest.VBProject.VBComponents(Comp.Name).Name)
If i <> 0 Then 'or: if err=0 then
Set dest = WB_Dest.VBProject.VBComponents(Comp.Name).CodeModule
Else 'create component
Err.Clear
If Comp.Type = 100 Then
Set sht = WB_Dest.Sheets.Add
Set dest = WB_Dest.VBProject.VBComponents(sht.Name).CodeModule
WB_Dest.VBProject.VBComponents(sht.Name).Name = Comp.Name
sht.Name = Comp.Name
Else
With WB_Dest.VBProject.VBComponents.Add(Comp.Type)
If Err.Number <> 0 Then
MsgBox "Error: Component " & Comp.Name & vbCrLf & Err.Description
Else
.Name = Comp.Name
Set dest = .CodeModule
End If
End With
End If
End If


If Err.Number = 0 Then
'copy module/Form/Sheet/Class 's code:
dest.DeleteLines 1, dest.CountOfLines
dest.AddFromString src.Lines(1, src.CountOfLines)
End If
Next Comp


'Add references as well :
For Each Ref In ThisWorkbook.VBProject.References
WB_Dest.VBProject.References.AddFromFile Ref.FullPath
Next Ref


Err.Clear: On Error GoTo 0


Set Ref = Nothing
Set src = Nothing
Set dest = Nothing
Set Comp = Nothing
Set WB_Dest = Nothing
End Sub