本文介绍了VBA从AutoFilter复制并粘贴到另一张Sheet中,输出一行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一个 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中,输出一行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

06-25 06:02