如果我從另一個工作表中的按鈕執行 VBA 程式碼，請保持 VBA 程式碼正常運作，或用公式取代程式碼

Question

我正在嘗試對 Excel 工作表中的日期進行排序，我想單擊一個按鈕來執行此操作，但按鈕不在同一張工作表中我在同一張工作表的按鈕中分配了宏，效果很好，但是當我將按鈕移到另一張紙時，它不起作用！我應該在這段程式碼中編輯什麼？

我還附上了一個螢幕截圖，也許有一個公式而不是這個 VBA 程式碼，或者讓 VBA 程式碼運行而不點擊任何按鈕我沒有 Office 365，所以 =FILTER 函數不起作用

Sub SortCC()
'
' SortCC Macro
'

'
    Sheets("Jan_List").Select
    Range("K2:R4").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Selection.End(xlUp).Select
    Range("T2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("Jan_List").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Jan_List").Sort.SortFields.Add2 Key:=Range( _
        "T2:T1241"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Jan_List").Sort
        .SetRange Range("T2:AA1241")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

多謝

Answer 1

將此程式碼放在普通模組中 -不是在Sheet或ThisWorkbook.
在要排序的每個工作表中新增一個按鈕並將其連接到巨集。

Public Sub SortCC()

    'You want the code to run on the sheet that has the button.
    'To manually push the button the correct sheet must be active.
    'So in this case we can use ActiveSheet.  Note this is the only Sheet reference in the code.
    With ActiveSheet
        'Assumes Column R (18) has a value in last row of data.
        Dim DataRange As Range
        Set DataRange = .Range("K2", .Cells(.Rows.Count, 18).End(xlUp))
        
        DataRange.Copy
        .Range("T2").PasteSpecial Paste:=xlPasteValues
        
        'We could figure out the new range the same way we did with the old one,
        'but seeing as it's the same size as the original and one column across
        'we can just use offset to figure it out.
        Dim NewDataRange As Range
        Set NewDataRange = DataRange.Offset(, DataRange.Columns.Count + 1)
    
        With .Sort
            .SortFields.Clear
            
            'Sorting on first column of NewDataRange so have resized it to one column.
            'For another column use, for example, .Offset(,1).Resize(,1) for second column.
            .SortFields.Add2 Key:=NewDataRange.Resize(, 1), _
                                  SortOn:=xlSortOnValues, _
                                  Order:=xlDescending, _
                                  DataOption:=xlSortNormal
            .SetRange NewDataRange
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlTopToBottom
            .Apply
        End With
                              
    End With
    
End Sub