Hi below I have a working but non-elegant macro by any means. I have a feeling the speed issues are related to having the ranges set all the way to the end of the workbook as opposed to the last row of data. Any improvements would be greatly appreciated. I have highlighted what I believe to be problematic areas.

Thanks

Sub EquityOrder()
'
' EqOrder Macro
'

'
Workbooks.Open Filename:= _
"K:\Retail Prodman\FlightDesk\Statistics\Source Data\OMS_SIT_EQOrders_" & _
Application.WorksheetFunction.Text(Date - 1, "yyyymmdd") & ".csv"
Dim Firstrow As Long
    Dim Lastrow As Long
    Dim lrow As Long
    Dim CalcMode As Long
    Dim ViewMode As Long

    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With
Application.ScreenUpdating = False
    'We use the ActiveSheet but you can replace this with
    'Sheets("MySheet")if you want
    With ActiveSheet

        'We select the sheet so we can change the window view
        .Select

        'If you are in Page Break Preview Or Page Layout view go
        'back to normal view, we do this for speed
        ViewMode = ActiveWindow.View
        ActiveWindow.View = xlNormalView

        'Turn off Page Breaks, we do this for speed
        .DisplayPageBreaks = False

        'Set the first and last row to loop through
        Firstrow = .UsedRange.Cells(1).Row
        Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

        'We loop from Lastrow to Firstrow (bottom to top)
        For lrow = Lastrow To Firstrow Step -1

            'We check the values in the A column in this example
            With .Cells(lrow, "AQ")

                If Not IsError(.Value) Then

                    If .Value <> "ATP" Then .EntireRow.Delete

                End If
            End With
            Next lrow
    End With

      'Insert new top row'
    Application.ScreenUpdating = False
Rows("1:1").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    'remove all duplicate values in column C'
      Columns("C:C").Select
    ActiveSheet.Range("$A$2:$BM$1048575").RemoveDuplicates Columns:=3, Header:= _
        xlYes
        'run a countif function on column counting any value larger then 0'
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(R[1]C:R[1048575]C,"">0"")"
    'Copy and paste special value of the countif function'
    Range("C1").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Columns("AK:AK").Select
    Application.CutCopyMode = False
    'filter for all values that do not contain the word "fill" and delete those rows'
    Selection.AutoFilter
    ActiveSheet.Range("$AK$1:$AK$1048575").AutoFilter Field:=1, Criteria1:= _
        "<>*fill*", Operator:=xlAnd
    Rows("3:3").Select
    Range("AI3").Activate
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    Selection.AutoFilter
    'remove the filter and run a counta function on column AK'
    Range("AK1").Select
    ActiveCell.FormulaR1C1 = "=COUNTA(R[2]C:R[1048575]C)"
    Range("AK2").Select
    
           Range("AM1").Select
  ActiveCell.FormulaR1C1 = "=SUM(R[2]C[-24]:R[1048575]C[-24])"
    
        ActiveWindow.WindowState = xlNormal
    ActiveWindow.WindowState = xlNormal
    'copy and paste the countif and counta figures into a secondary spreadsheet in the last row'
    Range("C1").Select
    Range("C1,AK1,AM1").Select
    Selection.Copy
    ActiveWindow.WindowState = xlNormal

    Windows("Standard Daily Stats.xlsm").Activate
    ActiveWindow.WindowState = xlNormal
    ActiveWindow.WindowState = xlNormal
    With Sheet1.Range("H" & Rows.Count).End(xlUp).Offset(0)
    .PasteSpecial Paste:=xlPasteValues
     For Each Wb In Application.Workbooks
     Select Case Wb.Name
             Case ThisWorkbook.Name
                 ' do nothing
             Case Else
                   Wb.Close False
                 
     End Select
Next Wb
    End With
End Sub