title: Move rows from worksheet1 to worksheet2 based on cell value in column AB and paste as values
body: <p>I need to selectively copy entire rows from sheet1 to sheet2. I would like to use a "No" value column "AB" to select the applicable rows and then copy the selected rows to sheet2 as values. I was able to create code to copy one row at a time, but would like all rows to copy at once vs. loop and I want the copied rows to be pasted into sheet2 as values.  Can you please help me with this? Here's the code I am using.</p>
  
  <pre><code>Option Explicit
  Sub Archive()
      Dim wc As Worksheet, wa As Worksheet
      Set wc = Sheets("sheet1")
      Set wa = Sheets("sheet2")
      Dim lr As Long
      lr = wc.Range("A" &amp; Rows.Count).End(xlDown).Row
      Dim i As Long
  Application.ScreenUpdating = False
      For i = lr To 3 Step -1 'sheets all have headers that are 2 rows
          If wc.Range("AB" &amp; i) = "No" Then
          wa.Range("A3").EntireRow.Insert
          wc.Range("A" &amp; i &amp; ":AG" &amp; i).Cut wa.Range("A3")
          wc.Range("A" &amp; i).EntireRow.Delete
          End If
      Next i
  Application.CutCopyMode = False
  Application.ScreenUpdating = True
  MsgBox "Archive Completed"
  End Sub
  </code></pre>