Rename Worksheet Function Not Working

[charllie]

Hi There

I have created the following function which makes a copy of the worksheet "January" and then creates a new sheet.

It then enters the current month in cell C5 and current year in D5 of the new worksheet.

I then want it to rename the sheet to the current month using the value in cell C5. It all works up this stage but i am unable to rename the new worksheet.

The code stops at "ActiveSheet.Name = Range("C5").Value" highligting it yellow.

Sheets("January").Copy After:=Sheets(Sheets.Count)
    
    Sheets("January (2)").Select
        
    With Range("C5")
        .Value = Date
        .NumberFormat = "mmmm"
    End With
    
    With Range("D5")
        .Value = Date
        .NumberFormat = "yyyy"
    End With

    ActiveSheet.Name = Range("C5").Value

Would appreciate any help in putting this right.

Thanks

Charllie

 

[Peter_SSs]

The value in C5 is a date, presumably containing "/" characters (illegal in a sheet name) even though you have formatted it to just show the month.

Try this instead

ActiveSheet.Name = Range("C5").Text

 

[charllie]

The value in C5 is a date, presumably containing "/" characters (illegal in a sheet name) even though you have formatted it to just show the month.

Try this instead

ActiveSheet.Name = Range("C5").Text

Hi Peter

Thank you very much, that works perfectly. Spent ages trying to figure out what i was doing wrong and didnt even thing of that, even after reading the error message.

Thanks again, i appreciate your time.

Charllie

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

BTW, for the future it would help if you also actually quote the full error message or include a picture of it (as well as the offending code line - which you did. )

 

[charllie]

You're welcome. Thanks for the follow-up.

BTW, for the future it would help if you also actually quote the full error message or include a picture of it (as well as the offending code line - which you did. )

Thanks Peter

Its been a while since i raised an issue on here so forgot the error message but will remember for next time, as there always is a next time

Thanks again.

 

[Peter_SSs]

forgot the error message but will remember for next time