vba save copy of workbook

[Kaps_mr2]

Hello,

I am trying to save a copy of the current workbook as a macro enabled workbook. However I get a wrong number of arguments or invalid property assignment on the line in Orange below. can anyone help pls ? thank you.

kind regards
Kaps

Public Sub create_reporting_workbook()
Dim File_name As String
Dim d As Integer
Dim sheet_name As String
Dim new_file_name As String

new_file_name = reporting_month & " " & reporting_year & ".xlsm"


template_wb.SaveCopyAs new_file_name, FileFormat:=xlOpenXMLWorkbookMacroEnabled

   
    Set reporting_wb = ActiveWorkbook
   
    reporting_wb.Sheets(1).Name = "Summary"

 

[Herakles]

Hello,

I am trying to save a copy of the current workbook as a macro enabled workbook. However I get a wrong number of arguments or invalid property assignment on the line in Orange below. can anyone help pls ? thank you.

kind regards
Kaps

Public Sub create_reporting_workbook()
Dim File_name As String
Dim d As Integer
Dim sheet_name As String
Dim new_file_name As String

new_file_name = reporting_month & " " & reporting_year & ".xlsm"


template_wb.SaveCopyAs new_file_name, FileFormat:=xlOpenXMLWorkbookMacroEnabled

  
    Set reporting_wb = ActiveWorkbook
  
    reporting_wb.Sheets(1).Name = "Summary"

What value does new_file_name have?

Is template_wb a valid workbook object and is the workbook referred to loaded?

 

[NdNoviceHlp]

A couple o things. You need to specify the whole path like...

"C:\Foldername\" & reporting_month & " " & reporting_year & ".xlsm"

with the path separator ("\") used betweeen drive, folder(s) and file name.
Also some characters can't be used in file names. I think a space maybe one of them. So you can use the following code to ensure that the file name is OK.

Public Function ValidFilePath(Arg As String) As String
Dim RegEx As Object
Set RegEx = CreateObject("VBScript.RegExp")
With RegEx
    .Pattern = "[\\/:\*\?""<>\|]"
    .Global = True
    ValidFilePath = .Replace(Arg, "_")
End With
Set RegEx = Nothing
End Function

To operate...

template_wb.SaveCopyAs ValidFilePath(new_file_name), FileFormat:=xlOpenXMLWorkbookMacroEnabled

HTH. Dave

 

[Domenic]

The SaveCopyAs method of the Workbook object contains only a single parameter, which specifies the filename. And the filename may or may not include the path. If it doesn't include the path, the workbook is saved in the current directory.

If your template workbook is already a macro-enabled workbook, you can simply do the following...

template_wb.SaveCopyAs new_file_name

If your template workbook is not a macro-enabled workbook, try the following instead...

    template_wb.Sheets.Copy
    
    With ActiveWorkbook
        .SaveAs Filename:=new_file_name, FileFormat:=xlOpenXMLWorkbookMacroEnabled
        .Close
    End With

Hope this helps!

 

[NdNoviceHlp]

Thanks Domenic for the new learning and clearing up my misinformation. My bad. Dave