Setting a worksheet name in code
If you are writing a macro which creates a new sheet in a workbook, it
is not at all obvious how you rename it once you've created it. Try this
Dim ws As Worksheet 'Create a worksheet object|
Set ws = Sheets.Add 'set worksheet Object to new sheet
ws.Name = "NewName" 'rename sheet
This is not obvious from the help except that the help says the new method
RETURNS a worksheet object. If something is returned you can set a
reference to it using (yes you've guessed it) SET. Now you have an object referencing
this sheet you can do what you like with it.
The easiest way to continue your macro from here is to record a macro then change all references to ActiveSheet to WS.