Can I use VBA code in Excel to do this?

Shubhashree

Initially client was asking for How can I preserve the values while "erasing" the formula in Cell BB7 in DataStream?

So I have provided the below steps:

 

To preserve the values while removing the formula, use the "Save as Values" feature:
1. Open your file (ensure data is updated)
2. Right-click on cell BB7 (or select the range BB7:CB2420)
3. Click "Save/Restore as Values" → "Save as Values - Worksheet"
This will convert all formulas in the selection to static values, allowing you to delete the formula in BB7 without losing the data in the range.

 

 

But now client is asking  Can I use VBA code in Excel to do this?

Jirapongse

@Shubhashree

Thank you for reaching out to us.

The code could be like this:

Sub CopySheetAsValuesInSameWorkbook()


    ActiveSheet.Copy After:=Worksheets(Worksheets.Count)


    ' Convert formulas to values in the new active sheet

    With ActiveSheet.UsedRange

        .Value = .Value

    End With    


End Sub

It will create a new worksheet which contains values.

This question isn’t related to our APIs, so it may fall outside our scope.