title: Compile error: searching for one specific data point, looping through whole workbook, copy/paste data
body: <p>Excel VBA beginner coming back for more. I am creating a macro that does the following two things:</p>
  
  <p>1) Searches through multiple worksheets in a single workbook for a specific piece of data (a name), variable A below<br>
  2) If that name appears, to copy a specific range of cells from the worksheet (variable X below) to the master file (variable B below)  </p>
  
  <pre><code>Sub Pull_X_Click()
  
  Dim A As Variant 'defines name
  Dim B As Workbook 'defines destination file
  Dim X As Workbook 'defines existing report file as source
  Dim Destination As Range 'defines destination for data pulled from report
  Dim ws As Worksheet
  Dim rng As Range
  
  A = Workbooks("B.xlsm").Worksheets("Summary").Range("A1").Value
  Set B = Workbooks("B.xlsm")
  Set X = Workbooks.Open("X.xlsm")
  Set Destination = Workbooks("B").Worksheets("Input").Range("B2:S2")
  
  'check if name is entered properly
  If A = "" Then
  MsgBox ("Your name is not visible; please start from the Reference tab.")
  Worksheets("Reference").Activate
  Exit Sub
  End If
  
  X.Activate
  
  
  For Each ws In X.Worksheets
    Set rng = ws.Range("A" &amp; ws.Rows.Count).End(xlUp)
    If InStr(1, rng, A) = 0 Then
  Else
      X.ActiveSheet.Range("$A$2:$DQ$11").AutoFilter Field:=1, Criteria1:=A
      Range("A7:CD7").Select
      Selection.Copy
      Destination.Activate
      Destination.PasteSpecial
  End If
  Next ws
  
  Application.ScreenUpdating = False
  
  End Sub
  </code></pre>
  
  <p><strong>UPDATE</strong>: I managed to resolve the previous compile error, and it seems that the code (should?) work. However, it gets to this step:</p>
  
  <blockquote>
    <p>X.Activate</p>
  </blockquote>
  
  <p>...and then nothing happens. There's no run-time errors or anything, but it doesn't seem to be searching through the file (variable X) or pulling any of the data based on the presence of variable A. Any thoughts?</p>