Thanks Dave, Joel for your inputs
I had no intents to change the codename: Live

I tried Joel's code like this (added a Next):

Sub test()

Set NewSht = Sheets.Add(befo=Sheets(1))
NewSht.Name = Format(data, "ddmmm")

For Each Sht In Sheets
If UCase(Sht.Name) = "LIVE" Then
Sht.Copy
NewSht.PasteSpecial _
Paste:=xlPasteValues
NewSht.PasteSpecial _
Paste:=xlPasteFormats

Exit For
End If
Next

End Sub

but I got stuck at this line:
NewSht.Name = Format(data, "ddmmm")
What I wanted was to rename the new sheet as the current date
How can I replace "data" so that this happens?

When I remarked the above line, and stepped through the rest of the sub,
nothing else happened in the new sheet? It should get pasted with
data/formats from Live.

Thanks for further help