Copy and rename worksheet based on specified cell value

[Kenor]

Hi All,

I want to set button click to copy and rename worksheet based on specified cell value

for example cell K1 = A Press and cell L1 = 16062022

So new worksheet = A Press 16062022

I would like to use the below code but need to modify it a bit. I need someone to help.

Sub Copyrenameworksheet()
Dim ws As Worksheet
Set wh = Worksheets(ActiveSheet.Name)
ActiveSheet.Copy After:=Worksheets(Sheets.Count)
If wh.Range("A1").Value <> "" Then
ActiveSheet.Name = wh.Range("A1").Value
End If
wh.Activate
Ens Sub

 

[Fluff]

How about

Sub Kenor()
   Dim ShtName As String
   
   ShtName = Range("K1").Value & " " & Range("L1").Value
   If ShtName = "" Then
      MsgBox "no name entered"
      Exit Sub
   ElseIf Evaluate("isref('" & ShtName & "'!A1)") Then
      MsgBox "Sheet name " & ShtName & " is already used"
      Exit Sub
   End If
   ActiveSheet.Copy , Sheets(Sheets.Count)
   ActiveSheet.Name = ShtName
End Sub

 

[Kenor]

How about

Sub Kenor()
   Dim ShtName As String
  
   ShtName = Range("K1").Value & " " & Range("L1").Value
   If ShtName = "" Then
      MsgBox "no name entered"
      Exit Sub
   ElseIf Evaluate("isref('" & ShtName & "'!A1)") Then
      MsgBox "Sheet name " & ShtName & " is already used"
      Exit Sub
   End If
   ActiveSheet.Copy , Sheets(Sheets.Count)
   ActiveSheet.Name = ShtName
End Sub

Thanks Fluff...it's great!

 

[Fluff]

You're welcome & thanks for the feedback.