Repeat same Macro for all sheets in same cell

Question

Repeat same Macro for all sheets in same cell

Chaturvedi, Santosh 430

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.

User's image

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

0 comments

Answer accepted by question author

Jayden-P 26,280 Microsoft External Staff Moderator

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.

Chaturvedi, Santosh 430 Reputation points

2026-06-23T13:56:47.0333333+00:00

Hello Jayeden -- This is working perfect !! Thanks for your experties..

I am mechanical engineering some time wonders how you guys do this formulation. I have learnt few tricks but i am very limited to this KNOW HOW.

Once ahain thanks for this help !!

Have a nice day from Kiel @ Germany.
Jayden-P 26,280 Reputation points Microsoft External Staff Moderator

2026-06-23T13:59:19.7866667+00:00

I'm glad to hear that. If you need anything else, please feel free to reach out.

I'm happy to help.

2 additional answers

Your answer

Chaturvedi, Santosh 430 Reputation points

2026-06-23T13:56:47.0333333+00:00

Hello Jayeden -- This is working perfect !! Thanks for your experties..

I am mechanical engineering some time wonders how you guys do this formulation. I have learnt few tricks but i am very limited to this KNOW HOW.

Once ahain thanks for this help !!

Have a nice day from Kiel @ Germany.
Jayden-P 26,280 Reputation points Microsoft External Staff Moderator

2026-06-23T13:59:19.7866667+00:00

I'm glad to hear that. If you need anything else, please feel free to reach out.

I'm happy to help.

Answer 1

Jayden-P 26,280 Microsoft External Staff Moderator

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.

Chaturvedi, Santosh 430 Reputation points

2026-06-23T13:10:35.9033333+00:00

Hello - Greetings

I am am using the VBA coding (marcros) in module 3.

It will be nice if get VBA codings.

See below..
Chaturvedi, Santosh 430 Reputation points

2026-06-23T13:12:11.17+00:00

Hello Jaydenn.. Please notice thathe sheets may be 150 or 135 or 20 or 300or 120 etc,, depending on requirements it varies..

Please do not make specific for 150 only..
Jayden-P 26,280 Reputation points Microsoft External Staff Moderator

2026-06-23T13:14:45.9866667+00:00

Please give me some time to test on this.

Jayden-P 26,280 Microsoft External Staff Moderator

Hi @Chaturvedi, Santosh

Please try this in your module, this will loop through all sheets. If you have any other requirements, you will need to modify it based on your condition.

Sub ApplyFormulaToSheets()

    Dim ws As Worksheet
    Dim lastRow As Long

    ' Loop through all sheets
    For Each ws In ThisWorkbook.Worksheets
        
        ' Find last used row
        If Application.WorksheetFunction.CountA(ws.Cells) = 0 Then GoTo NextSheet
        
        lastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        
        ' Apply formula from F6 down
        If lastRow >= 6 Then
            ws.Range("F6:F" & lastRow).Formula = "=(C6+E6)/2"
        End If

NextSheet:
    Next ws

End Sub

Chaturvedi, Santosh 430 Reputation points

2026-06-23T13:37:13.1966667+00:00
Hello Jayden--

Marcro is working fine.

but it is NOT required for "Guage_Liste_DB" --> this is dasbaord provding information. This macro must not work for this heet. (See fig below. image)

for all sheets (except "Guage_Liste_DB" ) --> The macro must insert one column in F , shifting all columns right side and then the it formula runs in the created column. This will be repeated for all sheets ( or what so ever the sheets are..)

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:

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
No .Activate needed: Works directly on selectedSheet - faster for 150 sheets
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.