Macro to make all forumlas values, all charts pictures and save as todays date

[HeatherEXL]

So I'm making a kind of daily dashboard with multiple sheets of data which automatically update from multiple other spreadsheets.

I've created the dashboard so that all data is linked to the other workbooks. However I want to save a static copy of this each day.
This means I need to

• Convert all forumlas to values across the workbook
• Convert all graphs into images across the workbook
• Save the workbook as Title_YYYY_MM_DD in a specific folder

I've got some of the component parts of the VBA code to do this via macro but am struggling with how to put them all together to make something that works seamlessly. any help much appreciated!

This is the VBA code I have so far, but feel free to change so that it works better

Sub Saveasvalue()
'Updateby Extendoffice
Dim wsh As Worksheet
For Each wsh In ThisWorkbook.Worksheets
wsh.Cells.Copy
wsh.Cells.PasteSpecial xlPasteValues
Next
Application.CutCopyMode = False
'
' Save_As Macro
'
' Keyboard Shortcut: Ctrl+Shift+I
'
ChDir "Z:\FOLDER_1\FOLDER_2\STATISTICS"
ActiveWorkbook.SaveAs Filename:= _
"Z:\FOLDER_1\FOLDER_2\STATISTICS\Daily_stats_" & Format(Now(), "YYYY-MM-DD") & ".xlsm", FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
End Sub

 

[Yongle]

Code below works for me
- charts converted to images
- cell formulas overwritten with values
- workbook saved under new name and closed

Amend file path etc to match your needs

Sub HardCode()
    Dim chrt As ChartObject, ws As Worksheet, wb As Workbook, fname As String, fpath As String
    Set wb = ThisWorkbook
    fpath = "c:\Test\Folder\SubFolder"
    fname = "Daily_stats_" & Format(Date, "YYYY-MM-DD") & ".xlsm"
  
    For Each ws In wb.Worksheets
        ws.Activate
        ws.UsedRange.Value = ws.UsedRange.Value
        For Each chrt In ws.ChartObjects
            chrt.Copy
            chrt.TopLeftCell.Select
            ActiveSheet.Pictures.Paste.Select
            chrt.Delete
        Next chrt
    Next ws
    wb.SaveAs fpath & "\" & fname
    wb.Close False
End Sub