vba 从一个工作簿复制并粘贴到另一个

Copy from one workbook and paste into another

提问by user2832896

I have written the following code and continually see pastespecial method of class has failed. I have tried to overcome this issue, but nothing seems to work. I am trying to copy an entire sheet from one workbook, and paste it into another:

我编写了以下代码并不断看到pastespecial method of class has failed. 我试图克服这个问题，但似乎没有任何效果。我正在尝试从一个工作簿中复制整个工作表，然后将其粘贴到另一个工作簿中：

Set x = Workbooks.Open(" path to copying book ")
Workbooks.Open(" path to copying book ").Activate
Range("A1").Select
'Cells.Select
Selection.Copy
Set y = Workbooks.Open("path to pasting book")
Workbooks.Open("Path to pasting book").Activate

With y
    Sheets("sheetname").Cells.Select
    Range("A1").PasteSpecial
    'Sheets("sheetname").PasteSpecial
    .Close
End With

With x
    .Close
End With

回答by David Zemens

This should do it, let me know if you have trouble with it:

这应该可以，如果您遇到问题，请告诉我：

Sub foo()
Dim x As Workbook
Dim y As Workbook

'## Open both workbooks first:
Set x = Workbooks.Open(" path to copying book ")
Set y = Workbooks.Open(" path to destination book ")

'Now, copy what you want from x:
x.Sheets("name of copying sheet").Range("A1").Copy

'Now, paste to y worksheet:
y.Sheets("sheetname").Range("A1").PasteSpecial

'Close x:
x.Close

End Sub

Alternatively, you could just:

或者，您可以：

Sub foo2()
Dim x As Workbook
Dim y As Workbook

'## Open both workbooks first:
Set x = Workbooks.Open(" path to copying book ")
Set y = Workbooks.Open(" path to destination book ")

'Now, transfer values from x to y:
y.Sheets("sheetname").Range("A1").Value = x.Sheets("name of copying sheet").Range("A1") 

'Close x:
x.Close

End Sub

To extend this to the entire sheet:

要将其扩展到整个工作表：

With x.Sheets("name of copying sheet").UsedRange
    'Now, paste to y worksheet:
    y.Sheets("sheet name").Range("A1").Resize( _
        .Rows.Count, .Columns.Count) = .Value
End With

And yet another way, store the value as a variable and write the variable to the destination:

还有另一种方式，将值存储为变量并将变量写入目标：

Sub foo3()
Dim x As Workbook
Dim y As Workbook
Dim vals as Variant

'## Open both workbooks first:
Set x = Workbooks.Open(" path to copying book ")
Set y = Workbooks.Open(" path to destination book ")

'Store the value in a variable:
vals = x.Sheets("name of sheet").Range("A1").Value

'Use the variable to assign a value to the other file/sheet:
y.Sheets("sheetname").Range("A1").Value = vals 

'Close x:
x.Close

End Sub

The last method above is usually the fastest for most applications, but do note that for very large datasets (100k rows) it's observed that the Clipboard actually outperforms the array dump:

对于大多数应用程序，上面的最后一种方法通常是最快的，但请注意，对于非常大的数据集（100k 行），观察到剪贴板实际上优于数组转储：

Copy/PasteSpecial vs Range.Value = Range.Value

复制/粘贴特殊 vs Range.Value = Range.Value

That said, there are otherconsiderations than just speed, and it may be the case that the performance hit on a large dataset is worth the tradeoff, to avoid interacting with the Clipboard.

也就是说，除了速度之外还有其他考虑因素，并且在大型数据集上的性能下降可能值得权衡，以避免与剪贴板交互。

回答by L42

You copied using Cells.
If so, no need to PasteSpecial since you are copying data at exactly the same format.
Here's your code with some fixes.

您使用单元格进行了复制。
如果是这样，则无需 PasteSpecial，因为您正在以完全相同的格式复制数据。
这是您的代码，并进行了一些修复。

Dim x As Workbook, y As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet

Set x = Workbooks.Open("path to copying book")
Set y = Workbooks.Open("path to pasting book")

Set ws1 = x.Sheets("Sheet you want to copy from")
Set ws2 = y.Sheets("Sheet you want to copy to")

ws1.Cells.Copy ws2.cells
y.Close True
x.Close False

If however you really want to paste special, use a dynamic Range("Address") to copy from.
Like this:

但是，如果您真的想粘贴特殊内容，请使用动态范围（“地址”）进行复制。
像这样：

ws1.Range("Address").Copy: ws2.Range("A1").PasteSpecial xlPasteValues
y.Close True
x.Close False

Take note of the :colon after the .Copywhich is a Statement Separatingcharacter.
Using Object.PasteSpecialrequires to be executed in a new line.
Hope this gets you going.

请注意:后面的冒号，.Copy这是一个Statement Separating字符。
使用Object.PasteSpecial需要在新行中执行。
希望这能让你继续前进。