通过宏，根据工作表名称重新排列Excel表格 [英] Re-order Excel Sheets based on Sheet names via a macro

问题描述

如果我在我的excel文档中有6张表，按照这个顺序排列：O1，O2，O3，O1_＃2，O2＃2，O3＃3

If I have 6 sheets in my excel document named and arranged in this sequence : O1, O2, O3, O1_#2,O2#2,O3#3

是否可以编写宏来更改这些工作表的顺序？
这是我正在寻找的顺序：
O1，O1＃2，O2，O2＃2，O3，O3＃2

Will it be possible to write a macro to change the sequence of these sheets? This is the sequence I am looking for: O1, O1#2, O2, O2#2, O3, O3#2

谢谢

推荐答案

如果我理解正确，您需要按字母顺序对表格进行排序：

If I understand you correctly, you need to sort sheets in alphabetical order:

Sub SortSheets()

    Dim shNames As Collection
    Dim i As Long, j As Long
    Dim temp As String
    Dim sh As Worksheet

    Set shNames = New Collection

    'add sheet names in collection
    For Each sh In ThisWorkbook.Worksheets
        shNames.Add sh.Name, sh.Name
    Next sh

    'bubble sort
    For i = 1 To shNames.Count - 1
        For j = i + 1 To shNames.Count
            If shNames(i) > shNames(j) Then
               temp = shNames(j)
               shNames.Remove j
               shNames.Add temp, temp, i
            End If
        Next j
    Next i

   ' move sheets
   For i = shNames.Count - 1 To 1 Step -1
       Worksheets(shNames(i)).Move Before:=Sheets(1)
   Next i

End Sub

这篇关于通过宏，根据工作表名称重新排列Excel表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！