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.

 

[jolivanes]

Sub Maybe()
Dim sh As Worksheet, wbName As String
wbName = ThisWorkbook.Name


    For Each sh In ThisWorkbook.Worksheets
        sh.UsedRange.Value = sh.UsedRange.Value
    Next sh


    Application.DisplayAlerts = False


        With ThisWorkbook
            .SaveAs Filename:=ThisWorkbook.Path & "\" & Left(wbName, InStrRev(wbName, ".") - 1) & Year(Now) & Format(Month(Now), "00") & Format(Day(Now), "00") & ".xlsx", FileFormat:=51
            .Close (False)
        End With


    ThisWorkbook.Close True


    Application.DisplayAlerts = True


End Sub

 

[Vtookup]

Hi jolivanes.
Thank you for your help.
My file have 25 sheets of identical invoice format. With vlookup for client names and details.
A summary sheet that 3d sum all sheets. finally to save another workbook as value for record-keeping (needed only this code).
next clearcontents all sheets for next day inputs. I tried your code and it failed here.
but with just one sheet. It works. what do you think? hoping to get help again. thanks.

 

[jolivanes]

Re: "next clearcontents all sheets for next day inputs. I tried your code and it failed here."
Can you elaborate on this.

BTW, a lot of people keep PDF files for record keeping.

 

[Vtookup]

Hi again.
I'm just explaining what this workbook is all about. no relation to clearcontents. the code I think failed because there's too much worksheets? Converting the whole workbook to Values is elusive online.
Actually some of my works create PDF files for record keeping but the files are too large that I omitted them. (...ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDF). Office resources are quite limited. instead I just teach them to screenshot . Excel files of this sort doesn't take too much space in your hard drive. Hope you get what I mean, Thanks again.

 

[jolivanes]

It does not matter how many sheets you have in the workbook.
Do you have any macros in the sheet and/or thisworkbook modules?
Which line was highlighted when the code failed?

Did you change anything in the code and if you did, what did you change?
If so, show your adjusted code here.

 

[Vtookup]

Got It! I have protected sheets. But can this codes be flexible to protected sheets too? and if it's not too much to ask, Please include saving the workbook first before converting to values? Many many Thanks.

 

[jolivanes]

Re: "Please include saving the workbook first before converting to values?"
I am not knocking your wishes but I always want to know people's train of thought.
Why would this be needed?

With code you can unprotect and protect sheets but the password(s) for the sheet(s) need to be known.
If different sheets have different passwords, all sheets with the password for that sheet need to be part of the code.

In Post #2 the workbook is saved as non macro (.xlsx). Is this still required as is?

 

[rlv01]

Give this a try.

Sub SaveWB()
    Dim Folder As String, FileName As String
    Dim FilePath As String, TempFilePath As String
    Dim Msg As String
    Dim DestWB As Workbook
    Dim Ans As Integer
    Dim WS As Worksheet

    Folder = ThisWorkbook.Path

    Folder = Trim(Folder)
    If Not Right(Folder, 1) = "\" Then
        Folder = Folder & "\"                         'add backslash if not present
    End If

    'Make file name
    FileName = Trim(ThisWorkbook.Name)
    If InStr(FileName, ".") > 0 Then
        Do While InStr(FileName, ".") > 0
            FileName = Left(FileName, Len(FileName) - 1)
        Loop
    Else
        MsgBox FileName & " invalid" & vbCr & vbCr & FileName
        Exit Sub
    End If

    'New file path
    FilePath = Folder & FileName & "_" & Format(Date, "yyyymmdd") & ".xlsx"

    'Temporary file path
    TempFilePath = Folder & "Tmp$File.xlsm"

    'Open temporary workbook.
    Application.DisplayAlerts = False
    ThisWorkbook.SaveCopyAs TempFilePath
    DoEvents                                          'optional
    Set DestWB = Application.Workbooks.Open(FileName:=TempFilePath)
    DoEvents                                          'optional

    ' Convert everything to values
    For Each WS In DestWB.Worksheets
        WS.UsedRange.Value = WS.UsedRange.Value
    Next WS

    'Save new file
    DestWB.SaveAs FileName:=FilePath, FileFormat:=xlOpenXMLWorkbook    'see XlFileFormat Enumeration for different formats
    DoEvents                                          'optional

    'Clean up
    DestWB.Close False
    Kill TempFilePath
    Application.DisplayAlerts = True
    MsgBox "Complete. New file is:" & vbCr & vbCr & FilePath
End Sub

 

[Vtookup]

Hi jolivanes, sorry to bother you about that dumb request. you're right, it's totally unnecessary.
I should have mentioned that the code works. my mistake was I overlooked some protected sheets.
Anyway this the best help. Thank You very much. Chill.

Hi rlv01. the code is quite long and detailed. It's also works. Thank You.