Cell range is shifting automatically in formula-CodePudding

I need some help in range lock process, in LONG SHORT tab L N and O column in linked from ALL DATA tab, and whenever I run the macro to update the data in ALL DATA tab then range in long short tab automatically get shifted.

Below is the code by which ALL DATA tab is updating after that range is shifting in LONG SHORT TAB - so I am not getting how to fix it:

LONG SHORT.

Sub datacopy()
    Sheets("All Data").Activate
    'lastrow = Sheets("All Data").Range("C55555").End(xlUp).Row
    Sheets("All Data").Range("C4").Select
    ActiveCell.EntireRow.Resize(11).Insert Shift:=xlDown
    
    Sheets("DERIVATIVES OI").Range("A2:O7").Copy
    Sheets("All Data").Range("c4:q9").PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    Sheets("DASHBOARD").Activate
    
    MsgBox "DATA UPDATED SUCCESSFULLY !"
    
End Sub

CodePudding user response：

This is standard Excel behavior. You are inserting data by a shift down and a paste action. So all your references to cells move with it, even if you lock it with the $.

One way to get around this is using the Indirect function:

= INDIRECT("'All Data'!D6")-INDIRECT("'All Data'!D17")