Macro works when stepping through, but when running, it seems to skip steps

Question

I am using the below macro:

'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

Basically, it uses the active cell, pastes it in the search field, and runs a macro that pulls data based on that criteria.

When stepping through, it copies and pastes the active cell and then the search works fine.

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...

I have tried adding pauses and doevents etc, but I assumed doevents was for odbc connections.

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:

'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

And this works fine.

Any ideas? Probably incredibly simple, as my vba is not brilliant.

Thanks,

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." & vbNewLine & vbNewLine & "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" & vbNewLine & vbNewLine & "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

Answer 1

It's a advisable not to use Select and Activate methods where possible, pass the value from selected cell to your desired cell like so:

Range("L5").Value = ActiveCell.Value
'Call macro to run the agency details search
Call AgencyDetails

As Vityata mentioned, it is better to fully qualify your ranges such as:

Sheet1.Range("L5").Value or even Sheets("Sheet1").Range("L5").Value, this way your code will not assume ActiveSheet and will take values from the defined ranges.

UPDATE

Sub AgencyDetails()

    Dim BlankCheckAgency As Range
    Set BlankCheckAgency = Sheets("Agency Search Data").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
    ActiveSheet.Range("G9,L9,G12,I12,G15,I15,G18,L18,Q9,Q12,Q15").ClearContents
    LastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "G").End(xlUp).Row
    Range("G28:G" & LastRow).ClearContents
    Range("I28:I" & LastRow).ClearContents
    Range("K28:K" & LastRow).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
    If IsEmpty(BlankCheckAgency.Offset(1)) = 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." & vbNewLine & vbNewLine & "If you think this is wrong, please contact OSD"
    GoTo Finish

Data:
    'Agency Name
    Range("AgencyDetails[Agency Name]").Copy
    Sheets("Agency Search").Range("G9").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False