VBA to copy row based on background color in separate file

[dingdang]

Hi,

I have customer data base having date of birth in column H, I have applied below conditional formula to change background color in RED if date of birth match with today's date.
( only DD-MM ).

I want vba code to copy all such row based on background color or based on today's birth day to new file will save auto on desktop with file name "Birthday-dd-mm-yyyy".

attached sample file for your ref.
https://dl.dropboxusercontent.com/u/66400357/Date of Birth.xlsx

 

[Smallman]

Hi DD

I had a look at your file and the following rolled for me.

Option Explicit

Sub SaveTodaysBirth()
Dim fname As String
fname = Format(Now, "dd_mm_yy")
'Filter by Items in Red.
    Range("B1:B10").AutoFilter 1, vbRed, xlFilterCellColor
    If Range("A" & Rows.Count).End(xlUp).Row > 1 Then
        Range("F1", Range("H65536").End(xlUp)).Copy
        Workbooks.Add
        [a1].PasteSpecial xlValues
        [a1].PasteSpecial xlPasteFormats
        ActiveWorkbook.SaveAs "C:\Users\HYMC\Desktop\Bdays " & fname & ".xls"
        ActiveWorkbook.Close False 'don't save see line above.
    End If
End Sub

You will need to change the path HYMC (Happy Young Married Couple) my wife's idea

The above makes the assumption that there will not be 65K people having a birthday on a single day. Anyways give it a crack.

Smallman

 

[dingdang]

Smallman,

Thanks.... its working fine but i want entire row with header to copy in new wb.
also auto filter still active after copy in original file.

Pls guide

 

[Smallman]

There is a line in the code which says copy F to H. Just think about it you don't want me to tell you how to change the code. You should be able to work that bit out.

Smallman