VBA从AutoFilter复制并粘贴到另一张Sheet中，输出一行 [英] VBA Copy and Paste in another Sheet from AutoFilter outputting one row

问题描述

我有一个 AutoFilter ，一旦应用它总是输出一个行 。我想复制这一个行和粘贴

• xlCellTypeAllValidation 但会引发错误


• xlCellTypeSameValidation 有许多验证标准a AutoFilter


• xlCellTypeLastCell 但它给出了过滤的行中最后的单元格的位置 / li>
  

我如何做到这一点？

code>代码：

  With ThisWorkbook.Sheets（k）.Range ：AZ1）
 .Value = .Value 
 .AutoFilter字段：= 1，Criteria1：= Rev_1 
 .AutoFilter字段：= 11，Criteria1：= Beginnings（k）
 .AutoFilter字段：= 12，Criteria1：= End_Instnts（k）
 
对于zz = 13到last_Field 
 .AutoFilter字段：= zz，Criteria1：=
 Next zz 
 .SpecialCells（xlCellTypeLastCell）.Select 
 .Range.Select 
 
 ThisWorkbook.Sheets（k）.AutoFilterMode = False 
 
 
 End有了
  

解决方案

我建议测试，复制之前的标准 - 例如：

  With ThisWorkbook.Sheets（k）.Range（A1）。CurrentRegion。调整大小（，52）
 .Value = .Value 
 .AutoFilter字段：= 1，Criteria1：= Rev_1 
 .AutoFilter字段：= 11，Criteria1：= Beginnings（k）
 .AutoFilter字段：= 12，Criteria1：= End_Instnts（k）
 
对于zz = 13到last_Field 
 .AutoFilter字段：= zz，Criteria1：=
 zz 
'确保有匹配过滤器的结果
如果.Columns（1）.SpecialCells（xlCellTypeVisible）.Count> 1 Then 
'offset and resize to avoid headers然后复制
 .Resize（.Rows.Count  -  1）.Offset（1）.SpecialCells（xlCellTypeVisible）.Copy Destination：= Sheets（other sheet ）.Range（A1）
结束如果
 
 ThisWorkbook.Sheets（k）.AutoFilterMode = False 
 
结束于
  

I have an AutoFilter that once it is applied it always outputs one row. I want to copy this one row and paste it on another Sheet.

I have considered:

• xlCellTypeAllValidation but it throws out an error
• xlCellTypeSameValidation there are many validation criteria an AutoFilter
• xlCellTypeLastCell but it gives the location of the last cell in the filtered row

How can i do this?

Here is an excerpt from my code:

With ThisWorkbook.Sheets(k).Range("A1:AZ1")
        .Value = .Value
        .AutoFilter field:=1, Criteria1:=Rev_1
        .AutoFilter field:=11, Criteria1:=Beginnings(k)
        .AutoFilter field:=12, Criteria1:=End_Instnts(k)

        For zz = 13 To last_Field
            .AutoFilter field:=zz, Criteria1:=""
        Next zz
        .SpecialCells(xlCellTypeLastCell).Select
        .Range.Select

     ThisWorkbook.Sheets(k).AutoFilterMode = False


End With

解决方案

I'd recommend testing to ensure something actually matched the criteria before you copy - something like:

With ThisWorkbook.Sheets(k).Range("A1").CurrentRegion.Resize(, 52)
    .Value = .Value
    .AutoFilter field:=1, Criteria1:=Rev_1
    .AutoFilter field:=11, Criteria1:=Beginnings(k)
    .AutoFilter field:=12, Criteria1:=End_Instnts(k)

    For zz = 13 To last_Field
        .AutoFilter field:=zz, Criteria1:=""
    Next zz
    ' make sure there are results matching filter
    If .Columns(1).SpecialCells(xlCellTypeVisible).Count > 1 Then
        ' offset and resize to avoid headers then copy
        .Resize(.Rows.Count - 1).Offset(1).SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets("other sheet").Range("A1")
    End If

    ThisWorkbook.Sheets(k).AutoFilterMode = False

End With

这篇关于VBA从AutoFilter复制并粘贴到另一张Sheet中，输出一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！