Filtering a range, copying filtered cells to new sheet in same workbook

[netrixuser]

Hi all,

My goal is to filter a list of alerts [in worksheet "Scratch Sheet"], on column A - for the word NEW - and paste the filtered results to worksheet "Current Alerts" (same workbook)
I have the code below but odd things are happening.
Firstly
As it stands the code throws an error at the line for the copy/paste - but the code does actually paste the data.

[If I insert an "on error resume next" above that line the code runs]

The top row of the source data (on Scratch Sheet is getting copied irrespective of whether is says NEW or not.

To test I have 4 rows of data in Scratch Sheet:

When I run the code the filter looks like this, row 1 is visible and the filter is starting at row 2?

And when the code executes I get this on the Current Alerts sheet - row 1 has also been copied (confirmed by the "1" taken from Column B)
I'm assuming because of the Current Region / visible cells piece in the code

If there are no instances of NEW the filter still "filters" - I would need some sort of error checking to account for this - with four rows all set to CLOSED:

when the filter runs with no "NEW" :

Now the code - I'm certain there is something "obvious" ......

Sub CopyNew()
Dim ws As Worksheet
Set ws = ActiveWorkbook.Sheets("Scratch Sheet")
Dim ws1 As Worksheet
Set ws1 = ActiveWorkbook.Sheets("Current Alerts")

Dim lastRow As Long
Dim lastRow1 As Long

 
lastRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
lastRow1 = ws1.Range("A" & ws1.Rows.Count).End(xlUp).Row

On Error Resume Next

ws.Range("A1").AutoFilter field:=1, Criteria1:="NEW"
ws.Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy ws1.Range("A" & (lastRow1))
'Sheets.Add
'ActiveSheet.Paste

 

ws.ShowAllData
Set ws = Nothing
Set ws1 = Nothing

End Sub

As always, thanks in advance for all who take the time to help me, it is always very much appreciated.

 

[RoryA]

Autofilter will always assume that the first row of your range is headers and you cannot alter that.

 

[netrixuser]

Autofilter will always assume that the first row of your range is headers and you cannot alter that.

Thank you Rory - I suppose I'll either have to add an offset of some sort to the copy / paste section or scrap filters and use a loop of some sort for when active cell.value = NEW ?

 

[RoryA]

If your first row is really data, then you will have to insert an actual header row to use autofilter.

 

[netrixuser]

If your first row is really data, then you will have to insert an actual header row to use autofilter.

Thanks RoryA,
New problem though - if I insert a header row before filtering, the copy command then includes the header row - how would I get around that ?

 

[RoryA]

Something like this should do it:

Dim copyRange as range
set copyrange = ws.Range("A1").CurrentRegion
if copyrange.columns(1).specialcells(xlcelltypevisible).count > 1 then ' only copy if there is more than the header row visible
set copyrange = copyrange.resize(copyrange.rows.count - 1).offset(1) ' offset by one and reduce row count by one to skip header row
copyrange.copy destination:=ws1.Cells(lastRow1, "A")
end if

 

[netrixuser]

Something like this should do it:

Dim copyRange as range
set copyrange = ws.Range("A1").CurrentRegion
if copyrange.columns(1).specialcells(xlcelltypevisible).count > 1 then ' only copy if there is more than the header row visible
set copyrange = copyrange.resize(copyrange.rows.count - 1).offset(1) ' offset by one and reduce row count by one to skip header row
copyrange.copy destination:=ws1.Cells(lastRow1, "A")
end if

It certainly does - and also helps me with other sections of my overall code that I was struggling with - thank you RoryA !