Macro which creates a Hyperlink on the next available blank row?

First of all I dont even know if this is achievable. I have been set a task for work, and have been asked to create this, if at all possible.

An example is attached.

The macro needs to be assigned to one button. I have filled in one row of data (row 6), so before i fill row 7 I need to click the button which would firstly create a new sheet (which i seem to have managed to do) and secondly create a hyperlink which would go in D7 and be linked with the sheet thats just been added.

Follow the same process above, but the hyperlink would need to be placed in D8 then D9, D10 and so on, is this possible to keep the macro all on one button.

Like i said i dont even know if this is possible but if anyone can help, really appreciate it!!!