Question

excel VBA 一数据引用另一张表的数据应该怎么写

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row >= 2 And Target.Row <= 1000 And _
Cells(Target.Row, 8) < Date Then
Application.EnableEvents = False
Cells(Target.Row, 9) = Cells(Target.Row, 9).Value
Cells(Target.Row, 10) = Cells(Target.Row, 10).Value
Cells(Target.Row, 11) = Cells(Target.Row, 11).Value
Cells(Target.Row, 12) = Cells(Target.Row, 12).Value
Cells(Target.Row, 13) = Cells(Target.Row, 13).Value
Cells(Target.Row, 14) = Cells(Target.Row, 14).Value
Cells(Target.Row, 15) = Cells(Target.Row, 15).Value
Cells(Target.Row, 16) = Cells(Target.Row, 16).Value
Cells(Target.Row, 17) = Cells(Target.Row, 17).Value
Cells(Target.Row, 18) = Cells(Target.Row, 18).Value
Application.EnableEvents = True
End If
End Sub
上面的代码我只能实现直接在第8列（H列）上面修改日期从而实现把后几列的数据选择性粘帖成数值。而实际上第H列的数据我是引用的另一张表中的数据（路径为：[新建 Microsoft Office Excel 工作表.xlsx]Sheet1），用VBA代码如何实现在：[新建 Microsoft Office Excel 工作表.xlsx]Sheet1这张表中修改H列的数据，从而使：[新建 Microsoft Office Excel 工作表.xlsx]Sheet2这张表中的I-R这几列的数据选择粘帖成数值。上面的代码应该怎么修改呢

Answer 1

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row >= 2 And Target.Row <= 1000 And _
Cells(Target.Row, 8) < Date Then
Application.EnableEvents = False
For i = 9 To 18
Cells(Target.Row, i) = Sheet2.Cells(Target.Row, i).Value
Next
Application.EnableEvents = True
End If
End Sub

Answer 2

是两个工作簿还是工作表？
两个的程序不一样