lock formula in a cell to its target cell

I have a relatively complicated problem with Excel which I have never encountered before.

I have a spreadsheet with some VB code in it which fills in another spreadsheet with 2 tabs. information is fed into one tab of the second sheet, and the other tab of the second sheet has some simple data mirroring formulae which present that data in another format. here is the problem. the VB code does an insert on the first tab to add a line as it adds data (since it is not known how many lines there will be) the formulae in the second tab are automatically adjusting themselves as the new lines are added, I don't want them to. a formula on the second tab says for example =Tab1!A5 which mirrors the contents to this other cell. the VB code adds a new line, and the formula in that tab automatically corrects itself to =Tab1!A6. I want it to stay A5, because that's the data that I need. using the $ character only prevents the formula from correcting itself if you move the cell around, it does not lock that formula so that it doesn't change when the user (or code) adds a line. how do I fix it?

Perhaps use the INDIRECT formula, e.g. change your formula from

=Tab1!A5

to

=INDIRECT("Tab1!A5")