VBA复制目标但作为值

Ryflex 发表于 Dev

来福克斯

我正在尝试使用“复制目标”将数据从一个文档传输到另一文档，因为我想避免使用剪贴板，但是我希望它停止使用其格式设置...

Dim Sheet As Worksheet
Dim FoundLocationSheet As Boolean
Dim n As Long
Dim AllSheet As Worksheet
Set AllSheet = Sheets("Main")

'Transfer data
For n = 1 To AllSheet.Cells(Rows.Count, 1).End(xlUp).Row
    If AllSheet.Cells(n, 1) = "TiTle" Then
        With Sheets(AllSheet.Cells(n - 1, 1).Value)
            AllSheet.Cells(n, 1).CurrentRegion.Copy Destination:=.Cells(Rows.Count, 1).End(xlUp).Offset(2, 0)
        End With
    End If
Next n

宏可能会从中提取数据A20:L40并将其放入A15:L35...

我一直在尝试很多不同的东西，AllSheet.Cells(n, 1).CurrentRegion.Copy Destination:=.Cells(Rows.Count, 1).End(xlUp).Offset(2, 0)但无法弄清楚如何使其工作...

工作表的大小意味着清除格式花费的时间太长：/

有任何想法吗？

克里斯·尼尔森

由于您要避免使用剪贴板而只复制值，因此可以对Value属性使用赋值，而不是Range.Copy

像这样

Sub Demo()
    Dim Sheet As Worksheet
    Dim FoundLocationSheet As Boolean
    Dim n As Long
    Dim rSource As Range
    Dim rDest As Range
    Dim AllSheet As Worksheet

    Set AllSheet = Sheets("Main")

    'Transfer data
    For n = 1 To AllSheet.Cells(Rows.Count, 1).End(xlUp).Row
        If AllSheet.Cells(n, 1) = "TiTle" Then
            With Worksheets(AllSheet.Cells(n - 1, 1).Value)
                ' Reference the range to be copied
                Set rSource = AllSheet.Cells(n, 1).CurrentRegion
                ' Reference the Top Left cell of the destination range
                ' and resize to match source range
                Set rDest = _
                  .Cells(.Rows.Count, 1).End(xlUp).Offset(2, 0) _
                  .Resize(rSource.Rows.Count, rSource.Columns.Count)
                ' Copy values
                rDest.Value = rSource.Value
            End With
        End If
    Next n
End Sub