I have a spreadsheet with two tabs (Customer Survey Data) and (Customer Rollup). The data starts on line 5 (headers in rows 1 thru 4) and is found in columns B thru J. In column B I have dates with no blanks. In column C I have customer names - no blanks. In column J is are the customer comments - not all customers made a comment so there are blanks.

I created a macro that autofilters the data to show only those entries within a specific date range (dictated by dates found in L1 and L2 - begin and end dates respectively) by column B (no problem with this). The macro also auto removes all rows where there are no comments (column J = no blanks). This leaves me with only those surveys recieved within a specied date range that have comments.

Here is my problem: the Customer Rollup sheet is the finalized report. I need to copy only the customer names and their comments into the finalized report. The following is the macro I put in which only half works and I have no idea why. It copies the names fine...but then only copies the first comment.

Oh yea...I don't want to copy the WHOLE column...only from the first visible row down to the last.

Btw...I put a lot of notes for myself...I capitalized the notes where I think the problem is...Any suggestions on how to fix it?

Sub test()
'
' test Macro
' Macro recorded 10/14/2008 by jnickers
'
'*********************************************************************************************  
'This will select the Data sheet
    Sheets("Customer Survey Data").Select
'Filters out the data to show only those within the date range specified (Criteria 1 and Criteria 2)
    Selection.AutoFilter Field:=1, Criteria1:=">" & Range("l1").Value, Operator:=xlAnd, _
        Criteria2:="<" & Range("l2").Value
'Filters out the filtered data to show only those with Comments
    Selection.AutoFilter Field:=9, Criteria1:="<>"

'************************************************************************************
'This will copy all visible names in the data (after the filters above) and paste it onto the
'Rollup sheet
    Sheets("Customer Survey Data").Select
    Range("c5:c5").Select
    Range("c5:c5", ActiveCell.End(xlDown)).Select
    Selection.Copy
    Sheets("Customer Rollup").Select
    Range("a17").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
 '************************************************************************************
'This will copy all visible Comments in the data (after the filters above) and paste it onto the
'Rollup sheet (THIS IS THE PART THAT SEEMS NOT TO WORK - ONLY COPIES THE FIRST CELL IN THE COLUMN FROM THE DATA)
    Sheets("Customer Survey Data").Select
    Range("j5:j5").Select
    Range("j5:j5", ActiveCell.End(xlDown)).Select
    Selection.Copy
    Sheets("Customer Rollup").Select
    Range("c17").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
'*********************************************************************************************
'*********************************************************************************************
End Sub