在Excel中使用VBA设置过滤器 [英] Set filter in Excel with VBA

问题描述

下面的宏将允许我在表1中的标题中找到一个名称，并将整个列复制到表2.现在我想继续执行代码，但是我遇到一个问题，我将尝试解释一下。 p>

  Sub CopyColumnByTitle（）
'在行1中查找名称
带表格（1）.Rows 1）
设置t = .Find（Name，lookat：= xlpart）
'如果找到，将列复制到Sheet 2，列A 
'如果没有找到， 
如果不是没有，那么
列（t.Column）.EntireColumn.Copy _ 
目标：=表（2）.Range（A1）
否则：MsgBox 未找到标题
如果
结束
结束Sub 
  

所有数据粘贴在表2中如下....

  Sheet 2 
名称年龄地址出生日期
 John 25 US 1-Sep-11 
 Hary 26 US 1-Sep-11 
 John 27 UK 1-Sep-11 
 Hary 28 US 2-Sep- 11 
 King 29 UK 3-Sep-11 
彼得30 US 3-Sep-11 
  

我需要设置如下所示的过滤器，并将过滤的数据复制到表格3如上代码所示：

1. 我需要在表2中设置过滤条件，这有助于我看到名称 s等于John或Hary，并将整个数据复制并粘贴到表3中。


2. 我需要设置另一个过滤器，其中姓名等于约翰和出生日期等于1-Sep-11（注意日期应该始终是昨天）。复制并将整个
   数据粘贴到表4中。


3. 第三次，我需要设置一个过滤器，其中 Name 等于King，并将整个数据复制并转贴到表5中。

---

感谢John的回覆，您给出的回复是有效的，但由于紧急需求，我已经设计了我的代码。

我需要一点帮助在同一个我正在粘贴一部分代码，因为它不可能粘贴整个代码。

该代码允许我将数据从一个工作簿复制到另一个工作簿，但是在复制数据时我需要复制整个列，因为它有一些空白单元格。所以如果我不使用 .EntireColumn ，宏不会在空白单元格之后复制单元格。此外，在将数据粘贴到其他工作簿中时，我需要粘贴它，而不用标题。

如果您帮助我，我将不胜感激。激活
范围（范围（M2），范围（ N2）。End（xlDown））。EntireColumn.Select
Application.CutCopyMode = False
Selection.Copy
Windows（formula.xls）激活
范围（I2），Range（J2）。End（xlDown））。EntireColumn.Select
Selection.PasteSpecial粘贴：= xlPasteValuesAndNumberFormats，操作：= _
xlNone，SkipBlanks：= False， ：= False

解决方案

任务1：

  thisworkbook.sheets（2）.activate 
 activesheet.range（A：A）。选择过滤可能性的列这里的名字
 Selection.AutoFilter字段：= 1，Criteria1：== John，运算符：= xlOr，_ 
 Criteria2：== Hary'仅适用于hary或john 
 activate.usedrange.select'现在选择要过滤的工作表复制
 selection.copy 
 ActiveSheet.ShowAllData'现在保留数据，以便您获得原始文件
 thisworkbook.sheets（3）选择您的sheet3并粘贴
 activate.range（A1）。选择
 activesheet.paste 
  

任务2：

  thisworkbook.sheets（2）.activate 
活动表单。范围（A：A）。选择\\'设置您在这里过滤的可能名称的列
 Selection.AutoFilter字段：= 1，Criteria1：=John\\ filters for john 
 Selection.AutoFilter字段：= 2，条件1：=1-sep-2011\\仅适用于约翰行的日期的过滤器
 activate.usedrange.select'现在选择要过滤的表单以复制
 selection.copy 
 ActiveSheet.ShowAllData'现在保留数据，以便您获得原始文件
 thisworkbook.sheets（4）.activate'选择您的sheet3并粘贴
激活。范围（A1）。选择
 activesheet.paste 
  

任务3

  thisworkbook.sheets（2）.activate 
 activesheet.range（A：A）。选择'过滤的列probab le name here 
 Selection.AutoFilter字段：= 1，Criteria1：== King'仅过滤王
 activate.usedrange.select'现在选择要过滤的表复制
 selection.copy 
 ActiveSheet.ShowAllData'现在保留数据，以便您获取原始文件
 thisworkbook.sheets（5）。激活'选择您的sheet3并粘贴
 activate.range（A1 ）.select 
 activesheet.paste 
  

