Copy/Paste/Sort Multiple Columns-CodePudding

Having issues copying/pasting data in script below. Plan to copy raw data from Columns T,C,R to Columns X,Y,Z then sort Column X descending. Started using Index/Match but easier to copy/paste/sort. Will have to copy/paste/sort every millisecond due to Worksheet_Calc event.

Private Sub Worksheet_Calculate()

Dim total_data As Range
Dim specific_column As Range

If Worksheets("Dashboard").ToggleButton1.Value = True Then
   On Error GoTo SafeExit
   Application.EnableEvents = False
   Application.ScreenUpdating = False

Set sht1 = ThisWorkbook.Sheets("Log")
Set cpyRng = sht1.Range("T3:T60000,C3:C60000,R3:R60000")
Set pstRng = sht1.Range("X3:X60000,Y3:Y60000,Z3:Z60000")

            cpyRng.Copy
            pstRng.PasteSpecial xlPasteValues
            Application.CutCopyMode = False ' Remove the copy area marker
            
Set total_data = Worksheets("Log").Range("X:Z")
Set specific_column = Worksheets("Log").Range("X:X")

total_data.Sort Key1:=specific_column, Order1:=xlDescending, Header:=xlYes
Worksheets("Log").Cells(1, 1).Select

End If

SafeExit:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

CodePudding user response：

You can use a formula in X3 (if you use Excel 365) - it will spill down all three columns in the new order and sort them

=SORT(CHOOSE({1,2,3},$T$3:$T$60000,$C$3:$C$60000,$R$3:$R$60000),1,1)

Or you use this formula in VBA and then switch the output to values - sth. like:

sht1.Range("X3").Formula2 = "=SORT(CHOOSE({1,2,3},$T$3:$T$60000,$C$3:$C$60000,$R$3:$R$60000),1,1)"
sht1.Range("X3:Z60000").value = sht1.Range("X3:Z60000").value