首页 文章

直接复制过滤后的数据无效

提问于
浏览 1267
0

我想使用直接复制将数据从一个工作簿复制到另一个工作簿,以避免剪贴板使用 .

SourceWS = Workbooks("Src").Worksheets("data src").Range("A1", "J" & lastRow)
DestWS = Workbooks("Dest").Worksheets("Data results").Resize(SourceWS.Rows.Count, SourceWS.Columns.Count)
DestWS.Value = SourceWS.Value

(lastrow在宏观中较早定义并且运行良好)

运行宏时,我收到一条错误消息 DestWS = Workbooks("Dest").Worksheets("Data results").Resize(SourceWS.Rows.Count, SourceWS.Columns.Count) 告诉 Runtime error '404' Object required 我不知道我的错误在哪里 .

此外,另外一个问题,源数据是过滤数据,我想只有它们作为目标工作表上的结果数据,但我不确定它会在最后这样做 . 有人可以确认和帮助吗?

2 回答

  • 0

    此片段将所有可见单元格复制到目标工作表上的相同位置,但为已过滤的行留下空单元格 .

    Dim shSrc As Worksheet, shTrg As Worksheet
    Dim cl As Range     ' aux, loop var
    Dim rSrc As Range   ' source range
    Dim rTrg As Range   ' target range
    
    Set shSrc = WorkBooks("Source.xlsx").Worksheets("source data sheet")
    If shSrc Is Nothing Then Exit Sub
    Set shTrg = Workbooks("Target.xlsx").Worksheets("Target data sheet")
    If shTrg Is Nothing Then Exit Sub
    
    Set rSrc = shSrc.Range("A10:M50")
    For Each cl In rSrc.SpecialCells(xlCellTypeVisible)
        shTrg.Cells(cl.Row, cl.Column).Value = cl.Value
    Next cl
    
  • 0

    设置对象,调整单元格大小而不是工作表,然后复制过滤后的数据,然后将任何公式恢复为其已解析的值 .

    'assumes filter is already in place
    SET SourceWS = Workbooks("Src").Worksheets("data src").Range("A1", "J" & lastRow)
    SET DestWS = Workbooks("Dest").Worksheets("Data results")
    with SourceWS
        .COPY destination:=DestWS.cells(1)
        DestWS.cells.Resize(.Rows.Count, .Columns.Count) = _
          DestWS.cells.Resize(.Rows.Count, .Columns.Count).Value
    end with
    

相关问题