Probably Simple

[jtsou]

I am trying to accomplish a couple things with macros. It's been a while since I used excel.

The workbook contains a sheet for every week in the year. The date in Cell A2 contains a date of every Monday in the year.

I want to run a macro that renames the worksheet to contents of A2(the date). After messing around I realize that I cannot have "/" in the name of the worksheet, so if possible I'd like the / replaced with - in the same instance.

I'd like to be able to run it on every sheet in the workbook besides the first two.

I've truly worked at this for an hour but keep running into errors whenever I research and attempt something new, namely Type Mismatch.

What I have so far-

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

Thank you!

 

[Michael M]

If A2:D2 is merged, you will need to refer only to the leftmost cell.. Try

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

 

[jtsou]

Perfect.

 

[Michael M]

Glad to help..