vba 搜索字符串中的文本，将行复制并粘贴到新工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20482207/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Search for text in a string, copy & paste rows to new sheet
提问by BranpanMan
I am new to VBA programming, and I need some help writing a simple macro in Excel 2010.
我是 VBA 编程的新手，我需要一些帮助来在 Excel 2010 中编写一个简单的宏。
I need to search for a text string in Column A (the exact text I'm searching for is not specified) and if that string is found within the cell, cut and paste that cell's entire row into another sheet in the workbook and then delete the empty rows in the original sheet.
我需要寻找在A列（我在寻找的是确切的文本的文本字符串not specified），如果该字符串的单元格，内切发现，该单元的整行粘贴到另一个工作表在工作簿，然后删除空原始工作表中的行。
I searched the forum a bit and found some code examples that almost got me where I wanted to get but not quite.
我在论坛上搜索了一下，发现了一些代码示例，它们几乎让我到达了我想要的地方，但并不完全。
回答by BranpanMan
OK. I used the recorder, and hints that you all left me, and came up with this macro. I'm not using an incrementor or looping it, but rather filtering it and doing it all in one go. This process has worked for me and now everyone in my office... and everyone now thinks I'm really good at VBA... which is NOT the case, but I'm certainly on my way :)
好的。我用录音机，暗示你们都离开了我，想出了这个宏。我没有使用增量器或循环它，而是过滤它并一次性完成所有操作。这个过程对我有用，现在我办公室里的每个人都有效……现在每个人都认为我真的很擅长 VBA……事实并非如此，但我肯定在路上 :)
Thanks everyone, for the help!!!
谢谢大家，帮忙！！！
Sub MoveNotSpec()
子 MoveNotSpec()
'Filters for "Not Specified" on main sheet and cuts, pastes rows into new sheet and deletes empty rows on main sheet'
'在主工作表上过滤“未指定”并剪切、将行粘贴到新工作表中并删除主工作表上的空行'
Selection.AutoFilter
ActiveSheet.Range("A1:A2000").AutoFilter Field:=1, Criteria1:= _
"=*specified*", Operator:=xlAnd
ActiveSheet.UsedRange.Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("OtherSheet").Select
Range("A2").Select
ActiveSheet.Paste
Sheets("FirstSheet").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
ActiveSheet.Range("A1:A2000").AutoFilter Field:=1
End Sub
结束子
回答by bf2020
If you use the macro recorder, you can record the whole process you describe, then view the generated code. Because the script won't need to remember the places where it found "not specified", just loop until find can't find the string anymore. Also, remember to keep a row incrementor for the destination sheet.
如果使用宏记录器，可以记录下你描述的整个过程，然后查看生成的代码。因为脚本不需要记住它找到“未指定”的地方，所以循环直到 find 再也找不到字符串。另外，请记住为目标工作表保留一个行增量器。
回答by Venzan
Maybe try something similar to this:
也许尝试类似的东西：
Sub quickexample()
Call FilterData("not specified", xlFilterValues)
ActiveSheet.UsedRange.Copy
Worksheets.Add.Name = "NewSheet"
Worksheets("NewSheet").Paste
End Sub
结束子
Function FilterData(criteria, voperator)
[A1].AutoFilter Field:=1, _
Criteria1:=criteria, _
Operator:=voperator
End Function
结束函数
回答by cilla
Possibly try something like what I posted below. I got the bulk of it just from recording a macro, like another user suggested. If you're new to VBA just recording a macro is the best place to start most of the time!
可能尝试类似我在下面发布的内容。就像另一个用户建议的那样，我只是通过录制宏获得了大部分内容。如果您是 VBA 新手，大多数情况下只录制宏是最好的开始！
Dim intPasteRow As Integer
intPasteRow = 2
Sheets("FirstSheet").Select
Columns("A:A").Select
On Error Resume Next
Selection.Find(What:="not specified", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
If Err.Number = 91 Then
MsgBox "ERROR: 'not specified' could not be found."
End
End If
Dim intRow As Integer
intRow = ActiveCell.Row
Rows(intRow & ":" & intRow).Select
Selection.Cut
Sheets("OtherSheet").Select
Range("A" & intPasteRow).Select
ActiveSheet.Paste
Sheets("FirstSheet").Select
Rows(intRow & ":" & intRow).Select
Selection.Delete Shift:=xlUp
