jewkes6000
Board Regular
- Joined
- Mar 25, 2020
- Messages
- 60
- Office Version
- 365
- Platform
- Windows
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.
VBA Code:
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