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

HeatherEXL

New Member
Joined
Aug 6, 2020
Messages
1
Office Version
  1. 2013
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,871
Office Version
  1. 365
Platform
  1. Windows
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

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,647
Messages
5,549,174
Members
410,903
Latest member
natesreich
Top