5/9/2017 - 11:41 AM

Table Sorter 2.0 Select a table and it sorts each column in descending order. Updated to accommodate conflicting sheet names (24-05-2017)

[Table Sorter 2.0] Select a table and it sorts each column in descending order. Updated to accommodate conflicting sheet names (24-05-2017)The output is on another sheet, with both the index/category and the values printed. #Excel

Sub FullTableSorter()

'On Error Resume Next

Dim rng, Headerx As Range
Dim nrow, ncol, i, j As Integer
'Dim oldsheet, newsheet As Worksheet
Dim orignam As String
Dim newnam As String

orignam = ActiveSheet.Name
Set rng = Application.InputBox("Please select source table area (include headers)", "Source table", Default:=Selection.Address, Type:=8)
Debug.Print "Original Worksheet Name: " & orignam

If SheetExists("Ranked_Full") Then
    Sheets.Add(after:=Sheets(Sheets.Count)).Name = "RFull" & Format(Date, "dd-mm-yy") & "@" & Format(Time, "hhmmss")
    newnam = "RFull" & Format(Date, "dd-mm-yy") & "@" & Format(Time, "hhmmss")
    Sheets.Add(after:=Sheets(Sheets.Count)).Name = "Ranked_Full"
    newnam = "Ranked_Full"
End If

'Set newsheet = ActiveWorkbook.Sheets("Ranked")

nrow = rng.Rows.Count
ncol = rng.Columns.Count

Set Headerx = Range(rng.Cells(1, 1), rng.Cells(1, ncol))

ActiveSheet.AutoFilterMode = False

'MsgBox Headerx.Address
'MsgBox Rng.Cells(1, 2).Address
'MsgBox Rng.Cells(nrow, 2).Address

For i = 2 To ncol
    j = (i - 1) * 2 'Column for output
    ActiveWorkbook.Worksheets(orignam).AutoFilter.Sort.SortFields.Add Key:=Range(rng.Cells(1, i), rng.Cells(nrow, i)), SortOn:=xlSortOnValues, Order:= _
        xlDescending, DataOption:=xlSortNormal
    With Worksheets(orignam).AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
    End With
    ActiveWorkbook.Worksheets(orignam).Range(rng.Cells(1, 1), rng.Cells(nrow, 1)).Copy
    ActiveWorkbook.Worksheets(newnam).Range(Cells(1, j), Cells(nrow, j)).PasteSpecial (xlPasteValues)
    ActiveWorkbook.Worksheets(orignam).Range(rng.Cells(1, i), rng.Cells(nrow, i)).Copy
    ActiveWorkbook.Worksheets(newnam).Range(Cells(1, j + 1), Cells(nrow, j + 1)).PasteSpecial (xlPasteValues)
    ActiveWorkbook.Worksheets(orignam).Range(rng.Cells(1, i), rng.Cells(1, i)).Copy
    ActiveWorkbook.Worksheets(newnam).Range(Cells(1, j), Cells(1, j)).PasteSpecial (xlPasteValues)
End Sub

Function SheetExists(sheetName As String, Optional Wb As Workbook) As Boolean
    If Wb Is Nothing Then Set Wb = ThisWorkbook
    On Error Resume Next
    SheetExists = (LCase(Wb.Sheets(sheetName).Name) = LCase(sheetName))
    On Error GoTo 0
Exit Function
End Function