Excel VBA copying literal cells from another sheet (do I really need to vlookup?)

You can go to your Excel sheet -> Formula Ribbon -> Name Manager -> New Name

Set your new names to be equal to $A$1 and $A$2. When you insert rows etc. in Excel, the Name Manager will automatically shift around so it stays pointing at the 'old cells'.

Then in VBA, you can refer to these as Range("NAME_FOR_A1") or Range("NAME_FOR_A2"), and it will always refresh to the 'updated' target.

Or, reading your question again, you could simply take the values from A1 & A2, put those values to a variable, and pass them to A3 & A4. So, something like:

A1Variable = Sheets(1).Range("A1").Formula

A2Variable = Sheets(1).Range("A2").Formula

Sheets(2).Range("A3") = A1Variable
Sheets(2).Range("A4") = A2Variable

Vlookup is not relevant here; that's an Excel Formula tool, not VBA. Hypothetically you could use it, but only rarely would that be simpler than using a VBA approach.