Clear Sheet 2 before "copy/cutrow" Macro from Sheet 1

I have a macro to sort, cut rows from sheet 1 and paste into sheet 2.

My issue is that every time I populate sheet 1 and run the macro, sheet 2 has duplicate information/rows.

I want the macro to clear sheet 2 from row 2 down first, and then run/cut rows from sheet 1 into sheet 2. Essentially starting with a blank sheet.

The code I'm using is:

Option Explicit


Sub copyrows()

Dim rData As Range
Dim lRw As Long

With Sheets("Tab 2 - Closed Work")
lRw = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
End With

With Sheets("Tab 1 - Open Work")
Set rData = .Range(.Cells(2, 1), .Cells(.Rows.Count, 14).End(xlUp))

If Not .AutoFilterMode Then .Cells(1, 1).AutoFilter

rData.AutoFilter Field:=11, Criteria1:="CLOSED"
rData.Copy Sheets("Tab 2 - Closed Work").Cells(lRw, 1)
rData.SpecialCells(xlCellTypeVisible).EntireRow.Delete
.ShowAllData
End With

End Sub

Please use code tags with your code as per forum's rule.

 Sheets("Sheet2").Rows("2:" & Rows.Count).ClearContents

Thanks AB33, not sure what I thinking when I hit post...

Using your suggestion, how would I list in the code in its entirety? Where does your code go in relation to mine?

You could put it in any part of the code before you start copying in that sheet. Normally, put it after declaring the variables, so that you have cleared all existing data from rows 2 down ward, you then left with the header. When you run the rest of the code all will be in order. Sheet 2 in mine is where my existing data are and where I want to copy in to it from other sheets. So when I copy, I avoid having duplicates as I am re-running my code.

Well that just worked great. Thank you for your support.

Noty,
You are welcome!

Could you please now close (Mark) this thread as solved? Go in to the top right-hand side of this page, choose "Thread Tools" from the menu, then select "solved" from the drop down menu.