Automatically copy rows down

Hi,

I have a series of data which appears in Sheet1 cells E15:U200 and some formulae on Sheet2 in cells A9:V9 (with headers in row 8). The formulae in Sheet2 link directly to the data on Sheet1 – row 9 relates to row 15, row 10 relates to row 16, etc. (nothing fancy going on there).

I would like to copy down the formulae in Sheet2 automatically depending on the length of the data in Sheet 1 (i.e. the length of column E, minus the first 14 rows).

If I re-run the report (and then the macro), is it also possible to delete any existing formulae in Sheet2 rows 10 onwards first (i.e. so the formulae only extend as far as the actual data, rather than as far as the longest set of data encountered so far – e.g. if the formulae extended to row 100 the last time the macro was run, delete rows 10-100 then copy the formulae down as far as required).

Can anyone help, please?

Thank you very much @millz that seems to do the job perfectly