vba - 将合并数据从多个工作表复制到 .ActiveWorksheet

Question

我一直在研究这篇文章，试图将多个工作表中的数据合并到一个汇总工作表中。我几乎让它工作了，但我正在努力改变目标工作表。

我试图让合并的数据出现在工作表B4上的单元格中Consolidated Tracker。

 With CopyRng

         Set DestSh = ThisWorkbook.Worksheets("Consolidated Tracker")
         Set myRange = DestSh.Range("B4")

 End With

问题是 myRange 始终为空，并且没有任何内容被复制。

没有错误，似乎f8按预期执行而无需复制任何内容。

---

完整代码供参考：

Sub CopyRangeFromMultiWorksheets()

    Dim sh As Worksheet
    Dim DestSh As Worksheet
    Dim Last As Long
    Dim CopyRng As Range

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    ' Loop through all worksheets and copy the data to the
    ' summary worksheet.
    For Each sh In ActiveWorkbook.Worksheets

            ' Find the last row with data on the summary worksheet.
            Last = LastRow(DestSh)

            ' Specify the range to place the data.
            Set CopyRng = sh.Range("B4:B50")

            ' This statement copies values
            With CopyRng
                 Set DestSh = ThisWorkbook.Worksheets("Consolidated Tracker")
                 Set myRange = DestSh.Range("B4")
            End With

        'End If
    Next

ExitTheSub:

    Application.Goto DestSh.Cells(4, 2)

    ' AutoFit the column width in the summary sheet.
    DestSh.Columns.AutoFit

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub    

标签： vbaexcel

Answer 1

问题是代码实际上从未执行任何类型的命令来移动数据。该代码仅设置变量。

查看下面修改后的代码，特别是 End With 之前的最后一行。

' Specify the range to place the data.
Set CopyRng = sh.Range("B4:B50")

' This statement copies values
With CopyRng
    Set DestSh = ThisWorkbook.Worksheets("Consolidated Tracker")
    DestSh.Range("B4").Resize(CopyRng.Rows.Count,1).Value = CopyRng.Value
End With