从一本工作簿复制并粘贴到另一工作簿中

我编写了以下代码并不断看到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

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

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

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

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

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

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 行），观察到剪贴板实际上优于数组转储：

复制/粘贴特殊与 Range.Value = Range.Value https://stackoverflow.com/questions/45019541/copy-pastespecial-vs-range-value-range-value/45020388#45020388

也就是说，有other考虑的不仅仅是速度，在这种情况下，为了避免与剪贴板交互，对大型数据集的性能影响值得权衡。