CODEWITHSUNDEEP
vbaexcelsorting
se_ib
se_ib

Reputation: 13

Trying to sort worksheets in descending order based on cell values

I am trying to sort worksheets based on a cell value located within each sheet. The main area I am having difficulty is applying the sort to ONLY selected sheets. Below is what I have so far. Pretty new to VBA

Sub SortWksByCell()
Dim i As Integer
Dim j As Integer

  For i = 1 To ActiveWindow.SelectedSheets.Count
    For j = i + 1 To ActiveWindow.SelectedSheets.Count
        If UCase(Worksheets(i).Range("q1")) <= _
          UCase(Worksheets(j).Range("q1")) Then
            Worksheets(j).Move Before:=Worksheets(i)
        End If
    Next
  Next
End Sub

Upvotes: 1

Views: 96

Answers (1)

user3598756
user3598756

Reputation: 29421

whenever you Move a worksheet you loose the selection of worksheets, so you have to fix it before starting the sorting process

Option Explicit

 Sub SortWksByCell()
    Dim i As Integer
    Dim j As Integer
    Dim wss As Sheets

    Set wss = ActiveWindow.SelectedSheets
    For i = 1 To wss.Count
        For j = i + 1 To wss.Count
            If UCase(wss(i).Range("q1")) <= _
              UCase(wss(j).Range("q1")) Then
                wss(j).Move Before:=wss(i)
            End If
        Next
    Next
End Sub

Upvotes: 1

Related Questions