SaveCopyAs but removing all formulas?

[JumboCactuar]

Hi,
I have a file I'm working on roughly 5MB with about 12 sheets and a lot of formulas.

I'm wanting to save a copy of the file often (values only) but want to do it as quick as possible.

SaveCopyAs is pretty quick but it saves an exact copy.
Where creating a copy of current workbook, removing formulas then saving is slower

How would you approach this?

 

[Yongle]

I would use SaveCopyAs and replace formulas with values like this:

For Each ws In wb.Worksheets
    ws.UsedRange.Value = ws.UsedRange.Value
Next ws

 

[MARK858]

Where creating a copy of current workbook, removing formulas then saving is slower

How exactly are you doing this, doing the savecopyas, then on the copy selecting all sheets then selecting all cells and doing a copy/paste as values?

 

[JumboCactuar]

@MARK858
I was creating a copy of current workbook then looping through each sheet and setting value=value

But now I have this which will work for me, I can create desperate file for each sheet and this is fast

Sub SaveasVals()
Dim WB1 As Workbook, WB2 As Workbook
Dim WS1 As Worksheet, WS2 As Worksheet

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set WB1 = ActiveWorkbook
Set WS1 = ActiveSheet
Set WB2 = Workbooks.Add
Set WS2 = WB2.Sheets("Sheet1")

CurDate = WS1.Range("P1").Value
MyDir = "C:\Users\test\Downloads\"

WS1.Cells.Copy
WS2.Cells.PasteSpecial Paste:=xlPasteValues

WB2.SaveAs MyDir & CurDate & ".xlsx"
WB2.Close

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

 

[MARK858]

Happy you have a solution but replacing

WS1.Cells.Copy
WS2.Cells.PasteSpecial Paste:=xlPasteValues

with

    With WS1.UsedRange
        WS2.Cells(1, 1).Resize(.Rows.Count, .Columns.Count).Value = .Value
    End With

should be faster.

The code above assumes the data in WS1 starts in row 1.