'Runtime Error 1004' when trying to Rename Worksheet using vba code

[Raporter65]

There is probably a simple solution for my problem, however I am just learning VBA.

I am using the attached code to name a worksheet. It works fine until I use a name that is already used on a worksheet in the workbook.

I want to have the option to enter another name that is not a duplicate.

Any suggestions would be accepted with gratitude.

ThanksName.txt
Name2.txt

[Trebor76]

Hi there,

Try this:

Please Login or Register  to view this content.

Regards,

Robert

[Raporter65]

Thanks Trebor76,

I should have included the rest of the code.

The full code is as below. Would you please suggest where to insert the code you suggested?

Private Sub CommandButton9_Click()
Workbooks.Open Filename:=("x:\purchasing\Current Contracts\Purchasing Workbook\JPACEMaster.xlsx")
Windows("JPACE.xlsm").Activate
Sheets("JPACE-SAGE").Select
Sheets("JPACE-SAGE").Unprotect
Range("A1:L81").Copy
Windows("JPACEMaster.xlsx").Activate
Sheets("Sheet1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
wsName = InputBox("Enter DO# and type of sheet", "Rename Sheet")
If wsName <> "" Then
With ActiveSheet
.Name = wsName
End With
End If
ActiveWindow.Zoom = 75
ActiveSheet.Range("a1").Select
ActiveSheet.Protect
Dim ws As Worksheet
Set ws = Sheets.Add
Worksheets("Sheet1").Move _
Before:=Worksheets("Sheet2")
ActiveWorkbook.Save
ActiveWorkbook.Close
Windows("JPACE.xlsm").Activate
ActiveSheet.Protect
End Sub


Thanks for your help in advance!

[Trebor76]

Untested, but try this:

Please Login or Register  to view this content.

You must also make sure to wrap any code you post with the appropriate tags as I have done.

Robert

[Raporter65]

Thanks Trebor76. This worked. I was not able to add the 'Option Explicit' to the sub, however the code worked anyhow.
Would you please explain how to wrap my code that I post with the appropriate tag? I am new to this and do not understand what you mean.

Once again, thanks for your help in resolving my issue.

Rob

[Trebor76]

I was not able to add the 'Option Explicit' to the sub

Then you must be variables that aren't defined like I did with the varWSName variable. This means that these variables default to a variant type, the most expensive type (in terms of memory) of a variable.

Would you please explain how to wrap my code that I post with the appropriate tag?

After you copy in any code, highlight it and click on the hash # icon in the menu.

HTH

Robert