vba 如何从一个excel文件中复制单元格值并将其粘贴到另一个excel文件中？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15999556/
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 to copy a cell value from one excel file and paste it to another excel file?
提问by Bryan R. Olmedo
I would like to copy a cell value from one workbook to another, but the problem is that the cell values are from different sheets of the workbook. How can I do this? Could you help me with this?
我想将一个单元格值从一个工作簿复制到另一个工作簿，但问题是单元格值来自工作簿的不同工作表。我怎样才能做到这一点？你能帮我解决这个问题吗？
回答by David Zemens
This is the bones of what you need, you can do a lot more with looping over ranges of cells, sheets in workbooks, etc., but fundamentally you will need to specify in VBA what is the target workbook, worksheet, and cell range, and then set that .Valueequal to a specific workbook/worksheet/cell .Valuein another workbook.
这是您需要的基本内容，您可以通过循环单元格范围、工作簿中的工作表等执行更多操作，但从根本上说，您需要在 VBA 中指定目标工作簿、工作表和单元格范围是什么，然后将其设置为.Value等于.Value另一个工作簿中的特定工作簿/工作表/单元格。
I use Workbook and Worksheet variables in this example, you can also use range variables for the cell(s) if you desire or if you need to apply to multiple cells.
我在这个例子中使用了 Workbook 和 Worksheet 变量，如果你愿意或者你需要应用到多个单元格，你也可以使用单元格的范围变量。
Sub TransferValues()
Dim wb1 as Workbook
Dim wb2 as Workbook
Dim ws1 as Worksheet
Dim ws2 as Worksheet
Set wb1 = Workbooks("First Workbook.xlsx") '<modify as needed'
Set ws1 = wb1.Sheets("The sheet name") '<modify as needed'
Set wb2 = Workbooks("Another Workbook.xlsx") '<modify as needed'
Set ws2 = wb2.Sheets("Another sheet name") '<modify as needed'
'This line puts the value from wb1.ws1 in wb2.ws2, cells specified:
ws2.Range("A1").Value = ws1.Range("A1").Value
End Sub
回答by NickSlash
I'm guessing you want a formula, and not instructions on copy and paste.
我猜你想要一个公式，而不是关于复制和粘贴的说明。
Cell references can be made using the following format.
可以使用以下格式进行单元格引用。
=[Workbook Name]SheetName!CellAddress
=[Workbook Name]SheetName!CellAddress
Example, you have two workbooks open, Book1 and Book2.
例如，您打开了两个工作簿，Book1 和 Book2。
Book1, Sheet1, Cell A1 = =[Book2]Sheet1!A1
书 1、表 1、单元格 A1 = =[Book2]Sheet1!A1
Book2, Sheet1, Cell A1 = Hello World
书 2、表 1、单元格 A1 = Hello World
The value of [Book1]Sheet1!A1will now be "Hello World" (it will always reflect the value that is in [Book2]Sheet1!A1, providing the book is open)
的值[Book1]Sheet1!A1现在将是“Hello World”（它将始终反映 中的值[Book2]Sheet1!A1，前提是书是打开的）
If you do want a copyof the actual value, and not a reference to it, you will need to just use plain copy and paste oruse the above formulas and when you're done use copy+paste special (paste values) to convert the formulas to their values.
如果您确实想要实际值的副本，而不是对它的引用，则只需使用简单的复制和粘贴或使用上述公式，完成后使用复制+粘贴特殊（粘贴值）进行转换其值的公式。
Update
更新
Just realised it was tagged excel-vbaso you'd need to use something like @DavidZemens' answer.
刚刚意识到它被标记为excel-vba所以你需要使用类似@DavidZemens 的答案。
You could also use the macro recorder to do this, but doing it manually like David suggests will result in nicer code that's easier to re-use/modify later on.
您也可以使用宏记录器来执行此操作，但是像 David 建议的那样手动执行会产生更好的代码，以后更容易重用/修改。
