I have tried to write code to copy data from sheet "Imported data" to sheet "Data to upload", but is not extracting the correct data
I have data from row 6 onwards in Col A:R
If Col A contains NV and Col B contains RE or Col A contains DV and M1 then I wants the data from A5 to the last row in Col R containing this criteria to be copied to sheet "Data to be uploaded"
See sample data. I have highlighted the critertia in yellow to be copied
It would be appreciated if someone could assist me
I have data from row 6 onwards in Col A:R
If Col A contains NV and Col B contains RE or Col A contains DV and M1 then I wants the data from A5 to the last row in Col R containing this criteria to be copied to sheet "Data to be uploaded"
See sample data. I have highlighted the critertia in yellow to be copied
Excel 2012 | ||||
---|---|---|---|---|
A | B | |||
5 | BT | Plan | ||
6 | NV | RE | ||
7 | NV | AA | ||
8 | DV | M1 | ||
9 | DV | M1 | ||
10 | DV | M1 | ||
11 | NV | B6 | ||
12 | DV | M1 | ||
13 | DV | M1 | ||
14 | NV | RE | ||
15 | DV | M1 | ||
16 | DV | M1 | ||
17 | ||||
Inported Data |
Code:
Sub Extract_Data()
Sheets("Imported Data").Select
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
With ActiveSheet.Range("A6:R" & Lastrow)
.AutoFilter Field:=1, Criteria1:=Array("DV", "NV"), Operator:=xlFilterValues
.AutoFilter Field:=2, Criteria1:=Array("M1", "RE"), Operator:=xlFilterValues
.SpecialCells(xlCellTypeVisible).Copy Worksheets("Data to be uploaded").Range("A2")
.AutoFilter
End With
End Sub
It would be appreciated if someone could assist me