vba Microsoft Excel - 如何将行从一个工作表复制到另一个按值过滤的工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28404339/
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
Microsoft Excel - how do I copy rows from one sheet to another sheet filtered by a value
提问by aceraven777
Example:
例子：
I have Sheet1 that looked like these:
我有看起来像这样的 Sheet1：
In Sheet2 I want an output like these (Here I want to get all rows with the Item 'CAMERA'):
在 Sheet2 中，我想要这样的输出（在这里，我想获取带有“CAMERA”项的所有行）：
I have a hard time composing formula to product Sheet2, can you help me what formula to use?
我很难为产品 Sheet2 编写公式，您能帮我使用什么公式吗？
采纳答案by Dane I
This macro should do the trick:
这个宏应该可以解决问题：
Step 1: Do Alt+F11 to open the 'Microsoft Visual Basic for Applications'
步骤 1：按 Alt+F11 打开“Microsoft Visual Basic for Applications”
Step 2: Hit F7 to open up a blank code sheet
第 2 步：按 F7 打开空白代码表
Step 3: Copy the below coding onto the sheet:
第 3 步：将以下代码复制到工作表上：
Sub FilterandCopy()
Dim Equipment As String
Equipment = InputBox("Which piece of equipment?")
With ActiveSheet.Range("A:D")
.AutoFilter Field:=1, Criteria1:="" & Equipment & ""
End With
ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible).Copy _
Destination:=Worksheets("Sheet2").Range("A2")
Sheets("Sheet2").Range("A1").Value = "" & Equipment & ""
End Sub
Step 4: Hit the red x to close the visual basic.
第 4 步：点击红色 x 关闭视觉基础。
Step 5: In your spreadsheet, go to the developer tab, and click the 'Macros' button.
第 5 步：在您的电子表格中，转到开发人员选项卡，然后单击“宏”按钮。
Step 6: Select the macro just created (called FilterandCopy) and click Run. (Note: A pop-up will come up asking for the equipment type, type in CAMERAto get the camera items)
第 6 步：选择刚刚创建的宏（称为 FilterandCopy）并单击运行。（注意：会弹出一个询问设备类型的弹出窗口，输入CAMERA以获取相机项目）
回答by El Scripto
I like the solution you were given, however here is another idea that might help you : * this assumes that you selected "by hand" from the filter a SINGLE value.
我喜欢您给出的解决方案，但是这里有另一个可能对您有所帮助的想法： * 这假设您从过滤器中“手动”选择了一个 SINGLE 值。
Public Sub CopySheet()
' Clear prev data
Sheet2.Rows.ClearContents
Sheet2.Rows.Delete
' Copy filterd data
Sheet1.Activate
Sheet1.UsedRange.Select
Selection.Copy
' Paste the filterd data
Sheet2.Activate
Sheet2.Range("A2").Select
ActiveSheet.Paste
' Copy the filtered "word" to sheet2, for example- "Camera"
Sheet1.Range("A2").Copy
Sheet2.Range("A1").Select
ActiveSheet.Paste
' Increase the size of the word above.
Sheet2.Range("A1").Font.Size = Sheet2.Range("A1").Font.Size + 5
End Sub
