☰

如果我从另一张工作表中的按钮运行 VBA 代码，则保持该代码有效，或者用公式替换代码

tag-icon • tag-icon microsoft-excel worksheet-function vba sorting
如果我从另一张工作表中的按钮运行 VBA 代码，则保持该代码有效，或者用公式替换代码

我正在尝试对 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

在此处输入图片描述

多谢

答案1

将此代码放在普通模块中 -不是在 aSheet或中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

相关内容