Need to clear sheets everytime MAcro is run

This macro copies a row to another spreadsheet depending on the text contained in the status column. However when you change the status it does not remove it from the previous status sheet it just places another copy on the new status sheet.(there should only be 1 instance of each row in the sheets broken out of the master sheet) Is there a way to clear all the sheets first evreytime the Macro is run and then copy ??


[CODE]

Sub tgr()

Const TableCols As String = "A:N"
Const StatusCol As String = "L"

With Sheets("New Carrier Leads")
Static rngTable As Range: Set rngTable = Intersect(.UsedRange, .Columns(TableCols))
Static rngStatus As Range: Set rngStatus = Intersect(.UsedRange, .Columns(StatusCol))
End With

Dim StatusCell As Range
Dim strStatus As String
Dim arrStatus() As Variant
Dim arrMax As Long
Dim AlreadyFiltered As Boolean

Application.ScreenUpdating = False
For Each StatusCell In rngStatus

Select Case Trim(StatusCell.Value)
Case vbNullString: strStatus = "Blank"
Case Else: strStatus = Trim(StatusCell.Value)
End Select

AlreadyFiltered = False
On Error Resume Next
AlreadyFiltered = IsNumeric(WorksheetFunction.Match(strStatus, arrStatus, 0))

If Not AlreadyFiltered Then

arrMax = arrMax + 1
ReDim Preserve arrStatus(1 To arrMax)
arrStatus(arrMax) = strStatus

Select Case strStatus
Case "Blank": rngStatus.AutoFilter 1, "="
Case Else: rngStatus.AutoFilter 1, strStatus
End Select

rngTable.Copy
With Sheets(strStatus).Range("A1")
.PasteSpecial xlPasteColumnWidths
.PasteSpecial xlPasteFormats
.PasteSpecial xlPasteValues
End With

End If

Next StatusCell

rngStatus.AutoFilter
Application.ScreenUpdating = True

End Sub
[CODE]

Hello Fferjay,

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here