Hello,
I'm trying to create VBA code that converts all current formulas in cells to text values and save the entire sheet to a new XLS file.
For this purpose I've used the following VBA code, but it doesn't do what I want. The newly created XLS file still contains formulas in the cells.
I would appreciate any idea how to fix it.
Sub SaveDialogFile()
Dim FilePath As String
Dim FilePathServer As String
FilePathServer = "\\Fileserver\2025\"
On Error Resume Next
Application.DisplayAlerts = False
With Sheets("Order")
If .Range("T9") = "" Then
MsgBox "Please enter ERP order number!"
.Activate
.Range("T9").Select
Exit Sub
End If
End With
With Application.FileDialog(msoFileDialogSaveAs)
Range("B4:C53").Copy
Range("B4:C53").PasteSpecial (xlPasteValues)
.Title = "Please choose a location and file name to save."
.ButtonName = "Save Deal File"
.InitialFileName = FilePathServer & Format(Now(), "yymmdd HhNn")
If .Show = 0 Then
MsgBox "File did not save!", vbCritical
Exit Sub
End If
Application.DisplayAlerts = False
.Execute
Application.DisplayAlerts = True
End With
End Sub
I'm trying to create VBA code that converts all current formulas in cells to text values and save the entire sheet to a new XLS file.
For this purpose I've used the following VBA code, but it doesn't do what I want. The newly created XLS file still contains formulas in the cells.
I would appreciate any idea how to fix it.
Sub SaveDialogFile()
Dim FilePath As String
Dim FilePathServer As String
FilePathServer = "\\Fileserver\2025\"
On Error Resume Next
Application.DisplayAlerts = False
With Sheets("Order")
If .Range("T9") = "" Then
MsgBox "Please enter ERP order number!"
.Activate
.Range("T9").Select
Exit Sub
End If
End With
With Application.FileDialog(msoFileDialogSaveAs)
Range("B4:C53").Copy
Range("B4:C53").PasteSpecial (xlPasteValues)
.Title = "Please choose a location and file name to save."
.ButtonName = "Save Deal File"
.InitialFileName = FilePathServer & Format(Now(), "yymmdd HhNn")
If .Show = 0 Then
MsgBox "File did not save!", vbCritical
Exit Sub
End If
Application.DisplayAlerts = False
.Execute
Application.DisplayAlerts = True
End With
End Sub