VBA 指定列的数据复制到另一工作表的方法

锁匙isthekey

已于 2023-11-06 14:44:06 修改

阅读量5.9k

点赞数 1

分类专栏： EXCEL 文章标签： excel

于 2023-11-06 14:39:20 首次发布

本文链接：https://blog.csdn.net/weixin_44819434/article/details/134245990

版权

EXCEL 专栏收录该内容

26 篇文章 2 订阅

订阅专栏

在这里插入图片描述
前后代码略过


   If rowsnum > 0 Then


'方法一：先全部复制过去，再删除不要的列
'     Dim rngnew As Range
'     Set rngnew = ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible)
'
'     Sheets("查询汇总").Select '转到“查询汇总”表
'
'     With Sheets("查询汇总")
'         .Range("A1").Select  '选择A4单元格
'         rngnew.Copy .Range("A1")
'         .Range("AD1:AJ" & rowsnum).Delete Shift:=xlToLeft
'         .Range("AA1:AA" & rowsnum).Delete Shift:=xlToLeft
'         .Range("N1:Y" & rowsnum).Delete Shift:=xlToLeft
'         .Range("I1:L" & rowsnum).Delete Shift:=xlToLeft
'     End With
'  
''方法二：获取未隐藏行号，遍历行号，获取数据再复制(遍历的速度有点慢）
'    Dim rngnew As Range
'    Dim i As Long, j As Long, k As Long
'    Set rngnew = Sheets("查询汇总").Range("A:Z")

'    k = 1 '初始化K值
'    For i = 1 To rowsnum
'     If Not ActiveSheet.Rows(i).Hidden Then
'      j = ActiveSheet.Cells(i, "A").Row '获取显示值的行号
'      rngnew.Cells(k, 1) = Sheets("数据").Cells(j, 1).Value
'      rngnew.Cells(k, 2) = Sheets("数据").Cells(j, 2).Value
'      rngnew.Cells(k, 3) = Sheets("数据").Cells(j, 3).Value
'      rngnew.Cells(k, 4) = Sheets("数据").Cells(j, 4).Value
'      rngnew.Cells(k, 5) = Sheets("数据").Cells(j, 5).Value
'      rngnew.Cells(k, 6) = Sheets("数据").Cells(j, 6).Value
'      rngnew.Cells(k, 7) = Sheets("数据").Cells(j, 7).Value
'      rngnew.Cells(k, 8) = Sheets("数据").Cells(j, 8).Value
'      rngnew.Cells(k, 9) = Sheets("数据").Cells(j, 13).Value
'      rngnew.Cells(k, 10) = Sheets("数据").Cells(j, 26).Value
'      rngnew.Cells(k, 11) = Sheets("数据").Cells(j, 29).Value
'      rngnew.Cells(k, 12) = Sheets("数据").Cells(j, 30).Value
'      k = k + 1
'
'     End If
'    Next
'    rngnew.Copy Sheets("查询汇总").Range("A1")
'方法三：类似方法一，指定range进行合并，再复制
     Dim rngnew As Range
     Set rngnew = ActiveSheet.Range("A1:H" & rowsnum).SpecialCells(xlCellTypeVisible)
     Set rngnew = Union(rngnew, ActiveSheet.Range("M1:M" & rowsnum).SpecialCells(xlCellTypeVisible))
     Set rngnew = Union(rngnew, ActiveSheet.Range("Z1:Z" & rowsnum).SpecialCells(xlCellTypeVisible))
     Set rngnew = Union(rngnew, ActiveSheet.Range("AB1:AC" & rowsnum).SpecialCells(xlCellTypeVisible))

     Sheets("查询汇总").Select '转到“查询汇总”表


    ActiveSheet.Range("A1").Select  '选择A4单元格
    rngnew.Copy ActiveSheet.Range("A1")

    
  End If