VBA Excel 过滤数据并复制到另一个工作表 - 新手警报

VBA Excel filter data and copy to another worksheet - Newbie alert

提问by Jason Pye

Hi I am a complete newbie when it comes to VBA Excel.

嗨，当谈到 VBA Excel 时，我是一个完整的新手。

Im trying to filter a variable sized spreadsheet full of data looking for a set word in one column. Once found I want to copy the complete row to another worksheet.

我试图过滤一个充满数据的可变大小的电子表格，以在一列中寻找一个固定的词。一旦找到，我想将整行复制到另一个工作表。

As they maybe more than one row with this word in it I don't want to write over the previous copied row.

因为他们可能不止一行包含这个词，所以我不想覆盖上一个复制的行。

this is what ive created to find the word, but how do I copy the row to another worksheet?

这是我为查找单词而创建的内容，但是如何将该行复制到另一个工作表？

Sheets("Sheet1").Select                                 'Select datasheet
Range("A1").Select                                      'Set cell position to start search from

Do Until Selection.Offset(0, 4).Value = ""              'word to be searched is 4 cell in
                                                        'do what is required
    If Selection.Offset(0, 4).Value = "UKS" Then
        MsgBox "Found"                                  'Found it!
        'not sure how to copy row to another worksheet
    End If

'finish move on to next one in list
         Selection.Offset(1, 0).Select                  'move down 1 row
    Loop

    Range("A1").Select ' reset cell position

Any help would be appreciated, please could you explain how it works as well as I like to understand rather than just copying.

任何帮助将不胜感激，请您解释它是如何工作的以及我喜欢理解的，而不仅仅是复制。

Jason

杰森

回答by George

Have a look down-below, perhaps it gives you clue. Nevertheless, I will try to comment the details to get you acquainted closely with the matter.

看看下面，也许它会给你一些线索。不过，我会尽量评论细节，让你更熟悉这件事。

My example is a bit more complicated because it applies filtering on two columns at once, but it is good for you understand it as complicated as it is because you can further apply it.

我的例子有点复杂，因为它一次对两列应用过滤，但你最好理解它的复杂程度，因为你可以进一步应用它。

 With Sheets("Source")
            .AutoFilterMode = False
        With .Range("$A" & ":" & "$C$" & 300)
             .AutoFilter Field:=1, Criteria1:=Array("April", "August", "Dezember", "Februar", "Januar", "Juli", "Juni", "Mai", "M?rz", "November", "Oktober", "September"), Operator:=xlFilterValues
             .AutoFilter Field:=2, Criteria1:="<>"
             ActiveSheet.AutoFilter.Range.Copy
             Sheets("Chart").Select
             Range("A7").Select
            Sheets("Chart").Paste
         End With
        End With

So, what is this about:

那么，这是关于什么的：

logic of the code is

代码的逻辑是

• filter on two columns columns 1 by the name of the Months (i.e. German language), column 2 filter the elements by eliminating the cells which are blanks/empty

• 按月份名称（即德语）过滤两列第 1 列，第 2 列通过消除空白/空单元格来过滤元素

With Sheets("Source") .AutoFilterMode = False With .Range("$A$21" & ":" & "$C$" & 300) .AutoFilter Field:=1, Criteria1:=Array("April", "August", "Dezember", "Februar", "Januar", "Juli", "Juni", "Mai", "M?rz", "November", "Oktober", "September"), Operator:=xlFilterValues

