Excel-VBA :replacing heavy formulas with static values for specific rows while preserving existing worksheet formulas

 

This Sub utilizes a "Conditional Logic" approach to selectively update specific rows while preserving existing formulas in the rest of the worksheet.

 It loops through a designated criteria column to identify target rows, then copies a master formula template across the entire row via .FillRight to ensure correct relative references. 

By forcing a calculation and immediately freezing the results into static values, the script maintains the integrity of adjacent rows that rely on automated logic.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 BTP_Copy_From_g1_For_required_Rows()

    Dim ws As Worksheet

    Dim lastRow As Long

    Dim i As Long

   

        '1.Data declaration

    Set ws = Sheets("03.BTP Plan")

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

   

        'optimize speed

    Application.ScreenUpdating = False

    Application.Calculation = xlCalculationAutomatic

    Application.CutCopyMode = False

   

    'with each row,check the specific condition

    For i = 3 To lastRow

        If ws.Cells(i, "E").Value = "Demand" Then 'copy cell( g1 )contains fomular then paste to the beginning of range(h3)

            ws.Range("g1").Copy

            ws.Range("H" & i).PasteSpecial xlPasteFormulas

            'Populate formulas across the entire range to ensure relative references adjust correctly

            ws.Range("H" & i & ":BF" & i).FillRight

           

            Application.Calculate 'Ensure Excel finishes all calculations

            ws.Range("H" & i & ":BH" & i).Value = ws.Range("H" & i & ":BH" & i).Value 'convert range to value only

        End If

    Next i

   

    Application.CutCopyMode = False

    Application.ScreenUpdating = True

        'update macro execution time for ref

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

    MsgBox "Done", vbInformation

 

End Sub