# Sheets for different birthdays based on month

#### zeoos2

##### New Member
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 Name Phone Number 13.01.1979 Roger 901823901 12.02.1977 John 10928390123 25.02.1991 Dana 0918239011 27.01.1920 Laura 09128390128 11.03.2005 Bill 1092839018.

<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.1979 Roger 901823901 27.01.1920 Laura 09128390128

<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

### 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
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
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