VBA粘贴范围 [英] VBA paste range

问题描述

我有一个简单的目标是复制范围并将其粘贴到另一个电子表格中。以下代码下面提供副本，但不会粘贴。

I have simple goal of copying range and pasting it into another spreadsheet. The following code below gives copies, but does not paste.

Sub Normalize()

    Dim Ticker As Range
    Sheets("Sheet1").Activate
    Set Ticker = Range(Cells(2, 1), Cells(65, 1))
    Ticker.Copy

    Sheets("Sheet2").Select
    Cells(1, 1).Activate
    Ticker.PasteSpecial xlPasteAll

End Sub

任何建议？

推荐答案

要真正修正您的示例，您将使用以下内容：

To literally fix your example you would use this:

Sub Normalize()


    Dim Ticker As Range
    Sheets("Sheet1").Activate
    Set Ticker = Range(Cells(2, 1), Cells(65, 1))
    Ticker.Copy

    Sheets("Sheet2").Select
    Cells(1, 1).PasteSpecial xlPasteAll



End Sub

为了摆脱选择和激活：

Sub Normalize()
    With Sheets("Sheet1")
        .Range(.Cells(2, 1), .Cells(65, 1)).Copy Sheets("Sheet2").Cells(1, 1)
    End With
End Sub

但是使用剪贴板需要时间和资源，所以最好的方法是避免复制和粘贴，并将值设置为等于所需的值。

but using the clipboard takes time and resources so the best way would be to avoid a copy and paste and just set the values equal to what you want.

Sub Normalize()
Dim CopyFrom As Range

Set CopyFrom = Sheets("Sheet1").Range("A2", [A65])
Sheets("Sheet2").Range("A1").Resize(CopyFrom.Rows.Count).Value = CopyFrom.Value

End Sub

定义 CopyFrom 您可以使用任何您想要定义范围的内容，您可以使用 Range（A2：A65）， Range（A2 [A65]），范围（A2，A65）全部都是有效的条目。如果A2：A65永远不会改变代码可以进一步简化为：

To define the CopyFrom you can use anything you want to define the range, You could use Range("A2:A65"), Range("A2",[A65]), Range("A2", "A65") all would be valid entries. also if the A2:A65 Will never change the code could be further simplified to:

Sub Normalize()

Sheets("Sheet2").Range("A1:A65").Value = Sheets("Sheet1").Range("A2:A66").Value

End Sub

我添加了从范围复制，并且调整大小财产，使其稍微更有活力，以防将来您想要使用其他范围。

I added the Copy from range, and the Resize property to make it slightly more dynamic in case you had other ranges you wanted to use in the future.

这篇关于VBA粘贴范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！