Save workbook into new workbook as values

[Vtookup]

Hi all.
I hope to get help with saving workbook into new workbook as values. In the same directory. With the same file name of the workbook and the current date as file name.
Thanks in advance for the help.

 

[Vtookup]

Thanks Alex for your input. just hoping that in one go, turn NEW WB into values without asking user for password (all cells without formula and protection). Sorry I have poor comprehension with macros. Really helpless. Maybe( ) works fine. but when the original file is protected. Macro stop and won't convert.

 

[Alex Blakenburg]

without asking user for password

If you don't want to ask the user for the password then replace this

pw = InputBox("Password Here")    '<---- Change required

with a line hard coding the password

pw = "Hard_Coded_Password_In_Quotes"   '<---- Change required

 

[Vtookup]

Hi Alex
We're talking about Post #18 right? Change
pw = InputBox("Password Here") '<---- Change required to
pw = "1234". my pw to all WS for this trial.
When tested on unprotected WS, the code run well. and all formula turn into values. Except all WS are PROTECTED. which is also okay.
When tested on Protected WS, the code stops and error 400.
Debugging stops at this code in my summary ws. Code for hidden picture that was assigned to clearcontents.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
Opinions?

 

[Alex Blakenburg]

Give this a try:
It will only protect sheets previously protected and turns EnableEvents Off/On

Sub Jolivanes_Mod()
    Dim newName As String, pw As String, wb As Workbook, sh As Worksheet
    Dim bHasPwd As Boolean
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    Set wb = ThisWorkbook
    pw = "123"    '<---- Change required
    newName = ThisWorkbook.Path & "\" & Left(wb.Name, InStrRev(wb.Name, ".") - 1) & Year(Now) & Format(Month(Now), "00") & Format(Day(Now), "00")     '& ".xlsx"    ', FileFormat:=51
    wb.Worksheets.Copy
    For Each sh In ActiveWorkbook.Worksheets
        bHasPwd = False
        If sh.ProtectContents Then bHasPwd = True
        sh.Unprotect pw
        sh.UsedRange.Value = sh.UsedRange.Value
        If bHasPwd Then sh.Protect pw
    Next sh
    With ActiveWorkbook
        .SaveAs newName & ".xlsx", 51
        .Close True
    End With
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True

End Sub

 

[Vtookup]

Alex,
LOOSEN UP. Everything is fine now.
just one small request, can we bypass the final message "To con't saving as macro-free WB -- Yes"
Jolivanes, Alex. You guys are the BEST.
Thank You for all your help.

 

[Vtookup]

Please ignore the saving new document as macro-free workbook without prompt request. It's done.
Now, I have to rewrite some of my work that deals with PDF.
Regards and Thanks again.

 

[Alex Blakenburg]

Glad you got it sorted and thanks for letting us know.