vba 如何将数据从一本书的范围复制到另一本书的范围？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16672996/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I copy data from range of one book to range in another book?
提问by kgosi desmond
I'd like to copy values from the workbook "Consulting-for Paracon_aax.xls" for a range "E24" to "Q24" to a workbook named "2014 - XPERT.xlsm" on a range "E9" to "Q9" in a sheet named Delivery.
我想将范围“E24”到“Q24”的工作簿“Consulting-for Paracon_aax.xls”中的值复制到范围“E9”到“Q9”的名为“2014-XPERT.xlsm”的工作簿中名为 Delivery 的工作表。
This is what I've come up with.
这是我想出的。
Sub UpdateLinks()
'open the source workbook and select the source sheet
Workbooks.Open Filename:="C:\Users\desmondm\Desktop\Consulting-for Paracon_aax.xls"
Sheets("Sheet1").Select
' copy the source range
Sheets("Sheet1").Range("E24").Select
Selection.Copy
' select current workbook and paste the value at E9
Workbooks("2014 - XPERT.xlsm").Activate
Sheets("Delivery").Select
Sheets("Delivery").Range("E9").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Save
End Sub
My macro only copies cell E24 from the Consulting-for Paracon_aax.xls to E9 in the Delivery sheet within the 2014 - XPERT.xlsm workbook.
我的宏仅将单元格 E24 从 Consulting-for Paracon_aax.xls 复制到 2014 - XPERT.xlsm 工作簿中交付表中的 E9。
回答by David Zemens
Give this a try, I consolidated a bit for you, but basically you need to specify the full range of cells to copy (previously you were only copying E24.
试试这个，我为你整理了一些，但基本上你需要指定要复制的完整单元格范围（以前你只复制E24.
Sub UpdateLinks()
Dim wbSource As Workbook
Dim wbDestination As Workbook
'open the source workbook and select the source sheet
Set wbSource = Workbooks.Open( _
Filename:="C:\Users\desmondm\Desktop\Consulting-for Paracon_aax.xls")
'Set the destition workbook variable
Set wbDestination = Workbooks("2014 - XPERT.xlsm")
'copy the source range
wbSource.Sheets("Sheet1").Range("E24:Q24").Copy
'paste the value at E9
wbDestination.Sheets("Delivery").Range("E9:Q9").PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
ActiveWorkbook.Save
End Sub
And, a word to the wise :) it is tempting to use Selectand Activatemethods -- especially when you are new to VBA, because that is how the macro recorder captures your actions, but 99% of the time these are unnecessary, and it's more efficient/better to just use object variables and explicitly refer to them, rather than relying on Selectionand ActiveWorkbookor ActiveCell, etc.
而且，对聪明人来说：) 使用Select和Activate方法很诱人——尤其是当您不熟悉 VBA 时，因为这是宏记录器捕获您的操作的方式，但在 99% 的情况下，这些都是不必要的，而且更多只使用对象变量并显式引用它们，而不是依赖SelectionandActiveWorkbook或ActiveCell等，效率更高/更好。
