Excel VBA - 如何有效地将多个单元格从一个文件复制到另一个文件？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8557669/
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
Excel VBA - how to efficiently copy multiple cells from one file to another?
提问by replax
currently, I am struggeling with the following: I have two Excel files. One files contains a lot of data, the other one is used to evaluate it. Now I need to efficiently copy data from the source to the target workbook. The source datapattern looks similar to the following:
目前，我正在努力解决以下问题：我有两个 Excel 文件。一个文件包含大量数据，另一个用于评估它。现在我需要有效地将数据从源复制到目标工作簿。源数据模式类似于以下内容：
x A B C D E F G H I
1 Oct bla bla bla bla bla bla bla bla
2 75 66
3 Nov bla bla IMT Frm bla bla IMT Frm
Where bla represents uninteresting data, Column A represents the Month (important) and IMT is important data, which is the cause of why I am doing this. Also, Frm means the cell contains a formula, rather than a value and has to be preserved/restored. Furthermore, the cell formatting of the target file has to be preserved (can be achieved with .PasteSpecial).
bla 代表无趣的数据，A 列代表月份（重要），IMT 是重要数据，这就是我这样做的原因。此外， Frm 意味着该单元格包含一个公式，而不是一个值，并且必须保留/恢复。此外，必须保留目标文件的单元格格式（可以使用 .PasteSpecial 实现）。
The Target Workbook does look exactly the same, omitting the half emty row "2".
目标工作簿看起来完全一样，省略了半空行“2”。
Every row has about 10 valuable entries. How would I, most efficiently, be able to copy the data into the second workbook? Please let me know if there is more information which I could provide. Thank you for your time!
每行大约有 10 个有价值的条目。我如何才能最有效地将数据复制到第二个工作簿中？如果我能提供更多信息，请告诉我。感谢您的时间！
EDIT: example screenshotClarification: The screenshot shows the source sheet. The important information is the one which is selected in the ss. The sheet goes on to the right like this (until BF). I need to copy every sixth entry. The target sheet looks exactly the same (omitting the half empty middle row), except for the needed information which, in the source, is a formula but it needs to be the value in the target. Thank you very much for helping me!
编辑：示例屏幕截图说明：屏幕截图显示了源表。重要信息是在 ss 中选择的信息。工作表像这样向右移动（直到 BF）。我需要每六个条目复制一次。目标表看起来完全相同（省略了中间的半空行），除了所需的信息，在源中，它是一个公式，但它需要是目标中的值。非常感谢你帮助我！
回答by ja72
Here is an example code that will get you going in the right direction:
这是一个示例代码，可以让您朝着正确的方向前进：
Public Sub CopyValues()
Dim wb_src As Workbook, wb_dst As Workbook
Dim ws_src As Worksheet, ws_dst As Worksheet
Set wb_src = Workbooks.Open("....")
Set wb_dst = Workbooks.Add()
Set ws_src = wb_src.Sheets(1)
Set ws_dst = wb_dst.Sheets(1)
Dim data() As Variant
Dim r_src As Range
Dim r_dst As Range
Set r_src = ws_src.Range("B4").Resize(1000, 100)
Set r_dst = ws_dst.Range("B4").Resize(1000, 100)
data = r_src.Value2
r_dst.Value2 = data
End Sub
回答by MacGyver
Try this:
尝试这个：
Option Explicit
Sub test()
Dim wb As Workbook, wb2 As Workbook
Dim ws As Worksheet
Dim vFile As Variant
'Set source workbook
Set wb = ActiveWorkbook
'Open the target workbook
vFile = Application.GetOpenFilename("Excel-files,*.xls", _
1, "Select One File To Open", , False)
'if the user didn't select a file, exit sub
If TypeName(vFile) = "Boolean" Then Exit Sub
Workbooks.Open vFile
'Set targetworkbook
Set wb2 = ActiveWorkbook
'Copy data from a range in the first workbook to another range in the other workbook
wb2.Worksheets("Sheet2").Range("C3:D4").Value = wb.Worksheets("Sheet1").Range("A1:B2").Value
End Sub
回答by replax
so after some more research, I think I have found an answer. Please, correct or improve me if my approach is off/wrong/not ideal. I am using a loop and then just assingin the value... I am wondering, coming from C++, if this is the "shortest" way of doing it? looks like a bloated for loop to me...
所以经过更多的研究，我想我已经找到了答案。如果我的方法不正确/错误/不理想，请纠正或改进我。我正在使用一个循环，然后只是 assingin 值...我想知道，来自 C++，如果这是做它的“最短”方式？对我来说看起来像是一个臃肿的 for 循环......
Dim i As Integer
For i = 6 To 55
targetSheetSal.Cells(ActiveCell.Row, i + 1).Value = sourceSheet.Cells(24, i).Value
i = i + 5
Next i
Thank you all for your time, input and advice!
感谢大家的时间，投入和建议！
