I am trying to run some reports to get summary information but it only works once and then does not sort properly. I am copying the visible cells from one worksheet which is filtered on date and pasting them to another worksheet. I then want to sort column B and subtotal at each change in catergory (B) and then hide the detail so I only get the totals.

This works fine the first time I run it but not again after. I tried clearing the contents of the destination sheet prior to running the macro but it still is not working. Seems to lose the sort. Here is my code>

Sub PurchasesCategory()

    Sheets("Purchases").Select
    Application.Run _
        "'adjusted whangateau shop model (version 1).xls'!MyFilterPurchases"
    Range("A4:G5001").Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    Sheets("PurchaseReports").Select
    Range("A4").Select
    ActiveSheet.Paste
       
       Range("B4").Select
    Application.CutCopyMode = False
    Range("A4:G5001").Sort Key1:=Range("B5"), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Range("A4:G5001").Select
    Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(6, 7), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    ActiveSheet.Outline.ShowLevels RowLevels:=2
End Sub