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

rjmdc

Active Member
Joined
Apr 29, 2020
Messages
364
Office Version
  1. 365
Platform
  1. Windows
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?
VBA Code:
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
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

does this work as expected?

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,862
Messages
5,574,711
Members
412,613
Latest member
EFRATA
Top