时间戳

首页 > 解决方案 > 从外部工作表复制一组数据，包括隐藏行

excel - 从外部工作表复制一组数据，包括隐藏行

问题描述

所以我想从外部工作表（源）复制一个外部数据集，但源有一个过滤器并且隐藏了一些行。现在我希望的输出是它复制所有数据，包括隐藏的数据。下面是我的代码，其中“ROCV”是内部目标表，“PROJECT DETAIL”是外部数据源：（顺便说一下，我是第一次来这里，也是一个非常业余的 VBA 编码器，所以请多多包涵：D）

Option Explicit
 
Sub CopyOutput()
    Dim wb1 As Workbook, wb2 As Workbook
    Dim Ret1, Ret2
 
    Set wb1 = ActiveWorkbook
 
    '~~> Get the File
    Ret1 = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*", _
    , "Please select file")
    If Ret1 = False Then Exit Sub
 
    Set wb2 = Workbooks.Open(Ret1, UpdateLinks:=False)
    wb2.Sheets("PROJECT DETAIL").Range("a7").CurrentRegion.Copy Destination:=wb1.Worksheets("ROCV").Range("A7")
    wb2.Close SaveChanges:=False

 
 
    Set wb2 = Nothing
    Set wb1 = Nothing
End Sub

标签： excelvba

解决方案

使用ShowAllData

Sub test()

    Dim wb1 As Workbook, wb2 As Workbook
    Dim Ret1, Ret2
    Dim Ws As Worksheet
    Dim vDB As Variant
    
    Set wb1 = ActiveWorkbook
    
    '~~> Get the File
    Ret1 = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*", _
    , "Please select file")
    If Ret1 = False Then Exit Sub
    
    Set wb2 = Workbooks.Open(Ret1, UpdateLinks:=False)
    Set Ws = wb2.Sheets("PROJECT DETAIL")
    
    If Ws.FilterMode Then
        Ws.ShowAllData
    End If
    
    '** Receives values into a two-dimensional array, and assigns the values of the array back to the target worksheet.
    vDB = Ws.Range("a7").CurrentRegion
    wb1.Worksheets("ROCV").Range("A7").Resize(UBound(vDB, 1), UBound(vDB, 2)) = vDB
    
    'Ws.Range("a7").CurrentRegion.Copy Destination:=wb1.Worksheets("ROCV").Range("A7")
    'wb2.Sheets("PROJECT DETAIL").Range("a7").CurrentRegion.Copy Destination:=wb1.Worksheets("ROCV").Range("A7")
    wb2.Close SaveChanges:=False
    
    
    
    Set wb2 = Nothing
    Set wb1 = Nothing
End Sub

推荐阅读