根据标签网格上的搜索将行复制到另一个工作表 [英] Copying rows to another worksheet based on a search on a grid of tags

问题描述

我有一个Excel问题，希望有人可以帮助我。

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

我有一个表格，其间列有K& Q是一些标签。我想做的是有一个函数或一个宏，或者允许我查看所有这些标签，并将包含特定单词的任何行复制到另一个工作表。

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.

例如

       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

列A到H中还有其他信息，我也需要复制，但不想包含在搜索中。所以在这种情况下，我想能够搜索标签资金，因此复制第1，2，5&

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.

这是可能吗？

推荐答案

这是代码。我从这个论坛（我基于我的代码关闭）给予信用：
http://en.kioskea.net/forum/affich-242360-copy-row-if-a-range-of-column-matches- a-value

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

更新：
代码改写为更有效，有一些梦幻般的点， -FrançoisCorbett 实现（谢谢！）。我还在最后添加了一个消息框，报告了多少行被复制。

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

我自定义代码来做你需要做的事情。运行宏时会发生什么（请确保不在表格2中）是否出现一个框。输入要过滤的单词（在您的情况下为资金），并且将通过K：Q查看包含它的单元格。

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.

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!

这篇关于根据标签网格上的搜索将行复制到另一个工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！