Paste data from One Sheet to Another

[mar1618]

Hi All:

I'm trying to copy data from one sheet to another. The following code is working but the problem is that it only copies the first line and it does not select any other breached or warning ticket(see attachment) If you open the sheet the code won't work because I set the Range to "D7:AC7" (See the commented lines below):

Option Explicit
Private Sub Worksheet_Activate()
Dim LR As Long

Me.UsedRange.Offset(17).ClearContents

With Sheets("Raw - Incident Request Report")
      .AutoFilterMode = False
      .Range("D7:AC7").AutoFilter 29, Criteria1:="Breached", Criteria2:="Warning" 'This is the part that is not working properly if I change this to "D7:AF7" it works but it only grabs the first Breached ticket
      LR = .Range("D" & .Rows.Count).End(xlUp).Row
      If LR > 1 Then
          .Range("AC7:AC" & LR).Copy Range("C17") 'In here I need to make this so that it only copies the information not the formatting
          .Range("D7:D" & LR).Copy Range("D17")
          .Range("I7:I" & LR).Copy Range("E17")
          .Range("K7:K" & LR).Copy Range("F17")
          .Range("T7:T" & LR).Copy Range("G17")
      Else
           Range("C17") = "No Data Found"
      End If
      .AutoFilterMode = False
End With
End Sub

Can anyone help solve this issue, please?

Here is the link to the file https://dl.dropboxusercontent.com/u/55305970/Report.xlsm