If you don't want the copied sheet to exist if there was an error renaming it,
you could just delete it in that error checking portion.

   Dim my_sheet As String    
   my_sheet = Range("f4").Value
   ActiveSheet.Copy After:=Sheets(1)
   
   on error resume next    
   ActiveSheet.Name = my_sheet
   if err.number <> 0 then
       application.displayalerts = false
       activesheet.delete
       application.displayalerts = true
       msgbox "rename failed--either invalid name " _
               & "or a worksheet with that name already exists"
       err.clear
   end if
   on error goto 0

Dim my_sheet As String
Dim sh As Worksheet
   my_sheet = Range("f4").Value
   On Error Resume Next
   Set sh = Worksheets(my_sheet)
   On Error GoTo 0
   If sh Is Nothing Then
       ActiveSheet.Copy After:=Sheets(1)
       ActiveSheet.Name = my_sheet
   Else
       MsgBox "Sheet " & my_sheet & " already exists"
   End If

That works much better.

Thanks Martin

Maybe the same idea without activating the sheet--might be useful if that
worksheet exists but is hidden.

   Dim my_sheet As String    
   Dim ws as worksheet
   dim testwks as worksheet

   set ws = activesheet

   my_sheet = Range("f4").Value

   set testwks = nothing
   on error resume next
   set testwks = worksheets(my_sheet)
   on error goto 0

   if testwks is nothing then
      'keep going
   else
       msgbox "Already have a sheet with the name " _
               & my_sheet, vbokonly,"Rename error"
       exit sub
   end if

   ws.Copy _
        After:=Sheets(1)

   on error resume next
   ActiveSheet.Name = my_sheet
   if err.number <> 0 then
       msgbox "rename failed--invalid name"
       application.displayalerts = false
       activesheet.delete
       application.displayalerts = true
       err.clear
      end if
  end if
  on error goto 0

(Untested--watch for typos.)