Copy some columns if date is not blank

[Rex44]

From sheet1 (roster) I want to copy to sheet2 (Birthday) all the members who's birthday
is in the month. (month is entered from input box) this works great for all months except December.

When December is selected it firsts copies the members who has no birthday (birthday cell is blank)
listed. Followed by the birthdays for the month of December.

The same story goes for Anniversaries.

inputnumber = InputBox("Enter Birthday By month number")
myrbdate = inputnumber      'Birthday month
Myaadate = inputnumber      'Anniversary Month
LastRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row

Sheet1.Range("A1").Select
For i = 2 To LastRow

    mybdate = Month(Cells(i, 9))  '<gets the month of cell (variable Row number i ,column 9)
    If myrbdate = mybdate Then
        erow = Bdays.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row
        Worksheets("Sheet1").Cells(i, 2).Copy _
                        Destination:=Sheets("bdays").Cells(erow, 2)
        Worksheets("Sheet1").Cells(i, 1).Copy _
                        Destination:=Sheets("bdays").Cells(erow, 3)
        Worksheets("Sheet1").Cells(i, 9).Copy _
                    Destination:=Sheets("bdays").Cells(erow, 4)
    End If
Next i

Results =

Day	First	Last	BDay
0​	Charlie	Agnew
0​	John	Anderson
0​	George	Asbury
0​	Bob	Austin
0​	Russ	Barker
0​	Ray	Romano
1​	Mark	Heitz	12/1/1941​
3​	David	Murphy	12/3/1946​
12​	Eric	Kisshauer	12/12/1945​
16​	Jack	Dennis	12/16/1950​
16​	Tom	McFarland	12/16/1942​
24​	Danny	Grant	12/24/1942​
25​	Mike	Bell	12/25/1950​
25​	Noel	Hayes	12/25/1937​
26​	Jim	Soule Jr.	12/26/1936​

 

[Legacy 143009]

Month() function returns 12 when the argument has zero or null value. That's why the code thinks the empty cells are December. You can try like this:

Sub test()
With Worksheets("Sheet1")
myrbdate = 12
LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
.Select
  For i = 2 To LastRow
    mybdate = IIf(.Cells(i, 9) > 0, Month(.Cells(i, 9)), 0) '<gets the month of cell (variable Row number i ,column 9)
    If myrbdate = mybdate Then
        erow = Sheets("bdays").Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row
        .Cells(i, 2).Copy _
                        Destination:=Sheets("bdays").Cells(erow, 2)
        .Cells(i, 1).Copy _
                        Destination:=Sheets("bdays").Cells(erow, 3)
        .Cells(i, 9).Copy _
                    Destination:=Sheets("bdays").Cells(erow, 4)
    End If
  Next i
End With
End Sub