way to create named tab sheets

[sam__gleeson]

Hi

I have a workbook which needs a new sheet for each day of the month.I normally just create and re-name each sheet with the date...is there a simpler way where i can create the new sheet with a date for each month instead of doing indiviually?

Thanks
Excel 2010

 

[Armando Montes]

Hi, here is one way:
Sheets for Days (Microsoft Excel)

 

[dmt32]

Hi

I have a workbook which needs a new sheet for each day of the month.I normally just create and re-name each sheet with the date...is there a simpler way where i can create the new sheet with a date for each month instead of doing indiviually?

Thanks
Excel 2010

Hi,
see if this does what you want. Place code in standard module.

Sub DayMonthTab()
    Dim TabName As Worksheet
    On Error Resume Next
    Set TabName = Worksheets(Format(Date, "dd-mmm"))
    If Err.Number = 9 Then
    'add new worksheet & name with next day date
    Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name = CStr(Format(Date, "dd-mmm"))
    End If
    On Error GoTo 0
End Sub

Dave

 

[sam__gleeson]

Hi
Thanks
I inserted a module and ran the code but it only produced 1 sheet - 07 jan

Thank you

 

[dmt32]

Hi
Thanks
I inserted a module and ran the code but it only produced 1 sheet - 07 jan

Thank you

Sorry, misunderstood the post. To create a whole month of worksheets try following:

Sub EachDayOfMonthTab()
    Dim TabName As Worksheet
    Dim DaysInMonth As Integer
    Dim i As Integer
    
    DaysInMonth = Day(DateSerial(Year(Date), Month(Date) + 1, 1) - 1)
    
    For i = 1 To DaysInMonth
    On Error Resume Next
    Set TabName = Worksheets(i & "-" & MonthName(Month(Date), True))
    If Err.Number = 9 Then
    'add new worksheet & name with next day date
    Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name = i & "-" & MonthName(Month(Date), True)
    On Error GoTo 0
    End If
    Next i
End Sub

Dave

 

[sam__gleeson]

Thanks Dave
Works great
1. Not sure if this is possible but can a messagebox be used to select which month to create
2. Can the 3 sheets without a date be used as 1st,2nd & 3rd dates of the month or must sheets 1,2 &3 always be visible (can the code delete maybe once the sheets with dates are created

Thanks again

 

[dmt32]

Thanks Dave
Works great
1. Not sure if this is possible but can a messagebox be used to select which month to create
2. Can the 3 sheets without a date be used as 1st,2nd & 3rd dates of the month or must sheets 1,2 &3 always be visible (can the code delete maybe once the sheets with dates are created

Thanks again

Hi,
Code can be amended to do what you ask but think you will find similar solution to point 1 was offered via a link by another contributor & may be worth you investigating that approach.

point 2 - I take it you already have three sheets in your workbook that are not named with day / month names & you want to include then in the naming process???

Dave

 

[sam__gleeson]

Thanks Dave

No.2 Yes

 

[dmt32]

Thanks Dave

No.2 Yes

Hi bit short of time today, dealing with small amount storm damage on property.

I have updated code to do what I think you want. When offering inputbox for users to enter data you need to trap any rubbish they may enter otherwise code fails. I have therefore created a function to do this & that should allow users to enter months both in either Alpha (Jan or January etc) or numeric format. (1,2,3 etc)

I have also updated the code to rename the first three worksheets in your workbook with the first 3 day names of chosen month.

Copy both proceures to standard module & see how you get on.

Sub EachDayOfMonthTab()
    Dim TabName As Worksheet
    Dim sMonth As Integer
    Dim DaysInMonth As Integer
    Dim i As Integer
    sMonth = GetMonth
    If sMonth = 0 Then Exit Sub
    DaysInMonth = Day(DateSerial(Year(Date), sMonth + 1, 1) - 1)
    For i = 1 To DaysInMonth
        On Error Resume Next
        Set TabName = Worksheets(i & "-" & MonthName(sMonth, True))
        If Err.Number = 9 Then
            If i < 4 Then
                Worksheets(i).Name = i & "-" & MonthName(sMonth, True)
            Else
                'add new worksheet & name with next day date
                Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name = _
              i & "-" & MonthName(sMonth, True)
            End If
            On Error GoTo 0
        End If
    Next i
End Sub


Function GetMonth() As Integer
    Dim GetInput As Variant
    Dim MonthArr(12) As Variant
    Dim msg As String
    Dim abbrv As Boolean
    Dim i As Integer
    msg = "Please Enter Required Month Name" & Chr(10) & _
        "          E.G. Jan or January" & Chr(10) & Chr(10) & _
        "                 or" & Chr(10) & Chr(10) & _
          "You Can Enter Months Numeric Value" & Chr(10) & _
        "          E.G 1 = January"
Retry:
    GetInput = InputBox(msg, "Enter Month")
    If StrPtr(GetInput) = 0 Then
        GetMonth = 0
        Exit Function
    ElseIf Len(GetInput) = 0 Then
        MsgBox "Month Cannot be Blank", 16, "Entry Required"
        GoTo Retry
    Else
        If IsNumeric(GetInput) Then
            If GetInput > 0 And GetInput < 13 Then
                GetMonth = CInt(GetInput)
                Exit Function
            Else
                MsgBox "Invaild Input" & Chr(10) & _
                       "Please Enter A Valid Month Name Or Number", 16, "Invalid Input"
                GoTo Retry
            End If
        Else
            If Len(GetInput) = 3 Then abbrv = True
            For i = 1 To 12
                MonthArr(i - 1) = MonthName(i, abbrv)
            Next i
            On Error Resume Next
            GetMonth = Application.Match(GetInput, MonthArr, False)
            If IsError(GetMonth) Then GetMonth = 0
            On Error GoTo 0
        End If
    End If
End Function

Dave

 

[sam__gleeson]

Thanks Dave

Hope all is well with the property

I will check out the code later today....thanks for the help...will let you know