Excel VBA - Copy Sheet and rename

Posted on 2013-01-20
Medium Priority
Last Modified: 2013-01-20
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").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 -
Question by:mike637
  • 2
LVL 22

Accepted Solution

Alan earned 2000 total points
ID: 38799502
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.



Author Closing Comment

ID: 38799514
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.

LVL 22

Expert Comment

ID: 38799545
Glad its sorted :-)

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

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.

Join & Write a Comment

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

587 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question