brianfosterblack
Active Member
- Joined
- Nov 1, 2011
- Messages
- 251
I am using this code to copy a worksheet and rename the copied sheet
This works perfectly but I run into a problem if I name the sheet with a name that already exists
I then get this message "Run Time error 1004 That name is already taken, try a different 1"
Even though I end the sub, a worksheet has been copied with the name of the sheet I copied with the word copy added to the name
I have no idea how to handle errors in VBA. I have tried to read up on this but it is a little over my head.
Can someone please help with the code I need.
VBA Code:
Sub CopySheet()
Dim ShName As String
With ActiveSheet
ShName = .Name & "Copy"
.Copy After:=Sheets(Worksheets.Count)
End With
Sheets(Worksheets.Count).Name = ShName
ActiveSheet.Name = InputBox("Enter the New Sheet name" & vbCr & "making sure it is only one word")
End Sub
I then get this message "Run Time error 1004 That name is already taken, try a different 1"
Even though I end the sub, a worksheet has been copied with the name of the sheet I copied with the word copy added to the name
I have no idea how to handle errors in VBA. I have tried to read up on this but it is a little over my head.
Can someone please help with the code I need.