VBA to populate table of formula's relative to active cell

[Chrican]

I’d like to build a macro that sets up a bunch of linked formula’s in a range relative to the active cell, which is getting a bit advanced for me. I’ve generalized it a bit for anonymity, but what I’d like is for the macro to do the following steps:

1. Relative to the active cell, check that the range 3 columns x 11 rows is empty. If not, prompt “Overwrite cells?” Yes/no.
2. In column 2, row 1, insert “Lookup”
3. In column 2, rows 2 through 11, insert “Revenue”
4. In column 3, row 1, insert “2019” (i.e. the year)
5. In column 3, rows 2 through 11, insert formula A (see below)
6. In column 1, rows 2 through 11, insert formula B from below
7. Bonus: Typically, each of these formula’s is a bit laggy when you enter it in (linked network files) – is there a way to pause all that, insert the text, then let them calculate at the last step, all at once? Maybe disable update links? Turning off automatic calculations doesn’t seem to help when I do it the non-VBA way.

Basically what this does is builds a simple table that index/matches data from each of 10 company models that I maintain, which we then use to compare metrics across our models. It’s meant to make the process a lot quicker than having to open each file and link them manually, or find+replace each file name, etc. Once the table is populated I can mess with the years and lookups or add more columns as needed.

Formula A – Index/matches the lookup from above, in column A of each model, and the year from above in row 4 of each model (these are based on running the macro from A1; anchoring the references to the second column and first row is important) (each of the ten times this is entered would be a different, but specific, file obviously):

=INDEX('X:\Filepath[ABC.xlsm]Model'!$1:$1048576,MATCH($B2,'X:\Filepath\[ABC.xlsm]Model'!$A:$A,0),MATCH(C$1,'X:\Filepath\[ABC.xlsm]Model'!$4:$4,0))

Formula B – Identifies which model each formula is linking to for auditing purposes:

=MID(FORMULATEXT(C2),FIND("[",FORMULATEXT(C2),1)+1,FIND(".xls",FORMULATEXT(C2),1)-FIND("[",FORMULATEXT(C2),1)-1)

Any help would be appreciated!