Debug tabs name to correspond with cell value.

[mrjinx007]

Hi,
I have this code that worked in another program but now that I use it in a new one, it converts all the tab names to dates except for the last three ones. The only thing that I changed was the tabs number. The old one was from 11-22 this one is 9-20

Private Sub Worksheet_Change(ByVal Target As Range)    If Intersect(Target, Range("AB2")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim x As Long, y As Long
    y = 0
    For x = 9 To 20
        Sheets(x).Name = "Sheet" & x
    Next x
    For x = 9 To 20
    
        Sheets(x).Name = Replace(DateAdd("m", y, Range("AB2").Value), "/", "-")
        Sheets(x).Name = Left(MonthName(Month(Sheets(x).Name)), 3)
        y = y + 1
    Next x
    Application.ScreenUpdating = True
End Sub

The debug highlights this part of the code:

Sheets(x).Name = Left(MonthName(Month(Sheets(x).Name)), 3)

Thank you

 

[Fluff]

What is the name of the sheet when you get the error?

 

[mrjinx007]

Sheet4 (Jan).
The other form it is sheet1

 

[Fluff]

The code has already changed that sheet name, what is the name of sheets(x) when the error occurs?
Also what is the error number & message?

 

[mrjinx007]

Sheet13(11/1/2019)
1004 Run time error. Can not rename a sheet to the same name as another sheet,....

 

[mrjinx007]

There is a sheet71 after sheet7 and a sheet91 after sheet9.

 

[Fluff]

The error message is telling you that you already have a sheet called Jan or Nov depending on your date format

 

[mrjinx007]

Is there a way I can share this with you via dropbox?

 

[Fluff]

Yes, you can upload to dropbox & provide the link in the thread

 

[mrjinx007]

Here