locked
Custom sorting works within Excel, but recorded macro fails RRS feed

  • Question

  • Upon trying the custom sort function, I stumbled upon the following problem:
    In my sheet, I have an autofilter on a range of columns. When I try to sort the rows by a custom sort order, this generally works (Select the autofilter drop down -> Sort by color -> Custom Sort). However, when I am using the macro recorder and record the custom sort macro, the macro does not seem to have the desired effect: An active sorting on the desired column is indicated by the visible drop-down button, but the order of the rows does not change. Is there something specific I have to take into account when recording custom sort macros/writing vbas to perform a custom sort on a given column?

    The code generated is: (where "G11" contains the header of the data I want to sort on. The Autofilter Dropdown Button is also placed in G11)

    Sub Makro2()
    '
    ' Makro2 Makro
    '
    
    '
        ActiveWorkbook.Worksheets("Planing").AutoFilter.Sort.SortFields. _
            Clear
        ActiveWorkbook.Worksheets("Planing").AutoFilter.Sort.SortFields. _
            Add Key:=Range("G12:G100"), SortOn:=xlSortOnValues, Order:=xlAscending, _
            CustomOrder:= _
            "Prio1,Prio2,Prio3,Prio4,Prio5,Prio6" _
            , DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("DSS Vorhabenplanung").AutoFilter.Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End Sub
    Thanks in advance for any suggestions.

    Sunday, March 26, 2017 11:35 PM

All replies

  • Hi Vincent,

    Thanks for visiting our forum.

    Then this forum mainly focus on general questions about Excel client, since your issue is related to macros, I'll move your thread to the following dedicated MSDN forum for Excel for better response:

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Best regards,
    Yuki Sun


    Please remember to mark the replies as answers if they helped.

    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Monday, March 27, 2017 2:52 AM
  • Hello Vincent,

    Have you edit the macro? Would you get any error when you run the macro above?

    The code you shared creates custom sort for Worksheets("Planing"), but applies the sort to Worksheets("DSS Vorhabenplanung"). I think we need to keep worksheets consistent.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, March 27, 2017 5:03 AM
  • Hello Celeste,

    Sorry for this mistake. I only changed the name of the worksheet for the post in order to not confuse anybody with german names - In the original created macro, the worksheet names are consistent. Besides that, I did not make any changes to the macro, so the posted macro is exactly the macro that was generated by the macro recorder.

    However, I do not get any error when running the macro - it just does not change the order of the rows as opposed to selecting the custom sort option manually ( of course I then sorted by another column before trying out the macro to note the differences). The Autofilter drop-down indicates that a sorting is active, but it does not seem to have any effect.

    Regards,

    Vincent

    Tuesday, March 28, 2017 12:52 AM
  • Hello,

    Is there other level in your sort list?

    I suggest you create a new workbook or sheet to test the macro with one column data.

    If it is possible, please share a sample file here.

    Regards,

    Celeste 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, March 28, 2017 3:23 AM