从一个工作簿复制并粘贴到另一个工作簿 [英] Copy from one workbook and paste into another
本文介绍了从一个工作簿复制并粘贴到另一个工作簿的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我已经写了下面的代码,并且不断地看到class的特殊方法失败了。我试图克服这个问题,但似乎没有任何效果。我正在尝试从一本工作簿中复制整张表,并将其粘贴到另一本工作簿中:
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 work book, 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
这篇关于从一个工作簿复制并粘贴到另一个工作簿的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文