复制/粘贴/排序多列

Question

在下面的脚本中复制/粘贴数据问题。计划将原始数据从列T，C，R列为列x，y，z然后对列x降序。开始使用索引/匹配，但更易于复制/粘贴/排序。由于Worksheet_calc事件，将必须复制/粘贴/对每毫秒进行复制/排序。

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

Answer 1

您可以在X3中使用公式（如果使用Excel 365） - 它将以新顺序溢出所有三列并对其进行排序，

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

或者您在VBA中使用此公式，然后将输出切换到值-STH。喜欢：

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