sort specific column and save worksheet with sorted column in column A

[rjmdc]

hi
using this code
1- how wouyld i be able to have new worksheet created named as the sorted column
2- sorted column gets either copied or moved to column A

can this be updatable each time it's run?

Sub sort()
Dim SortCol As Range
'  Input box to select the column to sort by

Set SortCol = Application.InputBox("Select the column to sort", "Sort-Box", 0, , , , , 8)
Range("ALLData").sort Key1:=SortCol, Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

 

[onlyadrafter]

Hello,

does this work as expected?

Sub sort()
    Dim SortCol As Range
'  Input box to select the column to sort by
    MY_SOURCE = ActiveSheet.Name
    Set SortCol = Application.InputBox("Select the column to sort", "Sort-Box", 0, , , , , 8)
    MY_COL = SortCol.Address
    If Len(MY_COL) > 5 Then MY_LEN = 5 Else MY_LEN = 4
    MY_COL = "Column " & Mid(MY_COL, 2, Len(MY_COL) - MY_LEN) & " Sort"
    Range("ALLData").sort Key1:=SortCol, Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
'see if sorted column sheet exists
    For MY_SHEETS = 1 To ActiveWorkbook.Sheets.Count
        If Sheets(MY_SHEETS).Name = MY_COL Then GoTo cont
    Next MY_SHEETS
    Worksheets.Add After:=Sheets(ActiveWorkbook.Sheets.Count)
    MY_DEST = ActiveSheet.Name
    Sheets(MY_DEST).Name = MY_COL
    Sheets(MY_SOURCE).Select
cont:
    SortCol.Copy Sheets(MY_COL).Range("A1")
End Sub