Excel VBA实现筛选后工作表D列起始数据复制至新工作表A列

我懂你的痛点——原代码把包括隐藏列在内的整个固定区域都复制了，所以新表的数据还是从D列“继承”了原表的隐藏列结构。咱们调整代码，只抓取可见的单元格/列，就能让数据直接从新表的A列开始排列。

首先先修正你原代码里的语法错误：你写的Set rng = "Worksheets("TESTSHEET".Range("A1:ZZ1000")引号嵌套错误，正确写法是Set rng = Worksheets("TESTSHEET").Range("A1:ZZ1000")，但即便修正这个，还是会复制隐藏列，所以核心要改的是获取数据的方式。

方案一：复制所有可见单元格（推荐） #

这个方法会自动跳过所有隐藏的行和列，完美适配筛选后的工作表：

Option Explicit
Sub CopyVisibleData()
    Dim sourceSheet As Worksheet
    Dim targetSheet As Worksheet
    Dim visibleRng As Range
    
    ' 绑定源工作表和新建目标工作表
    Set sourceSheet = ThisWorkbook.Worksheets("TESTSHEET")
    Set targetSheet = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    targetSheet.Name = "TEST2"
    
    ' 获取源表中所有可见的单元格区域（仅取已使用范围，避免多余空列）
    On Error Resume Next ' 处理没有可见数据的异常情况
    Set visibleRng = sourceSheet.UsedRange.SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    
    If Not visibleRng Is Nothing Then
        ' 将可见区域复制到目标表的A1起始位置
        visibleRng.Copy
        targetSheet.Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats ' 可按需选择粘贴类型：值、格式等
        Application.CutCopyMode = False ' 清除剪贴板的复制状态
    Else
        MsgBox "源工作表中没有可见的数据区域！"
    End If
End Sub

方案二：仅复制可见列（针对列隐藏场景） #

如果确定只有列被隐藏（行都是可见的），可以直接抓取可见列的范围，效率更高：

Option Explicit
Sub CopyVisibleColumns()
    Dim sourceSheet As Worksheet
    Dim targetSheet As Worksheet
    Dim visibleColumns As Range
    Dim lastRow As Long
    
    Set sourceSheet = ThisWorkbook.Worksheets("TESTSHEET")
    Set targetSheet = ThisWorkbook.Sheets.Add
    targetSheet.Name = "TEST2"
    
    ' 获取源表中所有可见的列
    On Error Resume Next
    Set visibleColumns = sourceSheet.UsedRange.Columns.SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    
    If Not visibleColumns Is Nothing Then
        ' 获取可见列的最后一行数据行号
        lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, visibleColumns.Column).End(xlUp).Row
        ' 复制可见列数据到目标表A1开始的位置
        visibleColumns.Resize(lastRow).Copy targetSheet.Range("A1")
        
        ' 如果只需要复制值（不需要格式），可以用赋值方式替代复制粘贴：
        ' targetSheet.Range("A1").Resize(lastRow, visibleColumns.Columns.Count).Value = visibleColumns.Resize(lastRow).Value
    Else
        MsgBox "源工作表中没有可见的列！"
    End If
End Sub

关键说明 #

• SpecialCells(xlCellTypeVisible)是核心：它会自动排除所有隐藏的行和列，只返回可见的单元格区域
• 使用UsedRange替代固定的A1:ZZ1000，能避免复制大量无用的空单元格，提升效率
• 两种方案都处理了异常情况（比如没有可见数据），避免代码报错

内容的提问来源于stack exchange，提问作者John De Boer