Upcoming Birthdays

[campersand]

Hi, I have a workbook with a bunch of info about customers on the first worksheet, and various statistics and graphs on subsequent worksheets. I've been trying to come up with a way to import certain info (name, birth date, address, age, etc) from the first worksheet into a "birthdays" worksheet, and have the birthdays worksheet only display customers who have a birthday in the next 3 days, or at least sort by birthday so that it's easy to find those with upcoming birthdays. I'd like it to automatically update when more customers are added to the main worksheet so that I don't have to enter each customer's info twice.

I've worked at this for hours without much progress but I'm admittedly not an excel wizard at all. Does anyone have a solution? Please let me know if you need more info as well. Thanks in advance.

 

[ranman256]

Sub GetBirthdays()
Dim iOff As Long
Dim shDat As Worksheet, shRlt As Worksheet
Dim vBday, vName
Const kRESULT = "Result"
Const kPRIOR = 3   'days before bday
Const kCOLbday = 5   'the birthday column

Set shDat = ActiveSheet
Range("A2").Offset(0, kCOLbday - 1).Select
Sheets.Add
ActiveSheet.Name = kRESULT
Set shRlt = Sheets(kRESULT)
Range("A1").Value = "NAME"
Range("B1").Value = "BDAY"
Range("C1").Value = "DAYS"
Range("A2").Select
shDat.Activate

'assumes there is always data in the 1st column.
iOff = ActiveCell.Column - 1
While ActiveCell.Offset(0, -iOff).Text <> ""
   vBday = ActiveCell.Value
   If IsDate(vBday) Then
      If DateDiff("d", vBday, Date) <= kPRIOR Then
          vName = ActiveCell.Offset(0, -iOff).Text
             'post the birthday person
        shRlt.Activate
          ActiveCell.Value = vName
          ActiveCell.Offset(0, 1).Value = vBday
          ActiveCell.Offset(0, 2).Value = DateDiff("d", Date, vBday)
          
          ActiveCell.Offset(1, 0).Select    'next row
        shDat.Activate
      End If
   End If
   ActiveCell.Offset(1, 0).Select  'next row
Wend
shRlt.Activate
Set shDat = Nothing
Set shRlt = Nothing
End Sub

 

[campersand]

Hey ranman, thanks so much for the code. Not being much of an excel guy, I don't think I'm utilizing it correctly. I created a new macro and ran it, which created a new worksheet called "Result" with "NAME", "BDAY" and "DAYS" in cells A1, B1 & C1. Not sure what to do next...

 

[campersand]

bump

 

[DeezNuts]

I am a novice but think I got what you wanted done using formulas. IF not its closer and someone better at this can help


https://www.dropbox.com/s/pqkrit4py9fwkok/NV_StudentsSAMPLE.xlsx


for example when you fill down and you have empty birthday I dont know how to make those show blank. so they show a default excel date which is ugly maybe someone else can tinker with it.

 

[campersand]

Hey, thanks a lot for that. You got it closer than I could but it could use some more tweaking. The formula in the D column always returns "too late", because it's reading the year of the birth date and comparing it to today's date. That's something that I struggled with too.

It would be ideal if there was a way to either, A) Only show the birthdays within 3 days from today, or B) Sort by soonest birthday.

 

[DeezNuts]

Can sort this one
https://www.dropbox.com/s/xu7j31fft8t501w/NV_StudentsSAMPLEtable.xlsx?m=
Made a few changes but if you drag it further will see value errors and no idea how to make it ignore blank cells. but it a tad closer

 

[campersand]

Thanks guys, I got it figured out. I appreciate the help.