0

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:

Sub RenameFromA4()
Dim Msg As String, i As Integer
For i = 5 To Sheets.Count
If Sheets(i).Range("A4").Value = "" Then
Msg = "Sheet " & i & "(" & Sheets(i).Name & ") 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 " & i & "(" & Sheets(i).Name & ") could not be renamed. Check if name already used."
MsgBox Msg, vbExclamation

End Sub

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: ="IF(AND(C4="",D4="",D3="",C3=""),TRIM((MID(A2,FIND(":",A2)+2,20))),"")&IF(IFERROR(FIND("West",A2),0)>0," W","")&" "&TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",255)),255))"

and pick from the error or just go back to rerunning the macro.

Any insight on how I can't structure this will be helpful.

user3666237
  • 153
  • 1
  • 6
  • 18
  • Somewhere after `ErrSheetName:` add the line `Sheets(i).Range("B3").Formula = "=A4"`, replacing `=A4` with the formula you want. Since you have quotes in your formula, you will need to replace those with multiples - perhaps build your formula into a text string first. – OldUgly Apr 13 '16 at 02:57

1 Answers1

1

You can explicitly check for the existence of a sheet named the same as the value in B4 by using a function like what is described here: Test or check if sheet exists. Then, you can insert something like the following between your On Error... and Sheets(i).Name...:

On Error GoTo ErrSheetName

If SheetExists(Sheets(i).Range("A4").Value) Then
  Sheets(i).Range("B3").Formula = "=IF(AND(C4="",D4="",D3="",C3=""),TRIM((MID(A2,FIND(": ",A2)+2,20))),"")&IF(IFERROR(FIND("West ",A2),0)>0,"W ","")&""&TRIM(RIGHT(SUBSTITUTE(A2,"",REPT("",255)),255))"
End If

Sheets(i).Name = Sheets(i).Range("A4").Value
Community
  • 1
  • 1
Steve S
  • 421
  • 3
  • 4