运行时错误'91':Excel 2011 for Mac VBA

我正在创建一个宏,将每周报告中的信息提取到主工作表中;我已经完成了几个步骤,但我的Cells.Find编码仍然遇到错误 . 我不确定为什么会这样,因为宏 Logger 编写相同的代码,只需用变量替换我的手动输入 .

附件是带有问题的子线 in bold .

Sub Pull_W01_Data_Click()

Dim Advisor As Variant 'defines advisor name from first subroutine
Dim Buddy As Workbook 'defines xxx as destination file
Dim x As Workbook 'defines existing report file as source
Dim Destination As Range 'defines destination range of data pulled from report file

Advisor = Worksheets("Advisor Summary").Range("A1").Value
Set Buddy = Workbooks("file1name.xlsm")
Set x = Workbooks.Open("file2name.xlsm")
Set Destination = Workbooks("file1name.xlsm").Worksheets("Input").Range("B2:S2")

'check if advisor name is entered
    If Advisor = "" Then
    MsgBox ("Your name is not visible; please start from the Reference tab.")
    Worksheets("Reference").Activate
    Exit Sub
End If

x.Activate
Cells.Find(What:=Advisor, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Activate

ActiveSheet.Range("$A$2:$DQ$11").AutoFilter Field:=1, Criteria1:=Advisor
Range("A7:CD7").Select
Selection.Copy
Destination.Activate
ActiveSheet.Paste

Application.ScreenUpdating = True

ActiveWorkbook.Save

End Sub

与运行时错误91对齐:

Cells.Find(What:= Advisor,After:= ActiveCell,LookIn:= xlFormulas,LookAt:= xlPart,SearchOrder:= xlByRows,SearchDirection:= xlNext,MatchCase:= False).Activate

有任何想法吗?我的语法是否关闭?我搞砸了变量的名字吗?

回答(0)