Auto rename sheets based on text in cell

  • Hi
    I have a code that Auto rename sheets based on text in cell which works fine (See below). However I would like the code to ignore any sheets that have already been named.

    Sub tabname()
    ' Names each newley created tab with the scps name
    Application.ScreenUpdating = False

    Dim ws As Worksheet
    For Each ws In Worksheets
    On Error Resume Next
    If Len(ws.Range("P2")) > 0 Then
    ws.Name = Replace(ws.Range("P2").Value, "/", "-")
    End If
    On Error GoTo 0
    If ws.Name <> Replace(ws.Range("P2").Value, "/", "-") Then
    MsgBox ws.Name & " Was Not renamed, the suggested name was invalid"
    End If
    End Sub



  • If you mean sheets assigned default names and you don't have values starting with "Sheet" in the range, I guess you could wrap the code in an If statement like this:

    If ws.Name Like "Sheet*" Then
  • Hi Jonathan

    thank you for the quick response. My knowledge of VBA is still basic and the code I provided was already in existence and just edited by me. Wear in the code should I add or what part of my code should I replace with

    If ws.Name Like "Sheet*" Then



  • in context to what jonathan wanted to say, see below code, also please try to play with your code this is the way you will learn new things.

  • Again, i assume that unnamed sheets are those that have been created with the default names (Sheet1, Sheet2, etc.) and your P2 cells do not have names like that:

  • Hi Jonathan

    that's correct the unnamed sheets have been created with the default names (Sheet1, Sheet2, etc) and P2 is the name they will be populated with. I however have three other sheets already named called 'Tables', 'Data' and 'Lists these are the sheets I would like to code to ignore.


  • And it will, as their names do not start with the word "Sheet." That's what "Like Sheet*" is looking for; the asterisk is a wildcard.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!