Help with copying data to another worksheet

[paulageville]

Hi all,

I have a macro which I want to search through a list of dates in a range, and for each date found where the given criteria is met, paste the column header into another sheet in the same workbook.
Here is my code...

<code>
Sub compile()


Application.ScreenUpdating = False
Sheets("Matrix").Activate
For Each Cell In Range("d6", "x92")
If Cell.Value = "" Then
ElseIf Cell.Value < Date - 365 Then
Cells(2, Cell.Column).Copy
Sheets("Overdue").Range("a3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlValues <<<<<<<<<<<<<<<< error
Else
End If
Next


End Sub

</code>

I keep getting a runtime error 1004 when i run it on the highlighted line.

Any ideas? This is driving me nuts!!

Thanks in advance

P

 

[Osvaldo Palmeiro]

Hi. Try this:

Sub compile2()
  Dim cell As Range, LR As Long
  LR = Sheets("Overdue").Cells(Rows.Count, 1).End(xlUp).Row
  If LR < 3 Then LR = 3
    With Sheets("Matrix")
      For Each cell In .Range("d6", "x92")
        If cell.Value <> "" And cell.Value < Date - 365 Then
          Sheets("Overdue").Cells(LR + 1, 1).Value = cell.Value
          LR = LR + 1
        End If
      Next
    End With
End Sub