excel vba：是否将单元格值从一个工作簿复制到另一个工作簿？

Question

当在列BA中单击文本“发送调查”时，我正在尝试从工作表“供应商跟踪”上的活动行复制单元格b中的值，并将其粘贴到单元格中。A2在我的另一个工作簿“供应商调查”上的“Data”表。

由于某种原因，我没有收到任何错误，但是没有任何内容被粘贴到我的其他工作簿的单元格A2中。

有人能告诉我哪里出了问题吗。提前感谢

If Target.Column = Range("BA1").Column And Range("BA" & ActiveCell.Row).Value = "Send Survey" Then
Application.ScreenUpdating = False
Dim wb As Workbook
Dim ws1112 As Worksheet
Dim ws2221 As Worksheet
Set ws1112 = Sheets("SupplierTracking")
Set wb = Workbooks.Open("\\{server address}\assets\Supplier Survey.xls")
Set ws2221 = wb.Sheets("Data")
ws2221.Range("A2").Value = ws1112.Range("B" & ActiveCell.Row).Value
'Optional if you want to close the workbook afterwards
wb.Close SaveChanges:=True
Application.ScreenUpdating = True
End If

Answer 1

我设法解决了问题的答案，我需要保存activeCell。行作为字符串变量，如下所示

If Target.Column = Range("BA1").Column And Range("BA" & ActiveCell.Row).Value = "Send Survey" Then

Dim wb As Workbook
Dim ws1112 As Worksheet
Dim ws2221 As Worksheet
Dim s As String
Dim r As String
Set ws1112 = Sheets("SupplierTracking")
s = ws1112.Range("B" & ActiveCell.Row).Value
r = ws1112.Range("C" & ActiveCell.Row).Value
Set wb = Workbooks.Open("\\UKSH000-File06\Purchasing\New_Supplier_Set_Ups_&_Audits\assets\Supplier Survey.xls")
Set ws2221 = wb.Sheets("Data")

ws2221.Range("A2").Value = s
ws2221.Range("B2").Value = r
'Optional if you want to close the workbook afterwards
wb.Close SaveChanges:=True

End If