Excel: Can't get last piece of code to run uless I run the SUB twice.


Good evening, The following code works great but ONLY if I run it twice...

Sub Colate()
    With Application
        .ScreenUpdating = False: .Calculation = xlCalculationManual: .DisplayAlerts = False
    End With
    With Sheets("Working Sheet")
        Range("D3:D503").Select
        Selection.Copy
        Range("F3").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Application.CutCopyMode = False
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=Range( _
            "F3:F503"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal
        With Sheets("Working Sheet").Sort
            .SetRange Range("F3:F503")
            .Header = xlGuess
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        With Sheets("Working Sheet")
            Range("H3:H3").Select
            Selection.Copy
            Range("H6").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
        End With
    End With
    With Application
        .DisplayAlerts = True: .Calculation = xlCalculationAutomatic: .ScreenUpdating = True
    End With
End Sub
...the piece of code that requires to be run twice is this piece...
        With Sheets("Working Sheet")
            Range("H3:H3").Select
            Selection.Copy
            Range("H6").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
        End With
...but I don't understand why? Thanks in advance.


This question generated 15 answers. To proceed to the answers, click here.

This thread is current as of April 26, 2015.


For more resources for Microsoft Excel