vba - 将一行中的每个值复制到另一个值匹配的工作簿？

Question

我有两本工作簿。

工作簿 A

Supplier Name        Date

Supplier A         Jun-17
Supplier B         Jun-17
Supplier C         May-17

工作簿 B

Supplier Name
Supplier A
Supplier B

我正在尝试复制与 Jun-17 日期匹配的所有供应商名称。
Jun-17 写在单元格 B1 中名为 (assets) 的工作表上。

我收到一个错误

Object doesn't support this property or method.

在这条线上:

If ThisWorkbook.Worksheets(1).Format(Range("O" & j).Value, "mmm") = ThisWorkbook.Worksheets("assets").Range("b1").Value Then

我的代码:

Sub Monthly()


On Error Resume Next

UserForm1.Show
If Format(Range("O211").Value, "mmm") = Worksheets("assets").Range("b1").Value Then
MsgBox "Yes"
End If

Dim WB As Workbook
On Error Resume Next
Set WB = Workbooks("PAI Monthly Audits.xlsx")
On Error GoTo 0
If WB Is Nothing Then
    Set WB = Workbooks.Open("G:\QUALITY ASSURANCE\03_AUDITS\PAI\templates\PAI Monthly Audits.xlsx", UpdateLinks:=False)
End If



With ThisWorkbook.ActiveSheet
    LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
    j = 6

    For i = 2 To LastRow


        If ThisWorkbook.Worksheets(1).Format(Range("O" & j).Value, "mmm") = ThisWorkbook.Worksheets("assets").Range("b1").Value Then
                .Range("B" & i).Value = ThisWorkbook.Worksheets(1).Range("B" & j).Value
                j = j + 1
            End If
    Next i
End With



End Sub

请有人可以告诉我我要去哪里错了吗？

Answer 1

试试这样...

If Format(ThisWorkbook.Worksheets(1).Range("O" & j).Value, "mmm") = ThisWorkbook.Worksheets("assets").Range("b1").Value Then