本文介绍了VBA从AutoFilter复制并粘贴到另一张Sheet中,输出一行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个 AutoFilter
,一旦应用它总是输出一个行
。我想复制
这一个行
和粘贴
-
xlCellTypeAllValidation
但会引发错误
-
xlCellTypeSameValidation
有许多验证标准aAutoFilter
-
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 anerror
xlCellTypeSameValidation
there are many validation criteria anAutoFilter
xlCellTypeLastCell
but it gives the location of the lastcell
in the filteredrow
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中,输出一行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!