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