Save as .xlsm to .xlsx as values with conditions?

[navic]

I have a Workbook with multiple worksheets. Some worksheets contain formulas, some worksheets are protected but imported worksheets are not protected. The active workbook was named "Analyze.xlsm"

I want in the same folder the workbook 'Analyze.xlsm' save as 'Analyze-Month-Year-Date-Time.xlsx' under conditions.

These are the conditions

- When a user click on the 'Save' button on 'Quick Access Toolbar'
or
- When a user click on 'File' -> 'Save' or 'Save As'
or
- When a user click on 'CommandButton2_Click' on 'Helper' sheet

The VBA macros should do the following

1. Pass through all unprotected worksheets and set 'Locked' and 'Hidden' options to all cells in the range 'A1:KA500'
2. Set protection to each worksheet in the active workbook (the password should be defined in the VBA code).
3. The VBA should read the text in 'D1' cell (Month) on 'Helper' sheet.
4. The VBA should read the number 'E1' cell (Year) on 'Helper' sheet.
5. Activate the 'Save As' dialog window and set it up.
- new file name such as "Analyze-Month-Year-Date-Time" in .xlsx format (Date format = yyyy-mm-dd, Time format = hh:mm)
6. If the user clicks on the 'Cancel' button then 'Exit Sub' and returns to the active worksheet. (Is this a good plan, how the user can close the workbook and give up on saving?)

But there is an additional problem,
in 'Excel -> Options -> Trust Center settings' disabled the macros permission option (so must be). The "Disable all macros with notification" radio button has been checked. The folder in which the Workbook is located is "Tusted Locations". Also, I plan to run this VBA macro via CommandButton2_Click.

I've found several VBA macros, but I can not adapt them to my requirements.

First VBA

Sub SaveActiveXLSMWbkAsXLSX()
ThisWorkbook.Save   'Optional
Application.DisplayAlerts = False
ThisWorkbook.SaveAs ActiveWorkbook.Path & "\MyFileName - " & Format(Date, "yyyy-mm-dd"), 51 '51 = xlsx
Application.DisplayAlerts = True
'ThisWorkbook.Close  'Optional
End Sub

Second VBA

Option Explicit
Sub SaveAsName()
Dim save_as As Variant
Dim file_name As String
Dim ProgramName As String
file_name = ProgramName
    ' Get the file name.
    save_as = Application.GetSaveAsFilename(file_name, _
        FileFilter:="Excel Files,*.xlsx,All Files,*.*")
    ' See if the user canceled.
    If save_as = False Then Exit Sub
    ' Save the file with the new name.
    Application.DisplayAlerts = False
    If LCase$(Right$(save_as, 4)) <> ".xlsx" Then
        file_name = save_as & ".xlsx"
    End If
    ActiveWorkbook.SaveAs Filename:=save_as, FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False
End Sub

Is this possible or too complicated?
Can anyone adapt the code to one of the VBA macros or does anyone have a simpler solution?
or perhaps someone has a different advice?

I would appreciate if anyone could help me.

 

[mole999]

if you save the workbook as xlsx you remove the functional macros

 

[navic]

mole999, thank you for the warning

That I know it.
The goal is import and analysis of data,

and after that
- lock all cells in a defined range on all worksheets, (format cells: locked and hidden)
- protect all worksheets with a password (like "999")
- save all worksheets, ie Save As workbook without formula in .xlsx format
- send for inspection to the competent person

I do not need VBA macros in the final .xlsx workbook
This prevents data changes in the final Workbook (of course, for the average user of Excel?)

 

[navic]

This VBA macro saves xlsm in XLSX in the same folder. However, it saves WBK with formulas.
How do I add the command Save as values without formula?

CommandButton2 is located in helper sheet module

Private Sub CommandButton2_Click()
        Application.Run "Module1.Macro4" 'SaveAs XLSM wbk to XLSX without formulas
End Sub

VBA macro is located in Module1

Private Sub Macro4()
'Loop throught All Sheets and Copy-Paste data As Values And Save XLSM Wbk to XLSX
'Save to Active ThisWorkbook Path

Dim fname1 As String
Dim fname2 As String

'ThisWorkbook.Save   'Optional
    Application.DisplayAlerts = False
    
fname1 = Range("D1").Text 'from Helper sheet cell D1
fname2 = Range("E1").Text 'from Helper sheet cell E1

'Copy/PasteSpecial all sheets as values?

    ThisWorkbook.SaveAs "Analyze-" & fname1 & "-" & fname2 & "_" & Format(Now, "dd-mm-yyyy_hh-mm"), 51 
    
Application.DisplayAlerts = True

'ThisWorkbook.Close  'Optional
End Sub

Can anyone adapt this VBA macro?
I would appreciate it. Thanks in advance.

 

[navic]

How to merge these two macros to one?

I have two VBA macros that work properly, if I run them individually.
But the problem occurs when I call one after another by clicking 'CommandButton2_Click'.

When I click 'CommandButton2_Click', Excel saves the workbook but fails.
- Not saved in the same folder?
- Missing 'fname1' and 'fname2' in the name of the saved workbook? (This WorkbookPath)

This VBA macro works independently

Sub Macro4()
'Lock and Hidden formula in all cells on multiple sheets and Protect Multiple Sheets with password
    Dim wsWorksheet As Worksheet

'------------Optimize Macro Speed - Disable----------------------
Application.DisplayAlerts = False 
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
    
    For Each wsWorksheet In ActiveWorkbook.Worksheets

        wsWorksheet.Unprotect Password:="123"

        wsWorksheet.Range("A1:CZ500").Locked = True
        wsWorksheet.Range("A1:CZ500").FormulaHidden = True

        wsWorksheet.Protect Password:="123", AllowFiltering:=True, Contents:=True

    Next
'-------------Optimize Macro Speed - Enable------------------------
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.CutCopyMode = True 'clear clipboard
Application.Calculation = xlCalculationAutomatic

End Sub

This VBA macro works independently

Sub Macro5()
'Save data on all worksheets to values and save wbk to XLSX
Dim fname1 As String
Dim fname2 As String

'------------Optimize Macro Speed - Disable----------------------
Application.DisplayAlerts = False 'disable prompt popup warning
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

'ThisWorkbook.Save   'Optional
    Application.DisplayAlerts = False
    
fname1 = Range("D1").Text 'Sheet Helper Cell D1
fname2 = Range("E1").Text 'Sheet Helper Cell E1

Sheets("Helper").Unprotect Password:="123"

        ThisWorkbook.SaveAs "Analyze-" & fname1 & "-" & fname2 & "_" & Format(Now, "dd-mm-yyyy_hh-mm"), 51 'optional FileFormat:=51

    Application.DisplayAlerts = True

Sheets("Helper").Protect Password:="123", AllowFiltering:=True, Contents:=True
'ThisWorkbook.Close  'Optional

'-------------Optimize Macro Speed - Enable------------------------
Application.DisplayAlerts = True 'disable prompt popup warning
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.CutCopyMode = True 'clear clipboard
Application.Calculation = xlCalculationAutomatic 
End Sub

The result should be
Lock and Hidden formula in all cells on multiple sheets and Protect Multiple Sheets with password "123" and Save data on all worksheets to values and save wbk to XLSX without formulas

How to merge these two macros to one?