Sub Copy_rows_if()
Dim currentRow As Integer
Dim sourceCol As Integer
Dim LastRow As Integer
Dim currentRowValue
Dim sourcews As String

sourcews = ActiveSheet.Name 'базовый лист
sourceCol = 2   'колонка B ключевая
RowCount = Cells(Rows.Count, sourceCol).End(xlUp).Row

For currentRow = 1 To RowCount  'для всех строк базового листа
    currentRowValue = Cells(currentRow, sourceCol).Value
    If Not (IsEmpty(currentRowValue) Or currentRowValue = "") Then
          Rows(currentRow).Copy
          Worksheets("Финиш").Select
          LastRow = Cells(Rows.Count, sourceCol).End(xlUp).Row
          Range(Cells(LastRow + 1, 1), Cells(LastRow + 1, 1)).PasteSpecial Paste:=xlPasteValues
          Worksheets(sourcews).Activate
    End If
Next
End Sub

Sub copy_to_archive()

Dim currentRow As Integer
Dim sourceCol As Integer
Dim LastRow As Integer
Dim currentRowValue
Dim sourcews As String
Dim Rowsnum As Integer

sourcews = ActiveSheet.Name 'базовый лист
sourceCol = 5   'Ключевая E колонка
Set myTable = Worksheets(sourcews).Range("A1").CurrentRegion
Rowsnum = myTable.Rows.Count

For currentRow = 5 To Rowsnum  'проверяем есть ли пустые в 5-ой колонке
    currentRowValue = Cells(currentRow, sourceCol).Value
    If (IsEmpty(currentRowValue) Or currentRowValue = "") Then
    MsgBox ("Внимание! Есть пустые ячейки.")
    Exit Sub
    End If
Next

For currentRow = 5 To Rowsnum  'Копируем
    Rows(currentRow).Copy
    Worksheets("Архив").Select
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    With Range(Cells(LastRow + 1, 1), Cells(LastRow + 1, 1))
    .PasteSpecial Paste:=xlPasteValues
    .PasteSpecial Paste:=xlPasteFormats
    End With
    Worksheets(sourcews).Activate
Next
End Sub

with Worksheets("Архив")
 LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
 myTable.offset(5).resize(myTable.Rows.Count-5).copy .Cells(LastRow + 1, 1)
end with

 LastRow = Worksheets("Архив").Cells(Worksheets("Архив").Rows.Count, 1).End(xlUp).Row
 myTable.offset(5).resize(myTable.Rows.Count-5).copy Worksheets("Архив").Cells(LastRow + 1, 1)