刚刚遇到一个问题,程序处理完Excel之后,发现sheet的序号排序乱了,问过谷老师,拿到一个答案,贴出这段脚本,
做为备份。
Sub Sorting() Dim sCount As Integer, i As Integer, j As Integer Application.ScreenUpdating = False sCount = Worksheets.Count If sCount = 1 Then Exit Sub For i = 1 To sCount - 1 For j = i + 1 To sCount If Worksheets(j).Name < Worksheets(i).Name Then Worksheets(j).Move before:=Worksheets(i) End If Next j Next i End Sub Sub SortSheet() Dim WsCount As Integer Dim WsArray() As String Dim Ws As Worksheet On Error Resume Next WsCount = ActiveWorkbook.Worksheets.Count ReDim WsArray(1 To WsCount) If ActiveWorkbook.ProtectStructure Then MsgBox ActiveWorkbook.Name & " 被保护,不能进行排序,请解除保护后排序", _ vbCritical, "不能排序工作表" Exit Sub End If For Each Ws In ActiveWorkbook.Worksheets t = t + 1 WsArray(t) = Ws.Name Next Ws '对数组进行排序 For i = 1 To UBound(WsArray) - 1 For j = i + 1 To UBound(WsArray) If WsArray(i) > WsArray(j) Then t = WsArray(i) WsArray(i) = WsArray(j) WsArray(j) = t End If Next j Next i '利用Move方法以及Sheets(i)移动工作表,按指定的顺序排列 For i = 1 To WsCount Worksheets(WsArray(i)).Move before:=Sheets(i) Next i End Sub
使用方法:
在sheet底脚右击打开“查看代码”-》在代码区域cp以上脚本-》菜单“运行”(或者F5),保存即可!