Sure - like this:
Sub SaveAs_New_XLSM_Workbook()
'Save as xlsm file using the reference cell value
Dim wsA As Worksheet
Dim wbA As Workbook
Dim strName As String
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant
On Error GoTo errHandler
Set wbA = ActiveWorkbook
Set wsA = ActiveSheet
'Get active workbook folder
strPath = wbA.Path
If strPath = "" Then
strPath = Application.DefaultFilePath
End If
' make sure path ends with the appropriate separator
If Right$(strPath, 1) <> Application.PathSeparator Then strPath = strPath & Application.pathseparatorstrName = wsA.Range("B13").Value
'Create default name and save for file
strFile = strName & ".Model.xlsm"
strPathFile = strPath & strFile
ActiveWorkbook.SaveAs Filename:=strFile, FileFormat:=xlOpenXMLWorkbookMacroEnabled
'Confirmation message with file info
MsgBox "Hello Rob, " & cbCrLf _
& vbCrLf & vbCrLf & _
"This workbook has been saved as a customer model: " _
& vbCrLf & vbCrLf & _
strPathFile
ExitHandler:
Exit Sub
errHandler:
MsgBox "Could not save file"
Resume ExitHandler
End Sub
Display More
Thanks Rory, I tried your code and it kept failing on my Mac. When I stepped through it, it appeared to be failing on the "If Right$" line (I think from the Then part).
I've tried a few things today and got the following code to work on my Mac, using FileFormat:=53. I then changed this to FileFormat:=52 for the users windows pc but it still failed to save when he ran it! There is no doubt a simple fix to this but it is beyond my beginner knowledge!
Woz.
Sub SaveAs_New_XLSM_Workbook()
'Save as xlsm in same folder using the reference cell value
Dim wsA As Worksheet
Dim wbA As Workbook
Dim strName As String
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant
On Error GoTo errHandler
Set wbA = ActiveWorkbook
Set wsA = ActiveSheet
'Get active workbook folder
strPath = wbA.Path
If strPath = "" Then
strPath = Application.DefaultFilePath
End If
strPath = strPath & "\"
strName = wsA.Range("B13").Value
'Create file name and save
strFile = strName & "_Model"
strPathFile = strPath & strFile
'__________________________________________________________________
ActiveWorkbook.SaveAs filename:=strFile, FileFormat:=53
'Nb
'On a Mac: FileFormat:=53 = xlOpenXMLWorkbookMacroEnabled = xlsm
'In Windows: It's FileFormat:=52 as the number is +1 for a Mac!
'__________________________________________________________________
'Confirmation message with file info
MsgBox "Hello Rob, " & vbCrLf _
& vbCrLf & vbCrLf & _
"This workbook has been saved as a customer model: " _
& vbCrLf & vbCrLf & _
strPathFile
ExitHandler:
Exit Sub
Display More