Macro to change tab name based on cell value with special duplicate handling
<p>I have a macro that changed a tab name based on cell value (A4) which contains a formula to give the sheet a unique name, but I wanted to see if it was possible to create special case handling occurrences when there's a duplicate.  So here's the code:</p>

<pre><code>Sub RenameFromA4()
Dim Msg As String, i As Integer
For i = 5 To Sheets.Count
If Sheets(i).Range("A4").Value = "" Then
Msg = "Sheet " &amp; i &amp; "(" &amp; Sheets(i).Name &amp; ") has no value in A4. Fix sheet, then rerun."
MsgBox Msg, vbExclamation
Exit Sub
Else
On Error GoTo ErrSheetName
Sheets(i).Name = Sheets(i).Range("A4").Value
On Error GoTo 0
End If
Next i
Exit Sub
ErrSheetName: Msg = "Sheet " &amp; i &amp; "(" &amp; Sheets(i).Name &amp; ") could not be renamed. Check if name already used."
MsgBox Msg, vbExclamation

End Sub
</code></pre>

<p>The trouble I run into is sometimes duplicates can arise and error out my whole macro where it comes to a complete halt.  So I want to add a sequence that when the macro encounters a duplicate add the following formula in cell B3:  <code>="IF(AND(C4="",D4="",D3="",C3=""),TRIM((MID(A2,FIND(":",A2)+2,20))),"")&amp;IF(IFERROR(FIND("West",A2),0)&gt;0," W","")&amp;" "&amp;TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",255)),255))"</code></p>

<p>and pick from the error or just go back to rerunning the macro.</p>

<p>Any insight on how I can't structure this will be helpful.</p>