VBA Code to Copy / Paste efficiently and re-apply

[Kemidan2014]

I posted in an earlier thread and found some answers however I still lack some finishing touches to the code to complete the task i would like to do.
Macro I would like to achieve is this
Copy Rows based on matching Criteria (so far i have that working)
Paste rows as values in another sheet (also seems to work)
Stops looking to copy and paste when it gets to the bottom rows of data on Sheet 1 that are Vlookups with no value (#N/A) (this is where i struggle)

I would like the pasting on the new sheet to always start in a specific row so that the headers aren't copied over
I would like when i rerun the macro for the previous pasted data to be cleared and then new updated data inserted.

Below is sample of my code that I crowdsourced from google. Ideally I wanted to Tie this macro to a shape button so over time as we click it it will reupdate "Sheet2"

Thank you readers for your time!

Sub Filter_Data()
lastrow = Worksheets("sheet1").Range("A" & Rows.count).End(xlUp).Row

For r = 6 To lastrow
 If Not Error("Sheet1").Cells("G") Then
    If Worksheets("Sheet1").Range("D" & r).Value = "0101-6" And Worksheets("Sheet1").Range("G" & r).Value <> "Closed-Cancelled" And Worksheets("Sheet1").Range("G" & r).Value <> "Closed - LTCM Effective" And Worksheets("Sheet1").Range("G" & r).Value <> "Closed - LTCM Ineffective" And Worksheets("Sheet1").Range("G" & r).Value <> "Closed-No Response Required" And Worksheets("Sheet1").Range("G" & r).Value <> "#N/A" Then
      Worksheets("Sheet1").Rows(r).Copy
      Worksheets("Sheet2").Activate
      lastrowaact = Worksheets("Sheet2").Range("A" & Rows.count).End(xlUp).Row
      Worksheets("Sheet2").Range("A" & lastrowaact + 1).PasteSpecial Paste:=xlPasteValues
        
    End If
 End If
 
End Sub

 

[mumps]

That should work.

 

[Kemidan2014]

OK i tested it with my main spreadsheet that i have been working out of and it didn't error out but however it only pulled out 7 rows of information when there should be 18 or so that i know are open (based on what we are manually handwriting and keeping up with on a white board in a room) and form what i have manually filtered out in the past using this data.

Not sure if this is related to me giving you only a finite amount of information when i have 1045 rows of data to parse in reality do you have any tweeks in the code i should look at to accomodate this?

 

[Kemidan2014]

NM i grew impatiant at my slow computer and hit escape early which cut the macro short. i excercised patience and got my results. i am marking as solved. Thank you for your support!

 

[mumps]

You are very welcome.