title: Sorting a table using macro
body: <p>hoping this newbie here could get some assistance with sorting using macro please.</p>
  
  <p>I have a table ranging D:R to be sorted.</p>
  
  <p>Firstly, the whole table needs to be sorted in ascending order according to column Q.</p>
  
  <p>Then the same table get sorted in ascending order according to column P.</p>
  
  <p>Lastly the whole table needs to bring yellow highlighted rows per column D to the top.</p>
  
  <p>My current formula is recorded using the in-built recorder and it doesn't work.</p>
  
  <p>Would anyone provide any suggestions? Many thanks in advance!</p>
  
  <pre><code>'Sorting for easier read
      'Check for filter, turn on if none exists
      If Not ActiveSheet.AutoFilterMode Then
          ActiveSheet.Range("D1:R1").AutoFilter
      End If
      'Sort in order of column Q
      ActiveWorkbook.Worksheets(4).AutoFilter.Sort.SortFields.Clear
      ActiveWorkbook.Worksheets(4).AutoFilter.Sort.SortFields.Add Key:=Range( _
          "Q1:Q10000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
          xlSortNormal
      With ActiveWorkbook.Worksheets(4).AutoFilter.Sort
          .Header = xlYes
          .MatchCase = False
          .Orientation = xlTopToBottom
          .SortMethod = xlPinYin
      End With
      'Sort in order of column P
      ActiveWorkbook.Worksheets(4).AutoFilter.Sort.SortFields.Clear
      ActiveWorkbook.Worksheets(4).AutoFilter.Sort.SortFields.Add Key:=Range( _
          "P1:P10000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
          xlSortNormal
      With ActiveWorkbook.Worksheets(4).AutoFilter.Sort
          .Header = xlYes
          .MatchCase = False
          .Orientation = xlTopToBottom
          .SortMethod = xlPinYin
      End With
      'Bring highlighted row to the top
      ActiveWorkbook.Worksheets(4).AutoFilter.Sort.SortFields.Clear
      ActiveWorkbook.Worksheets(4).AutoFilter.Sort.SortFields.Add(Range("D:D" _
          ), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255 _
          , 255, 0)
      With ActiveWorkbook.Worksheets(4).AutoFilter.Sort
          .Header = xlYes
          .MatchCase = False
          .Orientation = xlTopToBottom
          .SortMethod = xlPinYin
      End With
  </code></pre>