title: Macro works when stepping through, but when running, it seems to skip steps
body: <p>I am using the below macro:</p>
  
  <pre><code>'Copy active agency ID and paste into search on Worker Details
      ActiveCell.Select
      Selection.Copy
      Range("L5").Select
       Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
          :=False, Transpose:=False
  
      'Call macro to run the agency details search
      Call AgencyDetails
  </code></pre>
  
  <p>Basically, it uses the active cell, pastes it in the search field, and runs a macro that pulls data based on that criteria.</p>
  
  <p>When stepping through, it copies and pastes the active cell and then the search works fine.</p>
  
  <p>When running the macro, it seems to not copy and paste the active cell to the search field. Or that the called macro runs too early...</p>
  
  <p>I have tried adding pauses and <code>doevents</code> etc, but I assumed <code>doevents</code> was for odbc connections.</p>
  
  <p>To further complicate things. I have another macro that is almost identical, where it copies text into a search field and then returns data based on that criteria:</p>
  
  <pre><code>'Copy active worker ID and paste into search on Worker Details
      ActiveCell.Select
      Selection.Copy
      Sheets("Worker Details").Select
      Range("E5").Select
       Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
          :=False, Transpose:=False
  
      'Call macro to run the worker details search
      Call WorkerDetails
  </code></pre>
  
  <p>And this works fine.</p>
  
  <p>Any ideas? Probably incredibly simple, as my vba is not brilliant. </p>
  
  <p>Thanks,</p>
  
  <pre><code>Sub AgencyDetails()
  
      Dim BlankCheckAgency As Range
      Set BlankCheckAgency = Range("AgencyDetails[[#Headers],[Agency ID]]")
      Dim BlankCheckWorkers As Range
      Set BlankCheckWorkers = Range("AgencyWorkers[[#Headers],[auto_number]]")
  
  
      Application.ScreenUpdating = False
      ActiveWorkbook.Sheets("Agency Search Data").Visible = xlSheetVisible
  
      'Clear Data
      Range("G9,L9,G12,I12,G15,I15,G18,L18,Q9,Q12,Q15").Select
      Selection.ClearContents
      Range("G28").Select
      Range(Selection, Selection.End(xlDown)).Select
      Selection.ClearContents
      Range("I28").Select
      Range(Selection, Selection.End(xlDown)).Select
      Selection.ClearContents
      Range("K28").Select
      Range(Selection, Selection.End(xlDown)).Select
      Selection.ClearContents
      Range("L5").Select
  
      'Refresh Data
      ActiveWorkbook.Connections("AgencyDetails").Refresh
      ActiveWorkbook.Connections("AgencyBDM").Refresh
      ActiveWorkbook.Connections("AgencyAM").Refresh
      ActiveWorkbook.Connections("AgencySalesRep").Refresh
      ActiveWorkbook.Connections("AgencyWorkers").Refresh
  
      'DataCheck for agency details
      Sheets("Agency Search Data").Select
      BlankCheckAgency.Select
      ActiveCell.Offset(1).Select
      If IsEmpty(ActiveCell) = False Then
      GoTo Data
      Else
      GoTo NoData
      End If
  
  NoData:
      'Go back to search window and display message
      Sheets("Agency Search").Select
      ActiveWorkbook.Sheets("Agency Search Data").Visible = xlSheetVeryHidden
      Application.ScreenUpdating = True
      msgBox "No agency on record matched the ID you have searched for." &amp; vbNewLine &amp; vbNewLine &amp; "If you think this is wrong, please contact OSD"
      GoTo Finish
  
  Data:
      'Agency Name
      Range("AgencyDetails[Agency Name]").Select
      Selection.Copy
      Sheets("Agency Search").Select
      Range("G9").Select
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
          :=False, Transpose:=False
  
      'Unmerge Address
      Range("L9").Select
      With Selection
          .HorizontalAlignment = xlGeneral
          .VerticalAlignment = xlBottom
          .WrapText = True
          .Orientation = 0
          .AddIndent = False
          .IndentLevel = 0
          .ShrinkToFit = False
          .ReadingOrder = xlContext
          .MergeCells = True
      End With
      Selection.UnMerge
  
      'Full Address
      Sheets("Agency Search Data").Select
      Range("AgencyDetails[Full Address]").Select
      Selection.Copy
      Sheets("Agency Search").Select
      Range("L9").Select
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
          :=False, Transpose:=False
  
      'Merge Address
      Range("L9:O15").Select
      With Selection
          .HorizontalAlignment = xlLeft
          .VerticalAlignment = xlTop
          .WrapText = True
          .Orientation = 0
          .AddIndent = False
          .IndentLevel = 0
          .ShrinkToFit = False
          .ReadingOrder = xlContext
          .MergeCells = False
      End With
      Selection.Merge
  
      'Agency Status
      Sheets("Agency Search Data").Select
      Range("AgencyDetails[Agency Status 2]").Select
      Selection.Copy
      Sheets("Agency Search").Select
      Range("G15").Select
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
          :=False, Transpose:=False
  
      'Agency Brand
      Sheets("Agency Search Data").Select
      Range("AgencyDetails[Brand]").Select
      Selection.Copy
      Sheets("Agency Search").Select
      Range("I15").Select
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
          :=False, Transpose:=False
  
      'Agency Reg
      Sheets("Agency Search Data").Select
      Range("AgencyDetails[Agency Reg]").Select
      Selection.Copy
      Sheets("Agency Search").Select
      Range("G12").Select
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
          :=False, Transpose:=False
  
      'VAT Reg
      Sheets("Agency Search Data").Select
      Range("AgencyDetails[Vat Reg]").Select
      Selection.Copy
      Sheets("Agency Search").Select
      Range("I12").Select
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
          :=False, Transpose:=False
  
      'Unmerge GNotes
      Range("G18").Select
      With Selection
          .HorizontalAlignment = xlGeneral
          .VerticalAlignment = xlBottom
          .WrapText = True
          .Orientation = 0
          .AddIndent = False
          .IndentLevel = 0
          .ShrinkToFit = False
          .ReadingOrder = xlContext
          .MergeCells = True
      End With
      Selection.UnMerge
  
      'General Notes
      Sheets("Agency Search Data").Select
      Range("AgencyDetails[General Notes]").Select
      Selection.Copy
      Sheets("Agency Search").Select
      Range("G18").Select
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
          :=False, Transpose:=False
  
      'Merge GNotes
      Range("G18:J24").Select
      With Selection
          .HorizontalAlignment = xlLeft
          .VerticalAlignment = xlTop
          .WrapText = True
          .Orientation = 0
          .AddIndent = False
          .IndentLevel = 0
          .ShrinkToFit = False
          .ReadingOrder = xlContext
          .MergeCells = False
      End With
      Selection.Merge
  
      'Unmerge SNotes
      Range("L18").Select
      With Selection
          .HorizontalAlignment = xlGeneral
          .VerticalAlignment = xlBottom
          .WrapText = True
          .Orientation = 0
          .AddIndent = False
          .IndentLevel = 0
          .ShrinkToFit = False
          .ReadingOrder = xlContext
          .MergeCells = True
      End With
      Selection.UnMerge
  
      'Sales Notes
      Sheets("Agency Search Data").Select
      Range("AgencyDetails[Sales Notes]").Select
      Selection.Copy
      Sheets("Agency Search").Select
      Range("L18").Select
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
          :=False, Transpose:=False
  
       'Merge SNotes
      Range("L18:O24").Select
      With Selection
          .HorizontalAlignment = xlLeft
          .VerticalAlignment = xlTop
          .WrapText = True
          .Orientation = 0
          .AddIndent = False
          .IndentLevel = 0
          .ShrinkToFit = False
          .ReadingOrder = xlContext
          .MergeCells = False
      End With
      Selection.Merge
  
      'BDM
      Sheets("Agency Search Data").Select
      Range("AgencyBDM[Full Name]").Select
      Selection.Copy
      Sheets("Agency Search").Select
      Range("Q9").Select
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
          :=False, Transpose:=False
  
      'Sales Rep
      Sheets("Agency Search Data").Select
      Range("AgencySalesRep[Full Name]").Select
      Selection.Copy
      Sheets("Agency Search").Select
      Range("Q12").Select
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
          :=False, Transpose:=False
  
      'AM
      Sheets("Agency Search Data").Select
      Range("AgencyAM[Full Name]").Select
      Selection.Copy
      Sheets("Agency Search").Select
      Range("Q15").Select
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
          :=False, Transpose:=False
  
      'DataCheck for workers
      Sheets("Agency Search Data").Select
      BlankCheckWorkers.Select
      ActiveCell.Offset(1).Select
      If IsEmpty(ActiveCell) = False Then
      GoTo Data2
      Else
      GoTo NoData2
      End If
  
  NoData2:
      Rows("1:1000").Select
      Selection.RowHeight = 15
      Sheets("Agency Search").Select
      ActiveWorkbook.Sheets("Agency Search Data").Visible = xlSheetVeryHidden
      Application.ScreenUpdating = True
      msgBox "The agency details have been pulled but there are no workers associated with the Agency" &amp; vbNewLine &amp; vbNewLine &amp; "If you think this to not be true, please contact OSD"
      GoTo Finish
  
  Data2:
      'Pull worker IDs
      Sheets("Agency Search Data").Select
      Range("AgencyWorkers[auto_number]").Select
      Selection.Copy
      Sheets("Agency Search").Select
      Range("G28").Select
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
          :=False, Transpose:=False
  
      'Pull worker first name
      Sheets("Agency Search Data").Select
      Range("AgencyWorkers[first_name]").Select
      Selection.Copy
      Sheets("Agency Search").Select
      Range("I28").Select
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
          :=False, Transpose:=False
  
      'Pull worker last name
      Sheets("Agency Search Data").Select
      Range("AgencyWorkers[last_name]").Select
      Selection.Copy
      Sheets("Agency Search").Select
      Range("K28").Select
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
          :=False, Transpose:=False
      Rows("1:1000").Select
      Selection.RowHeight = 15
      Range("L5").Select
  
  Finish:
      ActiveWorkbook.Sheets("Agency Search Data").Visible = xlSheetVeryHidden
      Application.ScreenUpdating = True
  End Sub
  </code></pre>