我正在第一次参加Excel VBA并有很多需要学习的东西 .
使用此宏,我正在设置一个文件,允许我根据个人姓名从每周报告中复制/粘贴特定范围的数据 . 从本质上讲,我希望能够让个人单击按钮并自动拉取数据 . 以下是我到目前为止所写的内容:
Sub 1(个人定义他们的名字):
Sub Button1_Click()
Dim Name As Variant
Name = InputBox("Enter your name as it appears on the report", "Enter Name")
Worksheets("Summary").Range("A1").Value = Name
Application.ScreenUpdating = True
ActiveWorkbook.Save
End Sub
Sub 2(宏根据在Sub 1中输入的名称从报告中提取数据):
Sub Report1_Click()
Dim Name As Variant
Set Name = Worksheets("Summary").Range("A1").Value
'check if Name is entered
If Name = "" Then
MsgBox ("Your name is not visible, please start from the Reference tab.")
Exit Sub
End If
Dim SourceFile As Workbook
Set SourceFile = Workbooks("filename.xlsm")
'check if source file is open
If SourceFile Is Nothing Then
'open source file
Set SourceFile = Workbooks.Open("C:\filename.xlsm")
SourceFile.Activate
Else
'make source file active
SourceFile.Activate
End If
Dim DestFile As Variant
Set DestFile = ActiveWorkbook.Worksheets("Input").Range("B2")
Dim ActiveCell As Variant
Set ActiveCell = Workbooks("filename.xlsm").Worksheets("Group").Range("A1")
Cells.Find(What:=Name, After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
ActiveSheet.Range("$A$2:$DQ$11").AutoFilter Field:=1, Criteria1:=Name
Range("A7:CD7").Select
Selection.Copy
DestFile.Activate
ActiveSheet.Paste
Application.ScreenUpdating = True
ActiveWorkbook.Save
End Sub
我在Sub 2的这一行上遇到运行时错误13(“类型不匹配”),我不明白为什么:
Set Name = Worksheets(“Summary”) . Range(“A1”) . Value
我在不同的点(包括91,1004和9)也得到了不同的错误,如果这个错误得到解决,可能会或可能不会再出现 .
UPDATE 我通过删除Set解决了上一行中的错误13(至少我认为我做了),但现在我收到错误91:
Cells.Find(What:= Name,After:= ActiveCell,LookIn:= xlFormulas ,LookAt:= xlPart,SearchOrder:= xlByRows,SearchDirection:= xlNext, MatchCase:= False).Activate
再说一次,我是全新的,所以任何帮助都会非常感激 .
Edit 我正在使用Excel 2011 for Mac,如果这很重要的话 .
1 回答
因为您正在激活一个不存在的对象 .
在尝试激活它之前,最好检查Cells.Find()是否返回Range(或Nothing) .