Sheets for different birthdays based on month

zeoos2

New Member
Joined
Feb 12, 2016
Messages
1
Hey there.
I need to make an excel with 13 sheets.
12 Sheets with every month of the year, and 1 sheet that will contain the "database".
So we have the sheets "January, February etc" and the sheet "Database"

In the "Database" sheet, we have a list of people where column A is the Birth Date, column B the name and column C the Phone Number.
Birth Date
NamePhone Number
13.01.1979Roger901823901
12.02.1977John10928390123
25.02.1991Dana0918239011
27.01.1920Laura09128390128
11.03.2005Bill1092839018.

<tbody>
</tbody>


My goal is to put in each sheet the people who's birth month matches that sheet.
So Roger and Laura will be in "January" sheet, Bill in "March" sheet and so on.

Example:
"January" sheet:

Birth Date
Name
Phone Number
13.01.1979Roger901823901
27.01.1920Laura09128390128

<tbody>
</tbody>

I tried doing with VLOOKUP and INDEX MATCH but with no luck so far. I am using MID function to select the people with birthday in January
=MID(A2,4,2)

Maybe you can throw on a couple of tips.

Thanks
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,900
If you are willing to try a macro, do the following: Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the menu at the top click 'Insert' and then click 'Module'. Copy and paste the macro into the empty code window that opens up. Press the F5 key to run the macro. Close the code module window to return to your sheet. There are other quicker ways to run the macro such as assigning it to a button that you would click on your sheet or assigning it to a short cut key.
Code:
Sub AddSheet()
    Dim bottomA As Long
    bottomA = Sheets("Database").Range("A" & Rows.Count).End(xlUp).Row
    Dim c As Range
    Dim rng As Range
    Dim ws As Worksheet
    For Each c In Range("A2:A" & bottomA)
        Set ws = Nothing
        On Error Resume Next
        Set ws = Worksheets(Format(c.Value, "mmmm"))
        On Error GoTo 0
        If ws Is Nothing Then
                Worksheets.Add(After:=Sheets(Sheets.Count)).Name = Format(c.Value, "mmmm")
                Sheets("Database").Rows(1).EntireRow.Copy ActiveSheet.Cells(1, 1)
        End If
    Next c
    For Each ws In Sheets
        If ws.Name <> "Database" Then
            ws.UsedRange.Offset(1, 0).ClearContents
        End If
    Next ws
    For Each rng In Sheets("Database").Range("A2:A" & bottomA)
        rng.EntireRow.Copy Sheets(Format(rng.Value, "mmmm")).Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    Next rng
 End Sub
The macro creates the monthly sheets for you and then copies over the data so you only need the "Database" sheet.
 
Last edited:

SpillerBD

Well-known Member
Joined
Jul 2, 2014
Messages
2,706
Add a column to display the Month as TEXT
Code:
=TEXT(A2,"MMMM")
Select a cell in the range or the entire range. Turn it into a Table; Insert ribbon, TABLE
Now select Summarize with Pivot Table.
Place the new field in the Filter area for the PivotTable.
Drag BirthDate to Row
Drag Name to Row
Drag Phone number to Row

Now following this "menu" path; PivotTable Tools/Design select Report Layout/Show in Tabular Form
then select Report Layout/Repeat All Item Labels
then Subtotals/Do Not Show Subtotals
then Grandtotals/Off for Rows and Columns

Now the cool kicker!
PivotTable Tools/Analyze
PivotTable Options (drop down to) Show Report Filter Pages
 

Watch MrExcel Video

Forum statistics

Threads
1,095,364
Messages
5,444,025
Members
405,260
Latest member
Khauff

This Week's Hot Topics

Top