VBA 自动筛选 然后转移数据 删掉不要的 解除筛选 请高手指点

Question

Hi 请高手兄弟帮忙
我需要把sheets3的数据筛选，然后把筛选出来的结果转移到sheets2的usedrange的最后一行，然后把筛选的结果删除掉，最后解掉自动筛选。我的code如下，但结果总是达不到预期，请高手兄弟帮忙：

With ThisWorkbook.Worksheets(3)
.Range("A1", "BE" & j).AutoFilter Field:=4, Criteria1:=Array("TIANYAL", "LIDAN01", "GUOFEI", "SHENYAN", "LUOLOL", "ONGNAL01", _
"LIBAR", "WANGLIN01", "YANGQIA"), Operator:=xlValues

j = ActiveWorkbook.Sheets(3).Range("D1048576").End(xlUp).Row
.Range("A2", "BE" & j).Copy
End With
ThisWorkbook.Worksheets(2).Activate
j = ActiveWorkbook.Sheets(2).Range("D1048576").End(xlUp).Row
ThisWorkbook.Worksheets(2).Range("A" & j + 1).Select
ThisWorkbook.Worksheets(2).Paste

'delete residal data and remover filter

ThisWorkbook.Worksheets(3).Activate
j = ActiveWorkbook.Sheets(3).Range("D1048576").End(xlUp).Row
ThisWorkbook.Worksheets(3).Range("A2:A" & j).SpecialCells(xlCellTypeVisible).EntireRow.Delete
ThisWorkbook.ActiveSheet.AutoFilterMode = False

奉上30大洋，笑纳 笑纳

Answer 1

建议你使用循环，由SHEET3的最后一条记录向上循环找符合要求的记录，符合则复制到SHEET2，并删除，这样就不比写筛选和取消筛选的代码了。

With ThisWorkbook.Worksheets(3)
j = ActiveWorkbook.Sheets(3).Range("D1048576").End(xlUp).Row

Do
If .Cells(j, 4) = "TIANYAL" Or .Cells(j, 4) = "LIDAN01" Or .Cells(j, 4) = "GUOFEI" Or .Cells(j, 4) = "SHENYAN" Or .Cells(j, 4) = "LUOLOL" Or .Cells(j, 4) = "ONGNAL01" _
Or .Cells(j, 4) = "LIBAR" Or .Cells(j, 4) = "WANGLIN01" Or .Cells(j, 4) = "YANGQIA" Then

.Range("A2", "BE" & j).Copy

ThisWorkbook.Worksheets(2).Activate
ji = ActiveWorkbook.Sheets(2).Range("D1048576").End(xlUp).Row
ThisWorkbook.Worksheets(2).Range("A" & ji + 1).Select
ThisWorkbook.Worksheets(2).Paste
.Row(j).Delete
End If
j = j - 1
Loop Until j = 1

End With