Exporting rows to another workbook by matching criteria

[dionysus_83]

I have following code for exporting data to another workbook:

Public Sub EXPORTPLAN()

Dim Ans As Variant
Ans = MsgBox("Are you sure?", vbYesNo)
    Select Case Ans
    Case vbYes
    


Dim LastRow As Integer
Dim wSOURCE As Worksheet
Dim NextRow As Integer




Set wSOURCE = Sheets("ArchivePlan")


Workbooks.Open Filename:="C:\Users\Shiky\Desktop\Consumption 2016..xlsm"
Application.ScreenUpdating = False
    wSOURCE.Activate
    Cells.Select
    Selection.EntireColumn.Hidden = False
    Selection.EntireRow.Hidden = False
    Cells.AutoFilter
    
    wSOURCE.Activate
    wSOURCE.Range("W1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$w$100000").AutoFilter Field:=23, Criteria1:="NO"
    
    LastRow = wSOURCE.UsedRange.Rows.Count
    Range(Cells(2, 1), Cells(LastRow, 22)).Select
    Selection.Copy
    Workbooks("Consumption 2016..xlsm").Activate
    NextRow = Worksheets("Cutter").Cells(Worksheets("Cutter").Rows.Count, "A").End(xlUp).Row + 1
    Sheets("Cutter").Range("A" & NextRow).PasteSpecial xlPasteValues
    Workbooks("Cutter 2016.xlsm").Activate
      
    Worksheets("ControlPanel").Range("T1").Copy
    wSOURCE.Range(Cells(2, 23), Cells(LastRow - 2, 23)).PasteSpecial xlPasteValues
    
    
    wSOURCE.Range("P:W").EntireColumn.Hidden = True
    Cells.AutoFilter
    
    MsgBox ("Export completed!")
    
Application.ScreenUpdating = True
    
    
    Case vbNo
    GoTo Quit:
    End Select


Quit:
    
    
End Sub

It should filter data in one workbook that matches criteria NO in column W (or 23 column) and copy those rows to first blank row in another workbook. Afterwards it should mark all those transfered rows from first workbook wih YES instead of now in 23 column!

More or less this does what I need except:

1. If there is no rows matching criteria NO it copies all rows from first workbook
2. When it finish, it marks all cells in 23 column with YES not only transfered ones!

I tried with different methods of selecting those rows and marking them with YES (I cant do just substitute as sometime there will be something else than NO or YES!).

 

[dionysus_83]

History continues?