CODEWITHSUNDEEP
excelvbaexport
Jason Yuen
Jason Yuen

Reputation: 25

Excel VBA Export to Excel sheet but remove formulas

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

Upvotes: 0

Views: 1361

Answers (3)

Osman Wong
Osman Wong

Reputation: 170

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.

Upvotes: 0

cybernetic.nomad
cybernetic.nomad

Reputation: 6368

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

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96753

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

Upvotes: 2

Related Questions