I have created an excel macro in VB which searches through a column of values for a given date, it then pastes the row into a second sheet. This works perfectly fine except:-

1. I encountered problems when I tried to put a link to a macro "NewDeals" in Column A of each line, things got very messy, then paste the copied row starting at B instead of A.
2. I also tried to set the print area to stop when there are no more values however I think it sets the print area before the macro finishes copying and pasting so it only prints my original text.
3. I would eventually like to be able to set the date externally without having to enter the macro, I'm guessing this is quite easy so it is less important as I will probably search for an answer once everything else works ok.

Thanks for any contributions. 

Sub Date search()

    Dim LSearchRow As Integer
    Dim LCopyToRow As Integer
    Dim x As Long, lastCell As Range  

    On Error GoTo Err_Execute
    
    LSearchRow = 4
    LCopyToRow = 7
    
    While Len(Range("A" & CStr(LSearchRow)).Value) > 0
        
        If Range("F" & CStr(LSearchRow)).Value = "06/02/2008" Then
            Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
            Selection.Copy
            Sheets("Current Deals").Select
            Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
            ActiveSheet.Paste
            LCopyToRow = LCopyToRow + 1
            Sheets("Deal History").Select
            
        End If
        
        LSearchRow = LSearchRow + 1
        
    Wend

    x = ActiveSheet.UsedRange.Columns.Count
    Set lastCell = Cells.SpecialCells(xlCellTypeLastCell)
    ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1), lastCell).Address
    Application.CutCopyMode = False
    Range("A3").Select
    
    
    Exit Sub
    
Err_Execute:
    MsgBox "Something screwed up"

x = ActiveSheet.UsedRange.Columns.Count
Set lastCell = Cells.SpecialCells(xlCellTypeLastCell)
ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1), lastCell).Address

End Sub