从一个工作簿复制并粘贴到另一个 [英] Copy from one workbook and paste into another

问题描述

我写了下面的代码，不断看到pastespecial 类的方法失败.我试图克服这个问题，但似乎没有任何效果.我正在尝试从一个工作簿中复制整个工作表，然后将其粘贴到另一个工作簿中:

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:

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

推荐答案

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

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

或者，您可以:

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

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

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:

Copy/PasteSpecial vs Range.Value = Range.Value

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

That said, there are other considerations 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.

这篇关于从一个工作簿复制并粘贴到另一个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！