Excel VBA横向排序宏仅在特定工作表生效问题求助

需求与问题背景 #

• 编写VBA宏实现每行数字横向升序排序，直至遇到空单元格或非数值数据（Excel无原生该功能）
• 数据规模：73行7列，所有数值不超过50
• 核心问题：宏仅在名为Sheet1的工作表中正常生效，更换工作表引用为其他表名、改用ActiveSheet或新建工作簿后，宏均无法正常运行

已尝试的排查操作 #

• 修改代码中固定工作表引用为目标表名称
• 将Worksheets("Sheet1")替换为ActiveSheet以适配当前活动表
• 添加计数器模块确认脚本是否执行及循环次数
• 调整目标单元格格式为常规、数值、自定义等多种类型

待排查代码 #

代码1：SortAndMove #

Sub SortAndMove()
    Dim rng As Range
    
    ' Set the initial range to the second row, columns A to F
    Set rng = Worksheets("Sheet1").Range("A1:G1")
    
    ' Loop until the first cell in the current row is not a number or is <= 0
    Do While IsNumeric(rng.Cells(1, 1).Value) And rng.Cells(1, 1).Value > 0
        ' Sort the selected range horizontally in ascending order
        rng.Sort Key1:=rng.Cells(1, 1), Order1:=xlAscending, Header:=xlNo
        
        ' Move the selection down one row
        Set rng = rng.Offset(1, 0).Resize(, 7) ' Resize to select the first six columns

    Loop
End Sub

代码2：HorizontalSort #

Sub HorizontalSort()

    Dim rng As Range
    Dim counter As Range
    Dim i As Integer
    
    
    ' Set the initial range to the second row, columns A to G
    Set rng = Worksheets("Sheet1").Range("A1:G1")
    
    ' Sets the range where the counter will be updated on the worksheet
    Set counter = Worksheets("Sheet1").Range("I1")
    i = 0
    
    
    ' Loop until the first cell in the current row is not a number or is <= 0
    Do While IsNumeric(rng.Cells(1, 1).Value) And rng.Cells(1, 1).Value > 0
        ' Sort the selected range horizontally in ascending order
        rng.Sort Key1:=rng.Cells(1, 1), Order1:=xlAscending, Header:=xlNo
        
        ' Move the selection down one row
        Set rng = rng.Offset(1, 0).Resize(, 7) ' Resize to select the first seven columns
        
        ' Increment the counter and print it's value during each iteration of the loop
        i = i + 1
        counter.Value = i
        
    Loop
    
End Sub

请求 #

排查代码存在的问题，或分析宏在非Sheet1工作表/新工作簿中失效的原因。

内容的提问来源于stack exchange，提问作者NonKonnektion