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

Are you alt-tabbing during execution / switching to another Excel sheet or workbook? ActiveCell can be a little volatile since it depends on the ActiveSheet and Workbook. — dadler, Jan 31 '18 at 09:24

Xabier · Accepted Answer · 2018-01-31T11:32:41.010

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

Thanks, so using this code, and adding the sheet reference before got it working. 'Sheets("Agency Search").Range("L5").Value = ActiveCell.Value' — EBarton, Jan 31 '18 at 09:55
Ah, replied too fast I am afraid. Seems when running this macro it does the same. This works stepping through, but not when the macro is run. Same as before. — EBarton, Jan 31 '18 at 10:34
@EBarton, then the issue might be with the Sub you are calling,.. Could you share your code, so that we can investigate the issue? — Xabier, Jan 31 '18 at 10:53
@EBarton You should really consider re-writing your code to omit any select or activate methods, as these will not only slow your code down, but as you haven't qualified your ranges, it might be picking up the wrong data when you run your code. — Xabier, Jan 31 '18 at 11:19
Yeah, I am very new to vba so this is all a learning curve to me. Thanks for your input, I will give it a go — EBarton, Jan 31 '18 at 11:22
@EBarton I've updated my answer to include a small sample of how you could re-write without using the Select, hope this points you in the right direction. — Xabier, Jan 31 '18 at 11:33
Wow, thanks @Xabier. Looks a lot tidier than mine, and I'd presume proper practice too! — EBarton, Jan 31 '18 at 13:23

Vityata · Answer 2 · 2018-01-31T10:51:31.300

The problem with the both codes is that you are not referring to the correct worksheet, but you are assuming the activesheet.

Make sure that you refer it and avoid working with ActiveCell:

Sub TestMe()

    With Worksheets("SomeDetails")
        .Range("A1").Copy
        .Range("L5").PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False
    End With

    Call AgencyDetails

End Sub

How to avoid using Select in Excel VBA

Whenever you are copying and pasting in Excel, it is a good practice to use Application.CutCopyMode = False.

It makes sure that the pasted range gets unselected, it is the same as pressing Esc.

score 1 · Answer 3 · answered Jan 31 '18 at 10:02

One consideration might be the Application.Calculation mode - if this is xlCalculationManual or xlCalculationSemiautomatic then Excel may not register that $L$5 has been updated when it calls the macro.

You can force recalculation on everything (with Application.Calculate), just the ActiveSheet (ActiveSheet.Calculate) or the specific range (Range("L5").Calculate or Cells(5,12).Calculate)

In big/complicated Macros, setting the Calculation Mode to manual and explicitly deciding when to calculate can save a lot of time, in the same way that setting Application.ScreenUpdating = False to does. Just remember to reset them both afterwards! (Very long-running macros probably also need DoEvents somewhere to let Windows know that Excel has not crashed!)

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

3 Answers3