Dfyjkt

I have a piece of VBA that is exporting 3 tabs to an excel Workbook daily. I have a piece of script that removed all external links which is great. however I am wondering if there is another piece of code that will allow me to also remove formulas. There is a formula on the field headers that are calculating dates.

So instead of just Showing NOV 7, it is still showing the formula =Today()-1
In my VBA I'd like to include code that would just include the value of "Nov 7" rather than the formula.

The script I have is below

'excel read only
Application.DisplayAlerts = False
Sheets(Array("Template", "Data Export", "Sales Breakdown")).Copy

Dim ExternalLinks As Variant
Dim x As Long

'Create an Array of all External Links stored in Workbook
ExternalLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)

'Loop Through each External Link in ActiveWorkbook and Break it
For x = 1 To UBound(ExternalLinks)
ActiveWorkbook.BreakLink Name:=ExternalLinks(x), Type:=xlLinkTypeExcelLinks
Next x

ActiveWorkbook.SaveAs Filename:="MY FILENAME", FileFormat:=51, CreateBackup:=False

How about:

Sub FormulaKiller()
 Dim sh As Worksheet

 For Each sh In ActiveWorkbook.Worksheets
 With sh.Cells
 .Copy
 .PasteSpecial xlPasteValuesAndNumberFormats
 End With
 Next sh
End Sub

Another approach:

On Error Resume Next
Set FormulaCells = ActiveSheet.UsedRange.SpecialCells(xlFormulas, 23)

' Exit if no formulas are found
If FormulaCells Is Nothing Then
 Exit Sub
End If

For Each myCell In FormulaCells
 myCell.Value = myCell.Value
Next myCell

right after you copy you can refer to value of the old workbook to change the stuff to value.
something like

Dim wb1, wb2 As Workbook
Set wb1 = ActiveWorkbook
wb1.Sheets(Array("Template", "Data Export", "Sales Breakdown")).Copy
Set wb2 = ActiveWorkbook 'the new workbook is now wb2
For Each i In Array("Template", "Data Export", "Sales Breakdown")
wb2.Sheets(i).usedrange.Value = wb1.Sheets(i).usedrange.Value
Next
///rest of yourcode///

or if you have the exact range you can use range("A1:Z500") instead of usedrange to make it faster. Using this method you can avoid using copy&paste value (which i hate). and instead of activeworkbook you can use wb2 in your below code.