Repeat same Macro for all sheets in same cell

Question

hello

I have made one macro to insert the formula in sheet S1 in cell F6. This is same from F6 to the end of F column. This macro is shown below is well working

Now --> I do have the sheet from S2, S3, S4.....S 150

I want --> to repeat the same marco for all sheets (150 nrs.) in same column F and in from same cell that is F6 till its end.

---

The macro is as shown below.. I want to repeat this macro for all sheets. from S1 to S2...... S150 (all sheets) in workbook

---

function main(workbook: ExcelScript.Workbook) { // Get all worksheets in the workbook. const sheets = workbook.getWorksheets(); // Loop through each worksheet and apply the same operations. for (let sheet of sheets) { applyMacroToSheet(sheet); } } function applyMacroToSheet(selectedSheet: ExcelScript.Worksheet) { // Insert at range F:F on selectedSheet, move existing cells right selectedSheet.getRange("F:F").insert(ExcelScript.InsertShiftDirection.right); // Set range F6 on selectedSheet selectedSheet.getRange("F6").setFormula("=(C6+E6)/2"); // Auto fill range selectedSheet.getRange("F6").autoFill("F6:F105", ExcelScript.AutoFillType.fillDefault); // Then keep the rest of the border code exactly as in the original macro, // but using the selectedSheet parameter instead of workbook.getActiveWorksheet(). }.

Answer 1

You can skip one sheet using this:

Sub ApplyFormulaToSheets()

    Dim ws As Worksheet
    Dim lastRow As Long

    For Each ws In ThisWorkbook.Worksheets

        ' Skip this sheet
        If ws.Name <> "Guage_Liste_DB" Then

            
            If Application.WorksheetFunction.CountA(ws.Cells) > 0 Then

                ' Insert new column at F (shift right)
                ws.Columns("F").Insert Shift:=xlToRight

                ' Find last used row
                lastRow = ws.Cells.Find("*", _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious).Row

                ' Apply formula from F6 down in the NEW column
                If lastRow >= 6 Then
                    ws.Range("F6:F" & lastRow).Formula = "=(C6+E6)/2"
                End If

            End If

        End If

    Next ws

End Sub

Please note that if you use Insert, each time you run the macro, it will create a new column at F.

Answer 2

function main(workbook: ExcelScript.Workbook) {

// Get all worksheets in the workbook

const sheets = workbook.getWorksheets();

// Loop through each worksheet and apply the same operations

for (let sheet of sheets) {

applyMacroToSheet(sheet);

}

function applyMacroToSheet(selectedSheet: ExcelScript.Worksheet) {

// Set formula in F6

selectedSheet.getRange("F6").setFormula("=(C6+E6)/2");



// Find last used row in column C or E to autofill correctly

let lastRow = selectedSheet.getRange("C:C").getUsedRange().getRowCount() + selectedSheet.getRange("C:C").getUsedRange().getRowIndex();

if (selectedSheet.getRange("E:E").getUsedRange().getRowCount() + selectedSheet.getRange("E:E").getUsedRange().getRowIndex() > lastRow) {

  lastRow = selectedSheet.getRange("E:E").getUsedRange().getRowCount() + selectedSheet.getRange("E:E").getUsedRange().getRowIndex();

}



// Autofill F6 down to last row

if (lastRow >= 6) {

  selectedSheet.getRange("F6").autoFill(F6:F${lastRow}, ExcelScript.AutoFillType.fillDefault);

}

}

}

Why this is best:

1. Dynamic last row: Instead of hardcoding F6:F105, it finds the last used row in C/E so it works even if S2 has 50 rows and S100 has 200 rows
2. No .Activate needed: Works directly on selectedSheet - faster for 150 sheets
3. Keeps your formula: =(C6+E6)/2 exactly as you had

VBA version if you need it:

Sub FillFormulaAllSheets()

Dim ws As Worksheet

Dim lastRow As Long



For Each ws In ThisWorkbook.Worksheets

    lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row

    ws.Range("F6").Formula = "=(C6+E6)/2"

    ws.Range("F6:F" & lastRow).FillDown

Next ws



MsgBox "Done for all sheets!"

End Sub

Run this once and it’ll hit all 150 sheets S1-S150, putting =(C6+E6)/2 in F6 and autofilling down column F on each sheet.

Answer 3

Hi @Chaturvedi, Santosh

I added a loop to run from s1 to s150. Since you mentioned you want to write the formula in the same column F,

I remove this line from your original script: SectedSheet.getRange("F:F").insert(ExcelScript.InsertShiftDirection.right); This line inserts a new column at column F and shifts the existing columns (F, G, H, etc.) one position to the right.

You can paste this script in Automate tab > Script.

function main(workbook: ExcelScript.Workbook) {

    for (let i = 1; i <= 150; i++) {
        let sheet = workbook.getWorksheet(`S${i}`);
        if (sheet) {
            applyMacroToSheet(sheet);
        }
    }
}

function applyMacroToSheet(sheet: ExcelScript.Worksheet) {

    let usedRange = sheet.getUsedRange();
    if (!usedRange) return;

    let lastRow = usedRange.getLastRow().getRowIndex() + 1;
	//directly write formula to column F (no insert) 
    if (lastRow >= 6) {
        let fillRange = sheet.getRange(`F6:F${lastRow}`);
        fillRange.setFormula("=(C6+E6)/2");
    }
}

Please try and let me know how it goes. If it does not work on your side, please provide the error code.

---

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.