VBA to filter and extract data

[tarunrag98]

Hi guys,

I am relatively new to Excel so please excuse any errors or stupidity on my part.

I have a dataset from which I need to filter out values. The data range is from Column A to Column AA. In columns V to AA I have some vlookups that are returning the value of #N/A. I need the VBA to work like:

1. In column V whichever cell has #N/A, filter that entire row from A to AA, copy and paste into another worksheet/workbook
2. This is to be applied to all columns from V to AA that have the vlookup. It does not matter if the same row is filtered out multiple times. I have attached a screenshot for reference.

For example, I need to check each cell in the column V. Suppose in cell V4 there is #N/A, then I want to copy the entire row 4 from A to AA and paste (without formulas) it in another workbook and delete the entire row from the original workbook. This is to be done for columns W, X, Y, Z and AA. It does not matter if the same row is copy pasted multiple times due #N/A in all the columns from V to AA. For example as per this procedure, row 2 has to be copy-pasted 4 times since the cells V2, W2, X2 and Z2 all have #N/A.
I have written some code for this that I have pasted below but it keeps giving me the error mentioned below and highlights the line that I have highlighted in yellow. The expectation is to create a new file called Mantis Final on the desktop and paste all the values in that.
Additionally, if I could get any advice on how to copy paste the filtered data in one sheet that would be great. I have used different sheets since I do not know how to paste new data after where the old data ends.

Private Sub CommandButton5_Click()


Workbooks.Add
ActiveWorkbook.SaveAs "C:\Users\Raghuram Tarun\Desktop\Mantis Final.xlsx"
Sheets.Add

ActiveSheet.Name = "Sheet2"
ActiveSheet.Name = "Sheet3"
ActiveSheet.Name = "Sheet4"
ActiveSheet.Name = "Sheet5"
ActiveSheet.Name = "Sheet6"



ThisWorkbook.Worksheets("MaterialSalesOrders").Range("A:AA").AutoFilter Field:=22, Criteria1:="#N/A"
Workbooks("Mantis WMS Template - Test").Worksheets("MaterialSalesOrders").Range("A:AA").Copy
Workbooks("Mantis Final.xlsx").Worksheets("Sheet1").Range("A:AA").PasteSpecial Paste:=xlPasteValues

ThisWorkbook.Worksheets("MaterialSalesOrders").Range("A:AA").AutoFilter Field:=23, Criteria1:="#N/A"
Workbooks("Mantis WMS Template - Test 2").Worksheets("MaterialSalesOrders").Range("A:AA").Copy
Workbooks("Mantis Final.xlsx").Worksheets("Sheet2").Range("A:AA").PasteSpecial Paste:=xlPasteValues

ThisWorkbook.Worksheets("MaterialSalesOrders").Range("A:AA").AutoFilter Field:=24, Criteria1:="#N/A"
Workbooks("Mantis WMS Template - Test 2").Worksheets("MaterialSalesOrders").Range("A:AA").Copy
Workbooks("Mantis Final.xlsx").Worksheets("Sheet3").Range("A:AA").PasteSpecial Paste:=xlPasteValues

ThisWorkbook.Worksheets("MaterialSalesOrders").Range("A:AA").AutoFilter Field:=25, Criteria1:="#N/A"
Workbooks("Mantis WMS Template - Test 2").Worksheets("MaterialSalesOrders").Range("A:AA").Copy
Workbooks("Mantis Final.xlsx").Worksheets("Sheet4").Range("A:AA").PasteSpecial Paste:=xlPasteValues

ThisWorkbook.Worksheets("MaterialSalesOrders").Range("A:AA").AutoFilter Field:=26, Criteria1:="#N/A"
Workbooks("Mantis WMS Template - Test 2").Worksheets("MaterialSalesOrders").Range("A:AA").Copy
Workbooks("Mantis Final.xlsx").Worksheets("Sheet5").Range("A:AA").PasteSpecial Paste:=xlPasteValues

ThisWorkbook.Worksheets("MaterialSalesOrders").Range("A:AA").AutoFilter Field:=27, Criteria1:="#N/A"
Workbooks("Mantis WMS Template - Test 2").Worksheets("MaterialSalesOrders").Range("A:AA").Copy
Workbooks("Mantis Final.xlsx").Worksheets("Sheet6").Range("A:AA").PasteSpecial Paste:=xlPasteValues

End Sub

If copying and pasting is not possible, I would atleast like the filtered data to appear in the original worksheet itself and I will copy paste manually.




Thank you