将多个过滤条件粘贴到一个工作表中，而不粘贴原始数据

Question

我在复制标准范围上的过滤数据时遇到了问题，然后将其粘贴到P&L Regulated Entities表中，而不需要将新过滤的数据粘贴到先前过滤的数据上。我如何调整下面的代码，以确保首先粘贴1226上过滤的数据，然后将1843上过滤的数据粘贴在1226的数据下面?

Sheets("P&L").Select
Range("A1").AutoFilter Field:=1, Criteria1:= _
    "*1226*"
ActiveSheet.AutoFilter.Range.Copy
Sheets("P&L Regulated Entities").Select
Range("A1").Select
Selection.PasteSpecial _
Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False,Transpose:=False

Sheets("P&L").Select
Range("A1").AutoFilter Field:=1, Criteria1:= _
    "*1843*"
ActiveSheet.AutoFilter.Range.Copy
Sheets("P&L Regulated Entities").Select
ActiveCell.Offset(1).Select
Range("A1").Select
Selection.PasteSpecial _
Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Sheets("P&L").Select
Range("A1").AutoFilter Field:=1, Criteria1:= _
    "*865*"
ActiveSheet.AutoFilter.Range.Copy
Sheets("P&L Regulated Entities").Select
ActiveCell.Offset(1).Select
Range("A1").Select
Selection.PasteSpecial _
Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Sheets("P&L").Select
Range("A1").AutoFilter Field:=1, Criteria1:= _
    "*1223*"
ActiveSheet.AutoFilter.Range.Copy
Sheets("P&L Regulated Entities").Select
ActiveCell.Offset(1).Select
Range("A1").Select
Selection.PasteSpecial _
Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Answer 1

我会用这个。With...End With结构允许您删除.Select语句。像.Range("A" & .Rows.Count).End(xlUp).Offset(1,0)这样的行将把复制的数据放在受管制实体表的第一个空行中。

With Sheets("P&L")
   .Range("A1").AutoFilter Field:=1, Criteria1:= _
    "*1226*"
   .AutoFilter.Range.Copy
End With
With Sheets("P&L Regulated Entities")
    .Range("A1").PasteSpecial _
Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False,Transpose:=False
End With

With Sheets("P&L")
   .Range("A1").AutoFilter Field:=1, Criteria1:= _
    "*1843*"
   .AutoFilter.Range.Copy
End With
With Sheets("P&L Regulated Entities")
   .Range("A" & .Rows.Count).End(xlUp).Offset(1,0).PasteSpecial _
Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End With

With Sheets("P&L")
   .Range("A1").AutoFilter Field:=1, Criteria1:= _
    "*865*"
   .AutoFilter.Range.Copy
End With
With Sheets("P&L Regulated Entities")
   .Range("A" & .Rows.Count).End(xlUp).Offset(1,0).PasteSpecial _
Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End With

With Sheets("P&L")
   .Range("A1").AutoFilter Field:=1, Criteria1:= _
    "*1223*"
   .AutoFilter.Range.Copy
End With
With Sheets("P&L Regulated Entities")
   .Range("A" & .Rows.Count).End(xlUp).Offset(1,0).PasteSpecial _
Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End With