Saving an excel file with multiple sheets as values using a cell reference as the file name

[Peter Davison]

Using VBA code, can you advise how to save a workbook with multiple sheets removing all formulas and save as values retaining all formatting, but using a name entered in "Cell I2" as the file name?

Thanks for your help

 

[KOKOSEK]

Sub OverwriteFormulaWithValuesAndSave()
    Dim sh As Worksheet
    Dim FileName as string
    For Each sh In ActiveWorkbook.Worksheets
        With sh.UsedRange
            .Value = .Value
        End With
    Next sh
    FileName=Sheets("XXX").Range("I2").value
    ActiveWorkbook.SaveAs filename:= FileName & ".xlsm", FileFormat:=xlNormal
End Sub

 

[Peter Davison]

Thank you I really appreciate your time.
As some of my sheets have a chart that is linked to other workbooks, can the code also make the charts just a picture with no source? Or any other way?
Cheers

 

[KOKOSEK]

Should works like this:

Option Explicit
Sub OverwriteFormulaWithValuesAndSave()
    Dim sh As Worksheet
    Dim FileName As String
    Dim MyChrt As Object
    Application.ScreenUpdating = False
    For Each sh In ActiveWorkbook.Worksheets
        With sh
            For Each MyChrt In .ChartObjects
                MyChrt.CopyPicture
                .Paste
                With .Shapes(.Shapes.Count)
                    .Top = MyChrt.Top
                    .Left = MyChrt.Left
                    .Name = MyChrt.Name & "_pasted_as_pic"
                End With
                MyChrt.Delete
            Next MyChrt
        End With
        With sh.UsedRange
            .Value = .Value
        End With
    Next sh
    FileName = Sheets("XXX").Range("I2").Value
    ActiveWorkbook.SaveAs FileName:=FileName & ".xlsm", FileFormat:=xlNormal
    Application.ScreenUpdating = True
End Sub

 

[Peter Davison]

Thank you I will give this a try and let you know how it goes.
What would I be entering here - "_pasted_as_pic"

 

[John_w]

As some of my sheets have a chart that is linked to other workbooks, can the code also make the charts just a picture with no source? Or any other way?

Instead of copying and pasting the charts as a picture, you could break the links to the other workbooks:

Sub OverwriteFormulaWithValuesAndSave2()
    Dim sh As Worksheet
    Dim FileName As String
    For Each sh In ActiveWorkbook.Worksheets
        With sh.UsedRange
            .Value = .Value
        End With
    Next sh
    
    Dim ExternalLinks As Variant, i As Long
    With ActiveWorkbook
        ExternalLinks = .LinkSources(Type:=xlLinkTypeExcelLinks)
        For i = 1 To UBound(ExternalLinks)
            .BreakLink Name:=ExternalLinks(i), Type:=xlLinkTypeExcelLinks
        Next
    End With
    
    FileName = Sheets("XXX").Range("I2").Value
    ActiveWorkbook.SaveAs FileName:=FileName & ".xlsm", FileFormat:=xlNormal
End Sub

 

[Peter Davison]

That's great as well.
Thanks so much both of you
I'll give it a try

 

[Peter Davison]

Hi John W
I tried the formula and all worked okay until the save file.
It says that it is trying to save in a 97-03 version (so I exceed rows)

Is it something to do with the line -
ActiveWorkbook.SaveAs FileName:=FileName & ".xlsm", FileFormat:=xlNormal

Should the xlNormal be different?
Could the file also save without the macro.

That would be great if you can help.
Cheers

 

[Peter Davison]

Hi John, Kokosek,

I have adjusted the formula to what I think is correct and added some message boxes.
I'm not sure I've got the first message box correct.
If they click cancel I want the routine to end, but I think it just goes back to the top and starts again
I think the word "End" should be something else, but just not sure what?

This is the code

Sub OverwriteFormulaWithValuesAndSave2()

Dim sh As Worksheet
Dim FileName As String

'Message Box to continue or abort
Dim answer1 As Integer
answer1 = MsgBox("Please Note: This process will remove the user selection options click OK to continue or Cancel", vbOKCancel)
If answer1 = vbOK Then
MsgBox "OK"
Else
MsgBox "Cancel"
End
End If

'Overwrites all formulas as values
For Each sh In ActiveWorkbook.Worksheets
With sh.UsedRange
.Value = .Value
End With
Next sh

'Breaks links with charts
Dim ExternalLinks As Variant, i As Long
With ActiveWorkbook
ExternalLinks = .LinkSources(Type:=xlLinkTypeExcelLinks)
For i = 1 To UBound(ExternalLinks)
.BreakLink Name:=ExternalLinks(i), Type:=xlLinkTypeExcelLinks
Next
End With

'Message Box before saving to name specified in Cell C1
Dim answer2 As Integer
answer2 = MsgBox("The File is now ready to Save - Click ok to continue", vbOKOnly)

'Saves file to name specified in Cell C1
Application.DisplayAlerts = False
FileName = Sheets("Reports Index").Range("C1").Value
ActiveWorkbook.SaveAs FileName:=FileName & "xlsx", FileFormat:=51
Application.DisplayAlerts = True
End Sub

If you can help me that would be great.

Regards

 

[KOKOSEK]

Sub aaa()
Dim answer1 As Integer
answer1 = MsgBox("Please Note: This process will remove the user selection options click YES to continue or NO to stop procedure", vbYesNo)
If answer1 = vbYes Then
MsgBox "OK go then.."
Else
MsgBox "You choose NO. Procedure has stop."
Exit Sub
End If
End Sub