Referencing issue when running insert-rows-Macro

Hello there,

I am struggling in a situation I hope anyone could help or provide any workaround. I am attaching a sample document to make the situation more clear. I run Excel 365.

I have two set of rows 3-5 and 9-11. Each of these sets of rows contain 3 rows. I then have a macro that inserts a row to each of these sets. That is, whenever I need a new product name, 1 row is inserted in the first set, that would then go from 3-6 and 1 row is inserted in the second set, that would then go from 10-13.

The pieces of code of that macro look as follows:

Sub Add_Rows()

Application.ScreenUpdating = False

    Sheets("Sheet1").Select
    Cells.Find(What:="Header 1", After:=ActiveCell, LookIn _
        :=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(rowOffset:=2).Activate
    ActiveCell.EntireRow.Select
    Selection.Copy
    ActiveCell.Offset(rowOffset:=1).Activate
    Selection.Insert Shift:=xlDown
    
    Sheets("Sheet1").Select
    Cells.Find(What:="Header 2", After:=ActiveCell, LookIn _
        :=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(rowOffset:=2).Activate
    ActiveCell.EntireRow.Select
    Selection.Copy
    ActiveCell.Offset(rowOffset:=1).Activate
    Selection.Insert Shift:=xlDown

Application.ScreenUpdating = True

End Sub

The problem I have is that there are cells in all these rows that reference the other set of rows at the same time. That is, explained by colors (see attached document), cells in the first set of rows reference both cells in the same set of rows (blue) and cells in the second set of rows (orange). At the same time, cells in the second set of rows reference cells in the first one (blue and green).

The real issue is that, whatever the order of the inserted rows in the VBA code is, there will always be some referencing that will be broken.

I want that whenever the row is added, it follows the formulation of the cells above/below accordingly, but the formulation breaks (naturally, because it tries to reference a row that is not yet created).

Is there any possibility to write additional code in order to handle this? Is it possible to write the code in a way that creates all rows simultaneously instead of reading the order of writing? Is this even possible to be worked around? I have tried with "Application.Calculation = xlCalculationManual" at the beggining and "Application.Calculation = xlCalculationAutomatic" at the end, but it does not work.

Would the only solution be to first create all rows and then write the code to fill the formulation of all these newly inserted rows accordingly?

Any help would be appreaciated.

Martin