我需要一个宏，它将搜索某个列中的单元格，并将所有行复制到指定的工作簿 [英] I need a macro that will search for a cells in a certain column and copy all rows to a specified workbook

问题描述

我正在使用一个具有数千行的电子表格，并且我需要搜索以E开头的B列中的单元格，并将所有行复制到另一个工作簿。我没有找到这个具体问题的运气，或者我发现这不是我需要做的事情。电子表格每周更新一次，我需要一个宏来做这个搜索，并快速复制和粘贴，我不得不选择，复制和粘贴。任何帮助将不胜感激。

I am working with a spreadsheet that has thousands of lines and out of those lines I need to search for cells in Col. B that start with " E" and copy all the rows to a different workbook. I have had no luck with searching for this specific question or if I have found something it was not exactly what I needed it to do. The spreadsheet is updated weekly and i need a macro to do this search and copy and paste quickly with out me having to do the selecting, copying and pasting. Any help will be greatly appreciated.

推荐答案

如果您更改格式，并使用stenci建议的数据进行某些操作，是你可以避免诉诸于VBA。但是，如果您需要保留表格格式相同的位置，并且无法按建议操作数据（例如由于办公室官僚作风），则这是一个应该做的事情的子例程你在问我确实添加了大量的评论来描述每一行的作用。我希望它有帮助，祝你好运。

If you changed the format and did some maneuvering with the data as suggested by stenci, yes you could avoid resorting to VBA. However, if you are in a position where you need to keep the format of the sheet the same and aren't able to manipulate the data as suggested (due to office-place bureaucracy for instance), here is a subroutine that should do what you are asking. I made sure to add plenty of comments to describe what each line does. I hope it helps, good luck.

Sub copyRows()

Dim i As Integer, j As Integer, k As Integer
Dim bReport As Workbook, bReport2 As Workbook
Dim Report As Worksheet, Report2 As Worksheet

Set Report = Excel.ActiveSheet 'This assumes your sheet with data is the active sheet.
Set bReport = Report.Parent
Set bReport2 = Excel.Workbooks.Add 'This opens a new workbook
Set Report2 = bReport2.Worksheets(1) 'This assigns the variable for the worksheet where we will be pasting our data.

Report2.Cells(1, 1).Value = "Copied Rows" 'This gives a title to the worksheet (helps to avoid having to identify _
                                            the initial iteration of our for...next loop

For i = 1 To Report.UsedRange.Rows.Count 'Loops once for each row in the worksheet.

    If InStr(1, Left(Report.Cells(i, 2).Value, 1), "e", vbTextCompare) = 1 Then 'This searches the first letter of the value. I used the instr function to make it case-insensitive.
                                                                                    'You could also disable the binary comparison for the subroutine to accomplish this, but why bother?
        Report.Cells(i, 1).EntireRow.Copy 'This copies the row
        j = Report2.UsedRange.Rows.Count + 1 'This finds the row below the last used row in the new worksheet/workbook.
        Report2.Cells(j, 1).EntireRow.Insert (xlDown) 'This pastes the copied rows.
    End If
Next i

End Sub

注意：Siddharth Routh可能建议使用.end找到最后使用的单元格，而不是使用usedrange.rows.count，但是我习惯于后者。一旦我比较熟悉，我可以开始使用以前的方法。

Note: Siddharth Routh might suggest using .end to find the last used cell as opposed to using the usedrange.rows.count, but I am used to the latter of the two. I may start using the former method once I am a bit more familiar with it.

这篇关于我需要一个宏，它将搜索某个列中的单元格，并将所有行复制到指定的工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！