Excel-VBA Optimization: accelerate heavy formulas reports with filldown, force calculate, and static values

 

This Sub optimizes heavy workbooks by using a "Template-Fill-Freeze" logic. It copies a master formula from cell W1 to the target range using .FillDown and .FillRight to ensure all relative references adjust correctly.
By forcing a calculation with Application.Calculate and then converting the entire range to static values , it eliminates "Calculating" lag while preserving data accuracy.
A final timestamp is added to cell D1 to provide a clear audit trail for the last update.

Open the VBA Editor (Alt+F11), insert a New Module, then paste the code below. Remember to adjust the parameters (Sheet name, Ranges) to fit your data structure:

 

Sub FG_Fill_From_W1_Then_Value()

    Dim ws As Worksheet

    Dim lastRow As Long

    '1.Data declaration

    Set ws = Sheets("02.FG")

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

    If lastRow < 3 Then Exit Sub

   

    'optimize speed

    With Application

        .ScreenUpdating = False

        .Calculation = xlCalculationAutomatic

        .CutCopyMode = False

    End With

   

    'copy cell( W1 )contains fomular then paste to the beginning of range(X3)

    ws.Range("W1").Copy

    ws.Range("X3").PasteSpecial xlPasteFormulas

    'Populate formulas across the entire range (Down then Right) to ensure relative references adjust correctly

    ws.Range("X3:X" & lastRow).FillDown

    ws.Range("X3:BV" & lastRow).FillRight

   

    Application.Calculate 'Ensure Excel finishes all calculations

    ws.Range("X3:BV" & lastRow).Value = ws.Range("X3:BV" & lastRow).Value 'convert range to value only

    Application.CutCopyMode = False

    Application.ScreenUpdating = True

   

    'update macro execution time for ref

    ws.Range("d1").Value = "Last Update: " & Format(Now, "dd/mm/yyyy hh:mm:ss")

    MsgBox "Done", vbInformation

End Sub.