VBA loop to copy a worksheet a number of times, assign name each time based on loop number

I'm looking to create a macro which will run a for...next loop 74 times (i=1 to 74) to copy a worksheet and name it dynamically based on the loop number. I'm not smart/experienced enough to do both within the same macro, so I could use some help from ya'll knowledgeable folks.

So I want to do the following steps:

Copy worksheet "Near leader output sheet 1" to the end of the workbook
Name new sheet "Near leader output sheet i+1" '<name contains a number based on the current number of the loop
repeat i times

I was using a simple macro to copy the worksheet a set number of times, but it has stopped working due to repeated name issues (I also can't copy worksheets by hand anymore more than once - excel seems to have forgotten how to automatically assign higher numbers to copied worksheets)

My current, non functional code which does not rename:

Please Login or Register  to view this content.

Any help would be much appreciated