vba 使用VBA将单元格值复制到Excel中的另一个工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23277857/
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
Copying Cell Value to another sheet in Excel with VBA
提问by user3569697
I have the following code which successfully copies cells B, E and F to cells B, C, D in sheet 2 after I match a string in another cell. The problem is that it copies the cell and not only just the value inside it (I don't need borders, colour etc).
我有以下代码，在匹配另一个单元格中的字符串后，它成功地将单元格 B、E 和 F 复制到工作表 2 中的单元格 B、C、D。问题是它复制了单元格，而不仅仅是其中的值（我不需要边框、颜色等）。
Another issue I have is that while it will copy the data to the next free row in Column B, it won't look for the next free row according to column C and D.
我遇到的另一个问题是，虽然它会将数据复制到 B 列中的下一个空闲行，但它不会根据 C 列和 D 列查找下一个空闲行。
Private Sub Worksheet_Change(ByVal Target As Range)
Dim thisrow As Long
Dim lr As Long
If Target.Column = 7 Then
thisrow = Target.Row
If Target.Value = "FAULTY" Then
lr = Sheets("sheet2").Range("B" & Rows.Count).End(xlUp).Row + 1
Range("B" & ActiveCell.Row).Copy Sheets("sheet2").Range("B" & lr)
Range("D" & ActiveCell.Row).Copy Sheets("sheet2").Range("C" & lr)
Range("F" & ActiveCell.Row).Copy Sheets("sheet2").Range("D" & lr)
End If
End If
End Sub
回答by Rich
You can use the .Value operator instead. Also, just set a separate variable for the C/D Range for the next available cell.
您可以改用 .Value 运算符。此外，只需为下一个可用单元格的 C/D 范围设置一个单独的变量。
Private Sub Worksheet_Change(ByVal Target As Range)
Dim thisrow As Long
Dim lr As Long
If Target.Column = 7 Then
thisrow = Target.Row
If Target.Value = "FAULTY" Then
lr = Sheets("sheet2").Range("B" & Rows.Count).End(xlUp).Row + 1
mr = Sheets("sheet2").Range("C" & Rows.Count).End(xlUp).Row + 1
nr = Sheets("sheet2").Range("D" & Rows.Count).End(xlUp).Row + 1
Sheets("sheet2").Range("B" & lr).Value = Range("B" & ActiveCell.Row).Value
Sheets("sheet2").Range("C" & mr).Value = Range("D" & ActiveCell.Row).Value
Sheets("sheet2").Range("D" & nr).Value = Range("F" & ActiveCell.Row).Value
End If
End If
End Sub
