How do I run an office script on all tabs in a workbook?

Question

I have an excel script that changes formulas to values for certain cells on an excel worksheet, and I am trying to figure out how to get it to work for all tabs in the same workbook. Either at the same time, or have it loop for all the tabs. Any ideas would be greatly appreciated!

Answer 1

Hi Christina, thanks for your question! You can loop through tabs/worksheets like this:

function main(workbook: ExcelScript.Workbook) {
    let sheets = workbook.getWorksheets();
    sheets.forEach(sheet => {
        // your code here
    });
}

Let me know if this helps or if you have any questions!

Answer 2

Hi,

To change formulas to values for certain cells on all tabs in an Excel workbook, you can use VBA code to loop through each worksheet in the workbook and apply the formula-to-value conversion to the desired cells. Here is an example of how to do this:

Sub ChangeFormulasToValuesOnAllTabs()
    Dim ws As Worksheet
    
    ' Loop through each worksheet in the workbook
    For Each ws In ThisWorkbook.Worksheets
    
        ' Convert formulas to values for range of cells
        ws.Range("A1:B10").Value = ws.Range("A1:B10").Value
        
    Next ws
    
End Sub

In the above code, we're looping through each worksheet using a For Each loop and the Worksheet object. You can adjust the range of cells to convert formulas to values by changing the "A1:B10" part of the code to the desired range for your workbook.

Once you've adjusted the code to your liking, you can run the macro by pressing F5 or clicking Run in the VBA editor. The macro will then loop through each worksheet in the workbook and convert the desired range of cells from formulas to values.

Note- This code will overwrite any existing formulas in the specified range with their calculated values. So, maybe take a backup of your file.

Best Regards.