stop calculating different range when adding new rows

[seansr]

I have a spreadsheet that I track all the orders for one customer
Each time we have an new order we have a macro that copies Row 6 and inserts a new row 6 and pastes it back in clearing the contens from the main section see Sub AddNewProject()

This works fine, however counting up items in the orders in hidden colums there are calculations to work out what equipment is needed
However
at the top is calculation for example =SUM($Y$6:$Y$187) - however each time when a row is added the calculation is amened by 1 row for example =SUM($Y$7:$Y$188)

how can I set it so that its always calculating from row 6 down to say 180 can be further thats not an issue, but always has to start at row 6

I have added copy of the spreadsheet with all sensative data removed

>>> Open thread in correct Forum <<<
>>> This belongs to VBA Macros <<<
Please, reread Forum Rules​

>>> use code - tags <<<

Sub AddNewProject()
'
' AddNewProject Macro
' Add new line to table

    Rows("6:6").Select
    Selection.Copy
    Rows("6:6").Select
    Selection.Insert Shift:=xlDown
    Range("B6:V6").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 3
End Sub

 

[p45cal]

try:

    Rows("6:6").Copy
    Rows("7:7").Insert
    Range("B6:V6").ClearContents ' or B7:V7 ?

 

[seansr]

try:

    Rows("6:6").Copy
    Rows("7:7").Insert
    Range("B6:V6").ClearContents ' or B7:V7 ?

Brilliant - Thanks - works a treat