Excel筛选拷贝

Excel中如果要拷贝粘贴筛选选的内容无法直接粘贴，因为粘贴时候不会过滤隐藏列。
此时可通过VB脚本在两个Sheet页中通过关键字匹配自动拷贝；

Sub CopyColumnsBasedOnTargetName()
    Dim wsSource As Worksheet
    Dim wsTarget As Worksheet
    Dim lastRowSource As Long
    Dim lastRowTarget As Long
    Dim i As Long
    Dim j As Long
    Dim keyCol As Variant
    Dim sourceColumn As Variant
    Dim targetColumns As Variant
    Dim targetRow As Long
    Dim foundCell As Range
    Dim targetName As String
    Dim keyValue As String
    
    ' 定义工作表
    Set wsSource = ThisWorkbook.Sheets("源Sheet页")
    Set wsTarget = ThisWorkbook.Sheets("目标Sheet页")
    
    ' 定义需要查找的列和目标列
    sourceColumn = "F"
    keyCol = "B"
    ' 定义要拷贝的列
    targetColumns = Array("F", "I", "J", "K", "L", "M", "N")
    
    ' 两个Sheet页相同列内容，通过该列匹配复制表格
    targetName = "XXX"
    
    ' 找到源工作表和目标工作表中最后一行
    lastRowSource = wsSource.Cells(wsSource.Rows.Count, keyCol).End(xlUp).Row
    lastRowTarget = wsTarget.Cells(wsTarget.Rows.Count, keyCol).End(xlUp).Row
    
    ' 循环遍历源工作表中的每一行（从第二行开始）
    For i = 2 To lastRowSource
        If wsSource.Cells(i, sourceColumn).Value = targetName Then
            keyValue = wsSource.Cells(i, keyCol).Value
            
            ' 在目标工作表中找到对应的行
            Set foundCell = wsTarget.Columns(keyCol).Find(What:=keyValue, LookIn:=xlValues, LookAt:=xlWhole)
            If Not foundCell Is Nothing Then
                targetRow = foundCell.Row
                
                ' 直接复制指定的列数据到目标工作表的对应列和行数
                For j = LBound(targetColumns) To UBound(targetColumns)
                    wsTarget.Cells(targetRow, targetColumns(j)).Value = wsSource.Cells(i, targetColumns(j)).Value
                Next j
            End If
        End If
    Next i
    
    ' 提示完成
    MsgBox "数据复制完成"
End Sub