way to create named tab sheets

sam__gleeson

Board Regular
Joined
Oct 11, 2012
Messages
102
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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,672
Office Version
  1. 2019
Platform
  1. Windows
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.

Code:
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

Board Regular
Joined
Oct 11, 2012
Messages
102
Hi
Thanks
I inserted a module and ran the code but it only produced 1 sheet - 07 jan

Thank you
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,672
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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:

Code:
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
 
Last edited:

sam__gleeson

Board Regular
Joined
Oct 11, 2012
Messages
102
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

Well-known Member
Joined
Jul 3, 2012
Messages
6,672
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,672
Office Version
  1. 2019
Platform
  1. Windows
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.

Code:
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
 
Last edited:

sam__gleeson

Board Regular
Joined
Oct 11, 2012
Messages
102
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
 

Forum statistics

Threads
1,137,061
Messages
5,679,388
Members
419,825
Latest member
MegastarMagus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top