Format 51

[billandrew]

Good Evening

Looking for some assist here. I am saving a workbook whichcontains several worksheets. When the new file is created there are two issues.

1. Message indicating to save the new file as an xlsx not amacro enabled file.

2. The new Worksheet the sheets have been grouped.

How can I avoid each of the above.

The code I am currently working with is:

Sub SaveE()
Dim Mypath As String
Dim Filename As String
Mypath = Environ("USERPROFILE") & "\Desktop\"
Filename = CreateObject("Scripting.FileSystemObject").GetBaseName(ActiveWorkbook.Name)
Sheets.Select
ActiveWorkbook.SaveAs Filename:=Mypath & " " & Filename & " " & ".xlsx", FileFormat:=51
End Sub

 

[MARK858]

What happens with

Sub SaveE()
Dim Mypath As String
Dim Filename As String
Mypath = Environ("USERPROFILE") & "\Desktop\"
Filename = CreateObject("Scripting.FileSystemObject").GetBaseName(ActiveWorkbook.Name)
'Sheets.Select
ActiveWorkbook.SaveAs Filename:=Mypath & " " & Filename & " " & ".xlsm", FileFormat:=52
End Sub

not sure why you have the & " " & in there?

 

[DanteAmor]

Try this

Sub SaveE()
  Dim Mypath As String
  Dim Filename As String
[COLOR=#0000ff]  Application.DisplayAlerts = False[/COLOR]
  Mypath = Environ("USERPROFILE") & "\Desktop\"
  Filename = CreateObject("Scripting.FileSystemObject").GetBaseName(ActiveWorkbook.Name)
  ActiveWorkbook.SaveAs Filename:=Mypath & " " & Filename & " " & ".xlsx", FileFormat:=51
End Sub

 

[MARK858]

1. Message indicating to save the new file as an xlsx not amacro enabled file

Fileformat 51 is for xlsx.

 

[billandrew]

The & " " & was used as a spacer in another code of mine, not relevant here.Thank You Mark works perfect.

 

[billandrew]

Thank You Dante as well.

 

[DanteAmor]

@billandrew, youre welcome.

 

[billandrew]

Hey guysCan you explain why the original file does not remain open?

 

[MARK858]

Your saving it as the new file name and extension not creating a new workbook so it is still open but renamed.

 

[billandrew]

Yes now I see.
I'll probably need to create a new workbook.