Excel VBA搜索多个工作表并将所选行粘贴到摘要工作表

Sub SearchAndCopy()
    Dim ws As Worksheet ' 工作表对象
    Dim summaryWs As Worksheet ' 摘要工作表对象
    Dim searchRange As Range ' 搜索范围
    Dim copyRange As Range ' 复制范围
    Dim pasteRow As Long ' 粘贴行数
    Dim cell As Range ' 单元格对象
    
    ' 设置摘要工作表
    Set summaryWs = ThisWorkbook.Sheets("摘要工作表")
    
    ' 清空摘要工作表内容
    summaryWs.UsedRange.Clear
    
    ' 设置搜索范围
    Set searchRange = ThisWorkbook.Sheets("工作表1").UsedRange
    
    ' 设置粘贴行数
    pasteRow = 1
    
    ' 遍历搜索范围
    For Each cell In searchRange
        ' 判断是否找到目标值
        If cell.Value = "目标值" Then
            ' 设置复制范围
            If copyRange Is Nothing Then
                Set copyRange = cell.EntireRow
            Else
                Set copyRange = Union(copyRange, cell.EntireRow)
            End If
        End If
    Next cell
    
    ' 将复制范围粘贴到摘要工作表
    If Not copyRange Is Nothing Then
        copyRange.Copy summaryWs.Cells(pasteRow, 1)
    End If
End Sub