我想复制第一列中与A3，A4，A5，A6中具有相同单词的整行中的一些，并从空行中粘贴到另一张纸上？

Question

我有一些行在copySheet。sheet1在第一列中有一些特殊的关键字"Ojha"。所以我要复制第一行包含"Ojha"的整行&将其粘贴到另一个粘贴表中。sheet2。一开始我发现那个牢房有"欧哈"我把它放在Foundcell中。所以现在我用…:

Foundcell.EntireRow.Copy

,在另一个工作表中，我首先找到空白行，从行粘贴的地方，所以

RowCount = WorksheetFunction.CountA(pasteSheet.Range("A:A")) + 1
pasteSheet.Range("A" & RowCount).PasteSpecial xlPasteValues

所以它只粘贴了包含"Ojha"的第一行现在我要复制所有在第一列中包含"Ojha"的行&粘贴到下一页

Answer 1

如果你只想循环遍历单元格，这就可以了

Sub Loopy()
    Dim sh As Worksheet, ws As Worksheet
    Dim Rws As Long, rng As Range, c As Range
    Set sh = Sheets("Sheet1")
    Set ws = Sheets("Sheet2")
    Application.ScreenUpdating = 0
    With sh
        Rws = .Cells(.Rows.Count, "A").End(xlUp).Row
        Set rng = .Range(.Cells(1, 1), .Cells(Rws, 1))
    End With
    With ws
        For Each c In rng.Cells
            If c = "Ojha" Then
                c.EntireRow.Copy
                .Cells(.Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial xlPasteValues
            End If
        Next c
    End With
    Application.CutCopyMode = 0
End Sub