シート名をA1のセルの内容と同じにします。
簡単には以下の様にします。
Sub Sample63()
ActiveSheet.Name = Range("A1").Value
End Sub
ただ、A1のセルにシート名に使用できない文字、「:」「?」「/」「\」「*」「[」「]」が含まれている場合や他のシート名と同一になってしまった場合、エラーでVBAが止まってしまいます。それをダイアログで表示する様にするには、以下の様な感じです。
Sub Sample63a()
On Error GoTo ERA
ActiveSheet.Name = Range("A1").Value
Exit Sub
ERA:
MsgBox "シート名に使用できない文字が含まれているか、" & vbCrLf + "他のシート名と同じ名前になっています"
End Sub