My sheets follow the last updated formula on the sheet and do not update automatically

Question

I have a macro which will insert a formula on a specified column across all the sheets in the workbook. But for some reason, the formula answer in previous sheet tend to be the same with other sheets which should not be the case. For example, after running the code, sheet1 will have "sheet1" as answer to the inserted formula. But checking the sheet2 and sheet 3 they also display "sheet1". If I do automatic calculation example in sheet2, "sheet2" will also be displayed to other sheets.

I tried to insert a different formula with a number answer and it worked out just fine. But if I insert this formula (=MID((CELL(""filename"")), (FIND(""]"",CELL(""filename""),1))+1,1500) the problem occurs.

macro:

Sub FormulaInsert()

    Dim i As Long
    Dim lastrow As Variant
    
    For i = 3 To Worksheets.Count
    
        With Sheets(i)
            lastrow = .Cells(Rows.Count, "B").End(xlUp).Row
            Debug.Print (i)
            Debug.Print (lastrow)

            .Range("A2:A" & lastrow).Formula = "=MID((CELL(""filename"")), (FIND(""]"",CELL(""filename""),1))+1,1500)"
           
        End With
    Next i
End Sub

Is there a way to solve this?

Answer 1

Your formula should be:

.Range("A2:A" & lastrow).Formula = "=MID((CELL(""filename"", A1)), (FIND(""]"",CELL(""filename"", A1),1))+1,1500)"

If you omit a cell address, formula returns name of a sheet, where there was last edition in the workbook.