可能会给你一些想法如何做。任何更多的疑问，请随时问我。

谢谢！你可能会去复制目的地：=和更多的方法来做到这一点。实际上我现在要走了，所以我只是给你一个样品来工作。

The below macro will allow me to find a name in the heading in sheet 1 and copy the entire column to sheet 2. Now I want to continue the code, but am facing a problem, which I will try explain.

Sub CopyColumnByTitle()
'Find "Name" in Row 1
  With Sheets(1).Rows(1)
   Set t = .Find("Name", lookat:=xlpart)
'If found, copy the column to Sheet 2, Column A
'If not found, present a message
     If Not t Is Nothing Then
        Columns(t.Column).EntireColumn.Copy _
          Destination:=Sheets(2).Range("A1")
       Else: MsgBox "Title Not Found"
     End If
  End With
End Sub

after all data is pasted in the sheet 2 as below ....

Sheet 2
Name Age Address    Date of Birth
John    25  US  1-Sep-11
Hary    26  US  1-Sep-11
John    27  UK  1-Sep-11
Hary    28  US  2-Sep-11
King    29  UK  3-Sep-11
Peter   30  US  3-Sep-11

I need to set filters as shown below and copy the filtered data to sheet 3 as above code does:

1. I need to set filter criteria on sheet 2 which helps me to see Names that are equal to "John" or "Hary" and copy and paste the entire data into sheet 3.
2. I need to set another filter where Name is equal to "John" and Date of Birth is equal to "1-Sep-11" (note the date should always be yesterday). Copy and paste the entire data into sheet 4.
3. On the third time, I need to set a filter where Name is equal to "King" and copy the and past the entire data into sheet 5.

---

Thanks a lot John for your reply, the reply you gave is effective but I have already designed my code due of the urgent requirements.

I need a slight help in same. I am pasting some part of code as it is not possible to paste the entire code.

The code allows me to copy data from one workbook to another, but while copying data, I need to copy an entire column because there are some blank cells in it. So if I don't use .EntireColumn, the macro does not copy cells after the blank cell. Also now, while pasting the data into the other work book, I need to paste it without the heading.

I would be grateful if you help me out with this.

Windows("macro2.xlsm").Activate
Range(Range("M2"), Range("N2").End(xlDown)).EntireColumn.Select
Application.CutCopyMode = False
Selection.Copy
Windows("formula.xls").Activate
Range(Range("I2"), Range("J2").End(xlDown)).EntireColumn.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

解决方案

Task 1:

 thisworkbook.sheets(2).activate
 activesheet.range("A:A").select 'set column you filter for probable names here
 Selection.AutoFilter Field:=1, Criteria1:="=John", Operator:=xlOr, _
 Criteria2:="=Hary" ' filters  only for hary or john 
 activate.usedrange.select ' now select the filtered sheet to copy  
 selection.copy 
 ActiveSheet.ShowAllData ' now retain back the data so that you get your original file
 thisworkbook.sheets(3).activate  'select your sheet3 and paste it
 activate.range("A1").select
 activesheet.paste

Task 2:

 thisworkbook.sheets(2).activate
 activesheet.range("A:A").select \\'set column you filter for probable names here
 Selection.AutoFilter Field:=1, Criteria1:="John" \\ filters for john
 Selection.AutoFilter Field:=2, Criteria1:="1-sep-2011"  \\ filters for date only for john rows 
 activate.usedrange.select ' now select the filtered sheet to copy  
 selection.copy 
 ActiveSheet.ShowAllData ' now retain back the data so that you get your original file
 thisworkbook.sheets(4).activate  'select your sheet3 and paste it
 activate.range("A1").select
 activesheet.paste

Task 3

 thisworkbook.sheets(2).activate
 activesheet.range("A:A").select 'set column you filter for probable names here
 Selection.AutoFilter Field:=1, Criteria1:="=King" ' filters  only king 
 activate.usedrange.select ' now select the filtered sheet to copy  
 selection.copy 
 ActiveSheet.ShowAllData ' now retain back the data so that you get your original file
 thisworkbook.sheets(5).activate  'select your sheet3 and paste it
 activate.range("A1").select
 activesheet.paste

Probably It might give you some idea how to do it. any more doubt feel free to ask me.

Thanks! You could probably go for copy destination:= and many more way to do it . actually I have to go now so i just gave you a sample piece to work on.

这篇关于在Excel中使用VBA设置过滤器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！