MAcro to calculate : (F6=(C6+E6)/2

Use a VBA macro that reads the sheet names listed on DB Collectives starting at C4, opens each matching worksheet, inserts a new column at F (immediately after E), writes the formula in F6, and fills it down to the last used row in column E.

Sub InsertFormulaInListedSheets()
    Dim wsList As Worksheet
    Dim ws As Worksheet
    Dim lastRowList As Long
    Dim cell As Range
    Dim sheetName As String
    Dim lastDataRow As Long
    
    Set wsList = ThisWorkbook.Worksheets("DB Collectives")
    lastRowList = wsList.Cells(wsList.Rows.Count, "C").End(xlUp).Row
    
    For Each cell In wsList.Range("C4:C" & lastRowList)
        sheetName = Trim(cell.Value)
        
        If sheetName <> "" Then
            On Error Resume Next
            Set ws = ThisWorkbook.Worksheets(sheetName)
            On Error GoTo 0
            
            If Not ws Is Nothing Then
                ' Insert a new column at F, which is after column E
                ws.Columns("F").Insert Shift:=xlToRight
                
                ' Find the last used row in column E
                lastDataRow = ws.Cells(ws.Rows.Count, "E").End(xlUp).Row
                
                ' Start only if there is data from row 6 downward
                If lastDataRow >= 6 Then
                    ws.Range("F6").Formula = "=(C6+E6)/2"
                    ws.Range("F6").AutoFill Destination:=ws.Range("F6:F" & lastDataRow)
                End If
            End If
            
            Set ws = Nothing
        End If
    Next cell
End Sub

Notes:

• This inserts a new F column every time the macro runs. If run again, another new column will be added.
• The formula is filled from F6 down to the last used row in E.
• Sheet names are taken from the text in DB Collectives!C4:C....

To add the macro in Excel:

1. Show the Developer tab.
2. Open Developer > Macros or the Visual Basic Editor.
3. Paste the code into a standard module.
4. Run InsertFormulaInListedSheets.

• Quick start: Create a macro
• Repeat same Macro for all sheets in same cell - Microsoft Q&A hello