vba VBA将单元格值从一张纸复制到另一张纸
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24808847/
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
VBA copying cell value from one sheet to another
提问by user3849959
does anyone has a clue how I can solve the following. I have a table in sheet 1 which I want to copy to sheet 2. However, copying will only take place for those cells in sheet1 for which column 3 row 11 to 32 are not empty. If say C11 is not empty, C11 will be copied together E11,F11,H11,I11 and J11. Then the code checks if C12 is filled and does the same and so on.
有没有人知道我如何解决以下问题。我在工作表 1 中有一个表格，我想将其复制到工作表 2。但是，只会对工作表 1 中第 3 列第 11 至 32 行不为空的那些单元格进行复制。如果说 C11 不为空，则 C11 将一起复制 E11、F11、H11、I11 和 J11。然后代码检查 C12 是否已填充并执行相同的操作，依此类推。
The copied cells are placed in sheet2 starting row and column 15. I have the following code which works very well. However it copies the formulas which obviously make no sense in sheet2, hence, values are nonsense:
复制的单元格放置在 sheet2 的第 15 行和第 15 列中。我有以下代码效果很好。然而，它复制了在 sheet2 中显然没有意义的公式，因此，值是无意义的：
Private Sub CommandButton1_Click()
Dim i As Integer
Dim a As Integer
a = 15
For i = 11 To 32
If Worksheets(1).Cells(i, 3) <> "" Then
Worksheets(1).Cells(i, 3).Copy Worksheets(2).Cells(a, 15)
Worksheets(1).Cells(i, 5).Copy Worksheets(2).Cells(a, 17)
Worksheets(1).Cells(i, 6).Copy Worksheets(2).Cells(a, 18)
Worksheets(1).Cells(i, 7).Copy Worksheets(2).Cells(a, 19)
Worksheets(1).Cells(i, 8).Copy Worksheets(2).Cells(a, 20)
Worksheets(1).Cells(i, 9).Copy Worksheets(2).Cells(a, 21)
a = a + 1
End If
Next i
How can I adjust the code in order to copy only the values?
如何调整代码以仅复制值？
Thank you very much for any support.
非常感谢您的支持。
回答by Tim Williams
Dim i As Integer
Dim rw As Range, rwD as Range
Set rwD = Worksheets(2).Rows(15)
For i = 11 To 32
Set rw = Worksheets(1).Rows(i)
If rw.Cells(3) <> "" Then
rwD.Cells(15).Value = rw.Cells(3).Value
rwD.Cells(17).Value = rw.Cells(5).Value
rwD.Cells(18).Value = rw.Cells(6).Value
rwD.Cells(19).Value = rw.Cells(7).Value
rwD.Cells(20).Value = rw.Cells(8).Value
rwD.Cells(21).Value = rw.Cells(9).Value
Set rwD = rwD.Offset(1, 0)
End If
Next i
回答by Pegasaurus
So, the way to do this with minimal changes to your code would be:
因此，在对代码进行最少更改的情况下执行此操作的方法是：
Private Sub CommandButton1_Click()
Dim i As Integer
Dim a As Integer
a = 15
For i = 11 To 32
If Worksheets(1).Cells(i, 3) <> "" Then
Worksheets(2).Cells(a, 15) = Worksheets(1).Cells(i, 3).Value
Worksheets(2).Cells(a, 17) = Worksheets(1).Cells(i, 5).Value
Worksheets(2).Cells(a, 18) = Worksheets(1).Cells(i, 6).Value
Worksheets(2).Cells(a, 19) = Worksheets(1).Cells(i, 7).Value
Worksheets(2).Cells(a, 20) = Worksheets(1).Cells(i, 8).Value
Worksheets(2).Cells(a, 21) = Worksheets(1).Cells(i, 9).Value
a = a + 1
End If
Next i
