I have a userform listbox which allows users to select any of the worksheets from the workbook. Then, after selecting the worksheet they want copied, I would like to change the name of that worksheet with VBA. The problem is that when the sheet gets copied, Excel automatically adds "(2)" to avoid a duplicate sheet name. There comes my issue to identify the sheet which needs to be renamed. Here is my code. The last line of code is my issue.
Private Sub CommandButton_OK_Click() CopySheetName = ListBox_SheetNames.Value 'The listbox value is whichever sheet the user choose to copy from the userform' 'Creates new sheet named "New Sheet Name" Sheets(CopySheetName).Visible = True Sheets(CopySheetName).Select Sheets(CopySheetName).Copy After:=Sheets("Summary Sheet") Sheets(CopySheetName) + "(2)".Name = "New Sheet Name" End Sub