Resolving runtime errors in Excel VBA

Question

I am taking my first plunge into Excel VBA and have a lot to learn.

With this macro, I'm setting up a file that will allow me to copy/paste a specific range of data from a weekly report based on the individual's name. Essentially, I want to be able to have the individual click a button and have the data get pulled automatically. Here's what I've put together so far:

Sub 1 (individual defines their name):

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 (macro pulls data from report based on name entered in 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

I'm getting a runtime error 13 ("Type mismatch") on this line in Sub 2, and I have no idea why:

Set Name = Worksheets("Summary").Range("A1").Value

I have also gotten different errors at different points (including 91, 1004, and 9), which may or may not show up again if this one gets fixed.

UPDATE I resolved the error 13 on the previous line by removing the Set (at least I think I did), but now I'm getting an error 91 on:

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

Again, I'm brand new, so any assistance would be greatly appreciated.

Edit I'm using Excel 2011 for Mac, if that matters.

Answer 1

Because you're activating a non existing object.

It's better to check if the Cells.Find() returns you a Range (or Nothing instead) before trying activate it.

'Create a temporary Range
Dim temp_range As Range

'Notice that i removed the activation of the range at the end
Set temp_range = Cells.Find(What:=Name, After:=activecell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)

'before anything, test if it's nothing...
If Not temp_range Is Nothing Then
    ' if not the you can activate..
     temp_range.Activate
    ' and work on the result...
    Debug.Print temp_range.Value , temp_range.Address
End If