How to give a NameBox a name with VBA

Hello,

I have the following code which names the Name Box through VBA.

activesheet.range("A:A").name="aaa"

The next time I open the same workbook I have to copy the sheet over to a new sheet and rename the Name Box to something like "aaaa"

Is there a way through VBA that it update automatically to a random number/ name or an increment of + 1 each time?

Hope that makes sense.

Thanks

Ross