VBA Add 0 in front of a Single-digit Number

[Apple1]

Hi,

Using Excel 2010. I have the below macro to create new sheets. I want my MTH which is part of each sheet name to have a "0" in front if it is single digit.

MTH increases by 1 with each iteration.

If MTH <10, it should have a 0 in front.
IF MTH>=10, no need to have a 0 in front.

Sub GenSheets1()

Dim i As Integer
Dim Mth As String

Mth = "05"

Application.ScreenUpdating = False


        For i = 1 To 3
            Worksheets("Sheet1").Copy after:=Sheets(Sheets.Count)
            Mth = Mth + 1
            ActiveSheet.Name = "Book" & Mth
        Next

        Application.ScreenUpdating = True

End Sub

Thank you.

 

[dmt32]

Try this change to your code & see if does what you want:

Sub GenSheets1()


    Dim i As Integer
    Dim Mth As Integer
    
    Mth = 5
    
    Application.ScreenUpdating = False




    For i = 1 To 3
        Worksheets("Sheet1").Copy after:=Sheets(Sheets.Count)
        Mth = Mth + 1
        ActiveSheet.Name = "Book" & Format(Mth, "00")
    Next


    Application.ScreenUpdating = True


End Sub

Dave

 

[gentle2005]

Sub GenSheets1()


Dim i As Integer
Dim Mth As String


Mth = "08"


Application.ScreenUpdating = False




For i = 1 To 3
ThisWorkbook.Worksheets("Sheet1").Copy after:=Sheets(Sheets.Count)
Mth = Mth + 1
If Mth < 10 Then
Mth = "0" & Mth
End If
ActiveSheet.Name = "Book" & Mth
Next


Application.ScreenUpdating = True


End Sub

Hi,

Using Excel 2010. I have the below macro to create new sheets. I want my MTH which is part of each sheet name to have a "0" in front if it is single digit.

MTH increases by 1 with each iteration.

If MTH <10, it should have a 0 in front.
IF MTH>=10, no need to have a 0 in front.

Sub GenSheets1()

Dim i As Integer
Dim Mth As String

Mth = "05"

Application.ScreenUpdating = False


        For i = 1 To 3
            Worksheets("Sheet1").Copy after:=Sheets(Sheets.Count)
            Mth = Mth + 1
            ActiveSheet.Name = "Book" & Mth
        Next

        Application.ScreenUpdating = True

End Sub

Thank you.

 

[gentle2005]

Dear Apple
Hope below code helps, Mth could be declared as any value in the beginning as per your requirement.

Sub GenSheets1()


Dim i As Integer
Dim Mth As String


Mth = "08"


Application.ScreenUpdating = False




For i = 1 To 3
ThisWorkbook.Worksheets("Sheet1").Copy after:=Sheets(Sheets.Count)
Mth = Mth + 1
If Mth < 10 Then
Mth = "0" & Mth
End If
ActiveSheet.Name = "Book" & Mth
Next


Application.ScreenUpdating = True


End Sub