vba 从范围循环中的每个单元格复制数据并将其粘贴到另一张纸上
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27404282/
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
copy data from each cell in range loop and paste it on another sheet
提问by acr
I have below vba code Sub Macro1()
我有以下 vba 代码 Sub Macro1()
Dim FLrange As Range
Set FLrange = Range("C3:E3")
For Each cell In FLrange
If cell.Value = 0 Then cell.FormulaR1C1 = "=SUMIFS(raw!C4,raw!C1,R[0]C1,raw!C3,R2C[0])"
Next cell
End Sub
Now, before executing the formula in above code, i would like to add another function on this. I need to copy the current cell value to another sheet on exactly same cell. ie, value from sheet1 cell C3has to be pasted sheet2 cell C3
现在，在执行上面代码中的公式之前，我想为此添加另一个函数。我需要将当前单元格值复制到完全相同的单元格上的另一个工作表。即，来自 sheet1 单元格的值C3必须粘贴到 sheet2 单元格C3
I have tried with
我试过
Sub Macro1()
Dim FLrange As Range
Set FLrange = Range("C3:E3")
For Each cell In FLrange
Selection.Copy
Sheets("sheet2").Paste
If cell.Value = 0 Then cell.FormulaR1C1 = "=SUMIFS(raw!C4,raw!C1,R[0]C1,raw!C3,R2C[0])"
Next cell
End Sub
But this one not pasting the value corresponding cell in sheet2, but see it is pasting formula to random cell.
但是这个不是粘贴sheet2中对应单元格的值，而是看到它是将公式粘贴到随机单元格中。
How can I paste the value from each cell in range in current sheet (sheet1), to corresponding cell in another sheet shet2
如何将当前工作表（sheet1）中范围内每个单元格的值粘贴到另一个工作表中的相应单元格 shet2
回答by Ben Black
First off, you're using Selectionin your code, but you're never changing your ActiveCell. Regardless, using ActiveCellis bad practice, you should always use object references to copy and not your selection unless it's completely necessary.
首先，你Selection在你的代码中使用，但你永远不会改变你的ActiveCell. 无论如何，使用ActiveCell是不好的做法，除非完全有必要，否则您应该始终使用对象引用来复制而不是您的选择。
Secondly you never give it an actual range to paste to, you just give it the sheet, so I'm surprised this isn't throwing an error of some sort when you try to run it.
其次，你从来没有给它一个实际的粘贴范围，你只是给它工作表，所以我很惊讶当你尝试运行它时这不会抛出某种错误。
What my code does it sets the range you want to iterate on (to use Meyou need to place this in the Sheet Module of Sheet1, otherwise you need to explicitly say it's on Sheet1), iterates over it and then copies it to the same Col/Row index on a second explicitly defined sheet.
我的代码做了什么它设置了你想要迭代的范围（使用Me你需要把它放在 Sheet1 的 Sheet Module 中，否则你需要明确地说它在 Sheet1 上），迭代它然后将它复制到同一个 Col / 行索引在第二个明确定义的工作表上。
When it comes to best practices you should always explicitly define things, especially when you're talking about VBA.
当谈到最佳实践时，您应该始终明确定义事物，尤其是在谈论 VBA 时。
Option Explicit
Sub Test()
Dim rng As Range
Dim c As Range
Dim dest As Worksheet
Set rng = Me.Range("A1:A10")
Set dest = ThisWorkbook.Worksheets("Sheet2") '' rename this to whatever sheet you want this to copy to
For Each c In rng
c.Copy dest.Cells(c.Row, c.Column)
If c.Value = 0 Then
c.FormulaR1C1 = "=SUMIFS(raw!C4,raw!C1,R[0]C1,raw!C3,R2C[0])"
End If
Next c
End Sub