With Sheets("Source") .AutoFilterMode = False With .Range("$A$21" & ":" & "$C$" & 300) .AutoFilter Field:=1, Criteria1:=Array("April", " August”、“Dezember”、“Februar”、“Januar”、“Juli”、“Juni”、“Mai”、“M?rz”、“November”、“Oktober”、“September”），运算符：=xlFilterValues

• name of the Worksheet that contains the Table to apply the filtering upon is "Source". leave .Autofiltering to false

  .AutoFilterMode = False

• the filtering values are set as follows: column 1

• 包含要应用过滤的表的工作表的名称是“源”。将 .Autofiltering 保留为 false

  .AutoFilterMode = False

• 过滤值设置如下：第 1 列

.AutoFilter Field:=1, Criteria1:=Array("April", "August", "Dezember", "Februar", "Januar", "Juli", "Juni", "Mai", "M?rz", "November", "Oktober", "September"), Operator:=xlFilterValues

.AutoFilter Field:=1, Criteria1:=Array("April", "August", "Dezember", "Februar", "Januar", "Juli", "Juni", "Mai", "M?rz", "November", "Oktober", "September"), 运算符:=xlFilterValues

column 2

第 2 栏

.AutoFilter Field:=2, Criteria1:="<>"

.AutoFilter Field:=2, Criteria1:="<>"

• last, but not least (very important) is the Range ( the cells of the tables to which the filtering is applied)

• 最后但并非最不重要的（非常重要）是范围（应用过滤的表格的单元格）

With .Range("$A$21" & ":" & "$C$" & 300)

随着 .Range("$A$21" & ":" & "$C$" & 300)

in this case my example applies the filter to a range of cells( as a matter a fact a table) that starts with A21 and ends up at C300.

在这种情况下，我的示例将过滤器应用于以 A21 开头并以 C300 结尾的一系列单元格（实际上是一个表格）。

why A21?Because that's where my data gets copied. It starts from A21 always.

为什么是A21？因为那是我的数据被复制的地方。它总是从 A21 开始。

why C300?Because the maximum number of rows will never exceed (300-21)=279 rows my model of data is not exceeding 279 of unfiltered rows at any time, you can put a greater figure as per your assumption. If there are more rows, nevermind because I filtered them by eliminating the blanks , see above.

为什么是C300？因为最大行数永远不会超过 (300-21)=279 行，我的数据模型在任何时候都不会超过 279 行未过滤的行，您可以根据您的假设输入更大的数字。如果有更多行，没关系，因为我通过消除空白来过滤它们，见上文。

By the way, the "kosher" version is to count the number of rows via VBA and use this when defining your range.

顺便说一下，“kosher”版本是通过 VBA 计算行数，并在定义范围时使用它。

You can simply use a very large number that covers the possible number of rows in your table.

您可以简单地使用一个非常大的数字来覆盖表中可能的行数。

Counting the number of rows this might be a bit complicated for you at the 1st glance, but shall pay its pennies in the end.

计算行数乍一看对您来说可能有点复杂，但最终会付出代价。

supposedly you want to count the number of rows (variable) in Column B FinalRowChartSheet = Range("B7").End(xlDown).Row

假设您想计算 B 列 FinalRowChartSheet = Range("B7").End(xlDown).Row 中的行数（变量）

Wish you the best. I hope I helped you.

祝你好运。我希望我帮助了你。

Do not forget to rate my answer if you find it useful.Thank you.

如果您觉得有用，请不要忘记评价我的答案。谢谢。

回答by Davesexcel

Here is a loop code example and a filter code example.

这是一个循环代码示例和一个过滤器代码示例。

Sub loopMe()

    Dim sh As Worksheet, ws As Worksheet
    Dim LstR As Long, rng As Range, c As Range

    Set sh = Sheets("Sheet1")    'set the sheet to loop
    Set ws = Sheets("Sheet2")    'set the sheet to paste
    With sh    'do something with the sheet
        LstR = .Cells(.Rows.Count, "D").End(xlUp).Row    'find last row
        Set rng = .Range("D2:D" & LstR)    'set range to loop
    End With

    'start the loop
    For Each c In rng.Cells
        If c = "UKS" Then
            c.EntireRow.Copy ws.Cells(ws.Rows.Count, "A").End(xlUp).Offset(1)    'copy row to first empty row in sheet2
        End If
    Next c

End Sub

Sub FilterMe()
    Dim sh As Worksheet, ws As Worksheet
    Dim LstR As Long, rng As Range

    Set sh = Sheets("Sheet1")    'set the sheet to filter
    Set ws = Sheets("Sheet2")    'set the sheet to paste
    Application.ScreenUpdating = False
    With sh    'do something with the sheet
        LstR = .Cells(.Rows.Count, "D").End(xlUp).Row    'find last row
        .Columns("D:D").AutoFilter Field:=1, Criteria1:="UKS"
        Set rng = .Range("A2:Z" & LstR).SpecialCells(xlCellTypeVisible)    'Replace Z with correct last column
        rng.Copy ws.Cells(ws.Rows.Count, "A").End(xlUp).Offset(1)
        .AutoFilterMode = False
    End With

End Sub