Dynamically rename worksheet (tab) name based on a cell value

[Capitan]

Hi all,


I've inherited a workbook with the following code which renames the worksheet (tab) name as per the contents of a given cell, say cell A1.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set Target = Range("A1")
If Target = "" Then Exit Sub
Application.ActiveSheet.Name = VBA.Left(Target, 31)
Exit Sub
End Sub


What i would like help with is concatenate the contents of cell A1 with the contents of cell A2 and display the result as the name of the sheet?

The other request ,which isn't critical, occurs when you copy the worksheet. Because two worksheet cannot have the same name, excel will throw an error. What i want to do is include an if statement to add 1 to the end of the name to avoid this error?

 

[ravishankar]

try these codes

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set Target = Range("A1")
If Target = "" Then Exit Sub
Application.ActiveSheet.Name = VBA.Left(Range("A1") & "-" & Range("b1"), 31)
Exit Sub
End Sub