Rename Worksheets with VBA

[Lasa1]

Hello,

I have 2 questions on renaming worksheets:

1) f I tried to name the worksheet with the name of each month from January to December with code below, I got the error "Compiled error.Variable not defined". It seems that code mmmm is not read.

Option Explicit

Sub RenameSheets()
' copy Sheet 1 12 times, one per month
Dim x As Integer
For x = 1 To 12

Worksheets(1).Copy After:=Worksheets(x)
Worksheets(1 + x).Name = Format(DateSerial(1, x, 1), [B]mmmm[/B])

Next x
Worksheets(1).Delete

End Sub

2) If I tried to name the worksheet with numbers from 1 to 12 with code below, all sheets are correctly renamed except the 10th one which is named "137165" instead of "10".
Any idea?

Option Explicit

Sub RenameSheets()
' copy Sheet 1 12 times, one per month
Dim x As Integer
For x = 1 To 12

Worksheets(1).Copy After:=Worksheets(x)
Worksheets(1 + x).Name = Format(DateSerial(1, x, 1), x)

Next x
Worksheets(1).Delete

End Sub

Thanks!

 

[Fluff]

You are missing the quotes in the format

Worksheets(1 + x).Name = Format(DateSerial(1, x, 1), "mmmm")

 

[FuNeS13]

not sure about the second code, but the first one you just need to quote the mmmm

Option Explicit

Sub RenameSheets()
' copy Sheet 1 12 times, one per month
Dim x As Integer
For x = 1 To 12

Worksheets(1).Copy After:=Worksheets(x)
Worksheets(1 + x).Name = Format(DateSerial(1, x, 1), "mmmm")

Next x
Worksheets(1).Delete

End Sub

 

[Fluff]

If you just want the number rather than the month use

Worksheets(1 + x).Name = x

 

[Lasa1]

Wow, that was quick , thanks both!Question # 2 s driving me crazy.. cannot solve it..

 

[Fluff]

Question # 2 s driving me crazy.. cannot solve it..

See post#4, we posted at the same time

 

[FuNeS13]

If you just want the number rather than the month use

Worksheets(1 + x).Name = x

right on! and I would just turn off the DisplayAlerts in case he get the excel message about permanent deleting a sheet...

Option Explicit

Sub RenameSheets()
' copy Sheet 1 12 times, one per month
    Dim x As Integer
        For x = 1 To 12

    Worksheets(1).Copy After:=Worksheets(x)
    Worksheets(1 + x).Name = x

        Next x
        
    Application.DisplayAlerts = False
    Worksheets(1).Delete
    Application.DisplayAlerts = True

End Sub

 

[Lasa1]

It works now , thanks!

 

[Fluff]

Glad we could help & thanks for the feedback

 

[Fluff]

For referrence, as you want the month name you can use

   Worksheets(1 + x).Name = MonthName(x)

or for the abbreviated name

   Worksheets(1 + x).Name = MonthName(x, True)