vba 根据对标签网格的搜索将行复制到另一个工作表

Copying rows to another worksheet based on a search on a grid of tags

提问by Adam Levy

I am having a problem with Excel that I was hoping someone could help me with.

我在使用 Excel 时遇到了问题，希望有人可以帮助我。

I have a table where between columns K & Q are a number of tags. What I would like to do is have a function or a macro or something that will allow me to look within all these tags and copy any rows that contain a specific word to another worksheet.

我有一个表格，其中 K 和 Q 列之间有许多标签。我想要做的是有一个函数或宏或其他东西，可以让我查看所有这些标签并将包含特定单词的任何行复制到另一个工作表。

e.g.

例如

       I           J        K          L         M           N              O     etc. 
1      blah        blah     funding    blah      blah        blah           blah
2      funding     blah     blah       blah      blah        blah           blah
3      blah        blah     blah       blah      blah        blah           blah
4      blah        blah     blah       blah      blah        blah           blah
5      blah        blah     blah       blah      blah        funding        blah
6      blah        blah     funding    blah      blah        blah           blah

There is other information in columns A to H that I will also need to copy across, but do not want to include in the search. So in this scenario, I would like to be able to search for the tag 'funding' and therefore copy rows 1, 2, 5 & 6 to a different worksheet.

A 到 H 列中还有其他信息，我也需要复制这些信息，但不想包含在搜索中。所以在这种情况下，我希望能够搜索标签“资金”，因此将第 1、2、5 和 6 行复制到不同的工作表。

Is this possible?

这可能吗？

回答by aevanko

Here is the code. I give credit to tompols from this forum (I based my code off this): http://en.kioskea.net/forum/affich-242360-copy-row-if-a-range-of-column-matches-a-value

这是代码。我感谢这个论坛的 tompols（我的代码基于此）：http://en.kioskea.net/forum/affich-242360-copy-row-if-a-range-of-column-matches-a -价值

UPDATE: Code rewritten to be more effecient with some fantastic points from Jean-Fran?ois Corbettimplemented (thanks!). I also added a message box at the end that reports how many rows were copied over.

更新：代码重写以提高效率，并实现了Jean-Fran?ois Corbett 的一些精彩观点（谢谢！）。我还在最后添加了一个消息框，报告复制了多少行。

I customized the code to do what you needed it to do. What happens when you run the macro (make sure you aren't on sheet 2) is that a box appears. Enter the word you want to filter by (in your case funding), and it will look through K:Q for cells that contain it. It will copy the entire column when it finds a match into sheet 2.

我定制了代码来做你需要它做的事情。当您运行宏时（确保您不在工作表 2 上）会出现一个框。输入您想要过滤的词（在您的资金情况下），它将通过 K:Q 查找包含它的单元格。当它找到匹配项时，它将整个列复制到工作表 2 中。

Sub customcopy()

Application.ScreenUpdating = False
Dim lastLine As Long
Dim findWhat As String
Dim toCopy As Boolean
Dim cell As Range
Dim i As Long
Dim j As Long

findWhat = CStr(InputBox("Enter the word to search for"))
lastLine = ActiveSheet.UsedRange.Rows.Count

j = 1
For i = 1 To lastLine
    For Each cell In Range("K1:Q1").Offset(i - 1, 0)
        If InStr(cell.Text, findWhat) <> 0 Then
            toCopy = True
        End If
    Next
    If toCopy = True Then
        Rows(i).Copy Destination:=Sheets(2).Rows(j)
        j = j + 1
    End If
    toCopy = False
Next

i = MsgBox(((j - 1) & " row(s) were copied!"), vbOKOnly, "Result")

Application.ScreenUpdating = True
End Sub

Accepting answers (I noticed you are new here): If this works for you, please click the arrow that appear on the upper left to accept this answer. Thanks!

接受答案（我注意到你是新来的）：如果这对你有用，请点击出现在左上角的箭头接受这个答案。谢谢！

回答by JMax

you can try recording a macrowith the following steps:

您可以尝试通过以下步骤录制宏：

1. select the columns where you want to search (Kand Qif i understood well)
2. perform a search with a sample tag
3. copy the row you found
4. paste it to the other Sheet

1. 选择您要搜索的列（K以及Q如果我深知）
2. 使用示例标签执行搜索
3. 复制您找到的行
4. 将其粘贴到另一个工作表

you will then have a first sample of code to start with.

然后，您将获得第一个代码示例。

see herefor some tips on how to clean up the code

有关如何清理代码的一些提示，请参见此处