Excel VBA - Copy Sheet and rename

Hello Experts,

I have my code to copy the current sheet and rename it.  It is module.  But for some reason it will not let me rename it.  I keeps erroring out saying I can not duplicate a sheet name.  

I assume when I add a sheet, or copy a sheet the "new" sheet is the active sheet.  Here is my code:

Public Sub ADDMONTH()
   
    With ActiveWorkbook.Sheets
   
        ActiveSheet.Copy After:=Sheets(Sheets.Count)
       
    End With
     
    Range("L3").ClearContents
    Range("L3").Value = ActiveSheet.Previous.Range("L3").Value + 1
       
    ActiveSheet.Name = Format(ActiveSheet.Range("L3"), "mmmm")

end sub

What could I being doing wrong or what have I missed?

Any help would be appreciated -
Thanks
mike637Asked:
Who is Participating?
 
AlanConnect With a Mentor ConsultantCommented:
Do you have another worksheet with the same name already earlier in the workbook (the same month from last year perhaps)?

It might be a hidden worksheet, so check for that.

HTH,

Alan.
0
 
mike637Author Commented:
I found my mistake, I did not have the orignal cell set as readable date so it would not calc correctly.

Thank you for responding though.  The points are yours.

Michael
0
 
AlanConsultantCommented:
Glad its sorted :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.