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]

If you're happy with what you have that is all that counts. You have set rlvo1's suggestion as the solution to your problem so that is good.
Good luck

 

[Vtookup]

​

jolivanes,
Again, Sorry! This is messed up. but I have checked yours as solution.
I'm multitasking, could be in my hastiness I checked the wrong button.

 

[jolivanes]

You wanted to protect the sheets also.
This does not convert the values to values in the original.

Sub Maybe_2()
Dim newName As String, wb As Workbook, sh As Worksheet
Set wb = ThisWorkbook
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
    sh.UsedRange.Value = sh.UsedRange.Value
    sh.Protect Password:="ABC"
Next sh
With ActiveWorkbook
    .SaveAs newName & ".xlsx", 51
    .Close True
End With
End Sub

Change password as desired but make sure to remember what it is!!!!

 

[Vtookup]

RE "You wanted to protect the sheets also", No. not in the new workbook.
If my original WB is protected, can maybe() work through that?
if this doable, perhaps another help? if not, maybe() is good.
I appreciated your help.
Thank You.

 

[jolivanes]

Re: "If my original WB is protected"
Does this mean some sheets are protected and others not. Or all sheet have protection.
Do you have the password for each sheet that has protection and the sheet names that have protection or are they protected without password (which really defeats the purpose but some people do)

 

[Vtookup]

All Sheet's protected and have one password.

 

[jolivanes]

This will leave the original workbook intact but unprotect any sheet that is protected without password in the newly created workbook.
If that is not what you have in mind, let us know what you want to unprotect or protect.

Sub Maybe_2()
Dim newName As String, wb As Workbook, sh As Worksheet
Set wb = ThisWorkbook
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
    If sh.ProtectContents = True Then sh.Unprotect
    sh.UsedRange.Value = sh.UsedRange.Value
Next sh
With ActiveWorkbook
    .SaveAs newName & ".xlsx", 51
    .Close True
End With
End Sub

 

[jolivanes]

Sub Maybe_2()
Dim newName As String, pw As String, wb As Workbook, sh As Worksheet
Set wb = ThisWorkbook
pw = InputBox("Password Here")    '<---- 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
    sh.Unprotect pw
    sh.UsedRange.Value = sh.UsedRange.Value
Next sh
With ActiveWorkbook
    .SaveAs newName & ".xlsx", 51
    .Close True
End With
End Sub

 

[Vtookup]

Good day.
I might not be clear that we have drifted far. Sorry.
To be fair also, I got good insights to what Post #17-18 can do. Thanks.

You have already solve it on Post #2. if sheets are unprotected.

Can we try again?
1. Original file should be intact. nothing will be altered.
2. What if Maybe() is tweak to work around Original file even if all sheets are protected (which I intend to do. if possible) and create copy of original file as value. just as maybe() have done?
What do you think?
Thanks again.

 

[Alex Blakenburg]

Isn't that what Jolivanes post #18 does ?
If all the sheets in the new copied workbook are meant to be protected perhaps just add the line in blue.

For Each sh In ActiveWorkbook.Worksheets
    sh.Unprotect pw
    sh.UsedRange.Value = sh.UsedRange.Value
    sh.Protect pw
Next sh