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
    Worksheets("PivotTables(1)").Activate


    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
    Next
    End Sub


    Thanks


    Richard

    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:

    Code
    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


    regards


    Richard

    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.


    Richard

    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!

Register Yourself Login