Name a sheet from a cell, with error checking

[beezlebob999]

I am trying to get my sheets to name themselves from a cell within the sheet. I have tried several codes found on this and other forums but the best I have managed is to get it to do it once then come up with errors when it works with a macro I made to add data from another sheet and create a blank copy of the destination worksheet. Hope this makes sense, I would be really grateful for any help people could give. My understanding of VB is basic so please give idiots guides. thanks

 

[Fishboy]

I am trying to get my sheets to name themselves from a cell within the sheet. I have tried several codes found on this and other forums but the best I have managed is to get it to do it once then come up with errors when it works with a macro I made to add data from another sheet and create a blank copy of the destination worksheet. Hope this makes sense, I would be really grateful for any help people could give. My understanding of VB is basic so please give idiots guides. thanks

Hi beezlebob999, welcome to the boards.

Below is some modified code I found online a while ago which should do what you need. It just needs to be added to a standard VBA module in your workbook then applied to a button to run it.

Sub Rename_Tabs()
' Defines ws as Worksheet
Dim ws As Worksheet
' For each tab in your workbook...
For Each ws In Worksheets
' If an error occurs, continue...
    On Error Resume Next
' If the length of value in cell A1 is greater than 0 then...
    If Len(ws.Range("A1")) > 0 Then
' Rename tab to the value of A1, replacing illegal characters to reduce errors
        ws.Name = Replace(ws.Range("A1").Value, "/", "-")
    End If
    On Error GoTo 0
' If the tab was not renamed...
    If ws.Name <> Replace(ws.Range("A1").Value, "/", "-") Then
' Display messagebox containing message below
        MsgBox ws.Name & " Was Not renamed, the suggested name was invalid"
    End If
Next
End Sub

You can replace the bold red A1 cell reference with whichever cell contains your desired name for renaming to.

I hope this helps.