提示:其它语言是由 Google 机器翻译的。 你可以访问 English 版本。
登录
x
or
x
x
马上登记
x

or

如何在Excel中按字母/字母数字顺序排列工作表?

通常情况下,您可以通过在工作表选项卡栏上拖放工作表选项卡,在Excel中排序或排列工作表选项卡顺序。 但为了使用多个工作表完成此工作,您可以考虑以下棘手方法,以便在大型工作簿中按字母/字母数字顺序对工作表进行快速排序。

用VBA代码按字母/字母数字顺序排列工作表

使用Kutools for Excel按字母/字母数字顺序排列工作表


在Excel中按字母/字母数字顺序快速排序所有工作人员:

在此 对表格进行排序 实用程序 Kutools for Excel 可以帮助您在Excel中按字母或字母数字顺序快速排序所有工作表,只需点击几下即可。 立即下载Kutools for Excel的全功能60天免费试用版!

DOC-排序sheets6

Kutools for Excel:比200方便的Excel加载项,可以在60天免费试用。 下载并免费试用现在!

Office选项卡在Office中启用选项卡式编辑和浏览,使您的工作更轻松......
Kutools for Excel解决了您的大多数问题,并使您的生产率提高了80%
  • 重用任何东西: 将最常用或最复杂的公式,图表和其他任何内容添加到您的收藏夹中,并在将来快速重复使用它们。
  • 超过20文本功能: 从文本字符串中提取数字; 提取或删除部分文字; 将数字和货币转换为英文单词。
  • 合并工具:将多个工作簿和工作表合二为一; 合并多个单元格/行/列,而不会丢失数据; 合并重复的行和总和。
  • 拆分工具:根据价值将数据分割成多个工作表; 一本工作簿可转换为多个Excel,PDF或CSV文件; 一列到多列。
  • 粘贴跳过 隐藏/过滤行; 数和总和 按背景颜色; 将个性化电子邮件批量发送给多个收件人。
  • 超级过滤器: 创建高级过滤方案并应用于任何工作表; 排序 按周,日,频率等; 筛选 通过大胆,公式,评论......
  • 超过300强大的功能; 与Office 2007-2019和365一起使用; 支持所有语言; 在您的企业或组织中轻松部署。

用VBA代码按字母/字母数字顺序排列工作表

有一个宏按微软支持中心发布的alpha进行工作表排序。 我们可以通过以下步骤来应用它:

1. 按住 ALT + F11 键,然后打开 Microsoft Visual Basic for Applications 窗口。

2. 点击 插页 > 模块,然后将下面的宏粘贴到 模块窗口.

VBA:按字母/字母数字顺序排列表格

Sub SortWorkBook()
'Updateby20140624
Dim xResult As VbMsgBoxResult
xTitleId = "KutoolsforExcel"
xResult = MsgBox("Sort Sheets in Ascending Order?" & Chr(10) & "Clicking No will sort in Descending Order", vbYesNoCancel + vbQuestion + vbDefaultButton1, xTitleId)
For i = 1 To Application.Sheets.Count
    For j = 1 To Application.Sheets.Count - 1
        If xResult = vbYes Then
            If UCase$(Application.Sheets(j).Name) > UCase$(Application.Sheets(j + 1).Name) Then
                Sheets(j).Move after:=Sheets(j + 1)
            End If
            ElseIf xResult = vbNo Then
                If UCase$(Application.Sheets(j).Name) < UCase$(Application.Sheets(j + 1).Name) Then
                    Application.Sheets(j).Move after:=Application.Sheets(j + 1)
            End If
        End If
    Next
Next
End Sub

3。 按 F5 键来运行这个宏。 在以下提示框中单击 ,所有的工作表将按照字母顺序升序排列; 并单击 没有,所有的工作表将按照字母顺序降序排列。


使用Kutools for Excel按字母/字母数字顺序排列工作表

如果你不熟悉宏或更喜欢其他方式,你可以试试 Kutools for Excel. Kutools for Excel's 对表格进行排序 工具可以轻松分类所有工作表。

Kutools for Excel : 与超过300方便的Excel加载项,在60天免费试用没有限制.

1。 点击 Kutools Plus > 工作表 > 对表格进行排序。 看截图:

2。 在 对表格进行排序 对话框中,在右窗格中选择您需要的一种排序类型,例如 Alpha排序, 字母数字排序,然后单击 OK 按钮。 看截图:

然后,所有工作表都基于指定的排序类型进行排序。 看截图:

DOC-排序sheets6

Kutools for Excel's 对表格进行排序 工具可以快速重新排列活动工作簿中的所有工作表。 它支持多种排序类型,包括 Alpha排序, 字母数字排序, 颜色分类 逆转。 另外,您还可以上下移动工作表,并重置排序。

提示。如果您想免费试用此实用程序,请转到 免费下载软件 首先,按照上述步骤进行操作。


Office Tab - 在Excel中选项卡式浏览,编辑和管理工作簿:

Office选项卡将Web浏览器(如Google Chrome,Internet Explorer新版本和Firefox)中显示的选项卡界面带到Microsoft Excel。 它将是一个节省时间的工具,在您的工作中无可比拟。 见下面的演示:

点击免费试用Office Tab!

Excel的Office选项卡


演示:按字母/字母数字顺序对所有工作表进行排序

Kutools for Excel:比200方便的Excel加载项,可以在60天免费试用。 下载并免费试用现在!


相关文章:

按颜色对工作表标签进行排序


Kutools for Excel解决了您的大多数问题,并使您的生产率提高了80%

  • 重用: 快速插入 复杂的公式,图表 以及你以前用过的任何东西; 加密单元格 密码; 创建邮件列表 并发送电子邮件...
  • 超级方程式酒吧 (轻松编辑多行文字和公式); 阅读布局 (轻松读取和编辑大量单元格); 粘贴到过滤范围...
  • 合并单元格/行/列 不丢失数据; 分裂细胞含量; 组合重复的行/列...防止重复的细胞; 比较范围...
  • 选择复制或唯一 行; 选择空行 (所有细胞都是空的); 超级查找和模糊查找 在许多工作簿中; 随机选择......
  • 精确复制 多个单元格而不更改公式参考; 自动创建参考 多张表; 插入项目符号,复选框等等......
  • 提取文本,添加文本,按位置删除, 删除空间; 创建和打印分页小计; 在单元格内容和注释之间转换...
  • 超级过滤器 (将过滤方案保存并应用到其他工作表); 高级排序 按月/周/日,频率等; 特殊过滤器 用粗体,斜体......
  • 结合工作簿和工作表; 根据键列合并表; 将数据拆分为多个表格; 批量转换xls,xlsx和PDF...
  • 超过300强大的功能。 支持Office / Excel 2007-2019和365。 支持所有语言。 在您的企业或组织中轻松部署。 全功能30天免费试用。
kte tab 201905

Office选项卡为Office提供选项卡式界面,使您的工作更轻松

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,Publisher,Access,Visio和Project。
  • 在同一窗口的新选项卡中打开并创建多个文档,而不是在新窗口中。
  • 通过50%提高您的工作效率,每天为您减少数百次鼠标点击!
官方底部
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Mahir · 11 months ago
    Thank you for your help, very much appreciated...
  • To post as a guest, your comment is unpublished.
    Sarah · 1 years ago
    I don't know VBA at all but your instructions worked perfectly. Thank you so much!
  • To post as a guest, your comment is unpublished.
    Mahir · 1 years ago
    Thank you :)
  • To post as a guest, your comment is unpublished.
    SAM · 1 years ago
    Very helpful :) Thank you!
  • To post as a guest, your comment is unpublished.
    Sagar · 1 years ago
    Thank you sooooooo much !!!
  • To post as a guest, your comment is unpublished.
    Varun Shah · 2 years ago
    Thanks a Lot!!!
  • To post as a guest, your comment is unpublished.
    Chris Hall · 2 years ago
    I so appreciate this ... thank you...thank you...Thank You!~chrissy
  • To post as a guest, your comment is unpublished.
    Roxanne · 2 years ago
    I love you right now. Thank you!
  • To post as a guest, your comment is unpublished.
    mozay · 2 years ago
    thanks so much guys this saved me alot of time
  • To post as a guest, your comment is unpublished.
    eka · 2 years ago
    Thanks so much..
  • To post as a guest, your comment is unpublished.
    liezl · 2 years ago
    yes! you help me a lot. big thanks :-)
  • To post as a guest, your comment is unpublished.
    Jae · 2 years ago
    Woohoo! Thanks so very much!!!!! :lol:
  • To post as a guest, your comment is unpublished.
    Terri A. Lapwing · 2 years ago
    Good morning, On an Excel spread sheet, you can group columns of data. They condense and are represented by a + sign; if you want to expand them, a line appears along with the - sign. Can you perform a similar task with worksheets inside of an Excel workbook. I would like to condense a set of worksheets of a particular fiscal quarter.
    Please help,
    Terri
  • To post as a guest, your comment is unpublished.
    Peggy · 2 years ago
    excellent!!! this saved me a lot of time!
  • To post as a guest, your comment is unpublished.
    racsar · 2 years ago
    [b]Omg, such a time saver! [/b]
    I have a list to keep track of which books I've read by favorite serial-writer authors. A different worksheet for each author got plugged-in very haphazardly as I discovered new authors differentiating them only by tab colors. Over the years it has grown to [b]37 worksheets[/b]. For some reason it just occurred to me today that it would be darn nice to have them in alphabetical order. A task which seemed daunting when I thought of pushing them around by hand.
    I have an old 2003 vs of Excel, running in Windows 10. Also, I'm no Tech, I'm completely autodidactic, so attempting anything of this sort (for me) is always risky. Amazingly, it worked like a charm.
    THANK YOU, so much!

    I have bookmarked you, I WILL be back.
  • To post as a guest, your comment is unpublished.
    josphat · 3 years ago
    This is amazing. Perfect. Thank you
  • To post as a guest, your comment is unpublished.
    Aziz · 3 years ago
    Thank you a lot many many thanks
  • To post as a guest, your comment is unpublished.
    John Machin · 3 years ago
    Macro worked first time, thank -you
  • To post as a guest, your comment is unpublished.
    Ming · 3 years ago
    Thanks for the vba codes, it works1

    One suggestion:
    After the dim comment add the following:
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    Before the end sub comment, add the following
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

    This will speed up the process.
  • To post as a guest, your comment is unpublished.
    Wasim Aftab · 3 years ago
    Thanks,
    It works Great.
  • To post as a guest, your comment is unpublished.
    Rosie · 3 years ago
    This macro worked great for me for a while! I have been using it in a huge spreadsheet at work, and it has been great for keeping it organized as I am continually adding new tabs. However, today I got this error message:

    "Excel was able to open the file by repairing or removing the unreadable content. Removed Part: /xl/vbaProject.bin part. (Visual Basic for Applications (VBA))"

    Now the macro is gone. Does anyone know why this happened or how I can fix it? I'd like to be able to keep using this macro without issues...
  • To post as a guest, your comment is unpublished.
    kristine · 3 years ago
    I love this macro! I want to modify the code to exclude certain sheets. Please help
  • To post as a guest, your comment is unpublished.
    Wahid · 3 years ago
    Work great, many thanks!!
  • To post as a guest, your comment is unpublished.
    Dave · 3 years ago
    How can I force an alpha sort in which lower case follows upper case?
    I tried a Custom List, which accomplished nothing, realized I hadn't selected Options >> Case Sensitive and tried again, with almost the same results.
    Only difference was that Excel sorted individual lower case entries before identical upper case ones.
    What I want is: ABCDE ... WXYZabcde ... wxyz

    Thanks!
  • To post as a guest, your comment is unpublished.
    adeoye · 3 years ago
    hi1 thanks for the code, does it take a long while to load after running the macro
  • To post as a guest, your comment is unpublished.
    Miguel · 3 years ago
    Thank you so much !! It worked great.
  • To post as a guest, your comment is unpublished.
    Sidrit Mero · 3 years ago
    You saved me 3600 seconds ! thank you !
  • To post as a guest, your comment is unpublished.
    Sam_Mel · 3 years ago
    Useful instructions...Thanks for adding value toward others knowledge.
  • To post as a guest, your comment is unpublished.
    rachel · 3 years ago
    thanks tons!!! fantastic macro! and such clear instructions!
  • To post as a guest, your comment is unpublished.
    Nes · 4 years ago
    Thank you so much, now i know what to do.. thank you very much
  • To post as a guest, your comment is unpublished.
    Gary Ho · 4 years ago
    Marco is fast and Awesome man. :lol:
  • To post as a guest, your comment is unpublished.
    Alka · 4 years ago
    Awesome!!!!worked out great!!!! :-)
  • To post as a guest, your comment is unpublished.
    Akshay · 4 years ago
    Thanks aton, sorted almost 100 sheets easily! :D
  • To post as a guest, your comment is unpublished.
    Vamsi · 4 years ago
    :lol:
    Sorting Macro worked perfectly, Thank you
  • To post as a guest, your comment is unpublished.
    Zahid · 4 years ago
    Thanks a lot
    very very nice
    once again thanks
  • To post as a guest, your comment is unpublished.
    Volkan · 4 years ago
    Thanks a lot !
    Very very useful tool.
  • To post as a guest, your comment is unpublished.
    Giang · 4 years ago
    Very nice :)
    Thanks a lot!!!
  • To post as a guest, your comment is unpublished.
    Kishore Joshi · 4 years ago
    worked like a charm. good work.
  • To post as a guest, your comment is unpublished.
    canuck74 · 4 years ago
    This saved me from having to manually sort worksheets for every company traded on American exchanges - more than 10,000 worksheets. Thanks
  • To post as a guest, your comment is unpublished.
    Madhav · 4 years ago
    Thanks a lot !!!!!!!!!!!!!
    Very very useful tool.
  • To post as a guest, your comment is unpublished.
    mahesh · 4 years ago
    hi, thanks, worked wonderfully to sort by macros in no time.
    much appreciate your tool.
  • To post as a guest, your comment is unpublished.
    GayeM · 4 years ago
    Macro worked perfectly. :-)
  • To post as a guest, your comment is unpublished.
    stevedoyle · 5 years ago
    Hi, I'm trying to run the macro but keep getting the error as follows:

    expected end of statement.

    The Macro is as follows:
    ysg2y
    VBA: Sort sheets order.

    12345678910111213141516171819 Sub SortWorkBook() 'Updateby20140624 Dim xResult As VbMsgBoxResult xTitleId = "KutoolsforExcel"xResult = MsgBox("Sort Sheets in Ascending Order?" & Chr(10) & "Clicking No will sort in Descending Order", vbYesNoCancel + vbQuestion + vbDefaultButton1, xTitleId) For i = 1 To Application.Sheets.Count For j = 1 To Application.Sheets.Count - 1 If xResult = vbYes ThenIf UCase$(Application.Sheets(j).Name) > UCase$(Application.Sheets(j + 1).Name) ThenSheets(j).Move after:=Sheets(j + 1) End IfElseIf xResult = vbNo ThenIf UCase$(Application.Sheets(j).Name) < UCase$(Application.Sheets(j + 1).Name) ThenApplication.Sheets(j).Move after:=Application.Sheets(j + 1) End IfEnd IfNextNextEnd Sub
  • To post as a guest, your comment is unpublished.
    GaryE · 5 years ago
    Superb simple macro, worked a treat
  • To post as a guest, your comment is unpublished.
    yabyaban · 5 years ago
    Thank you very much in advance
  • To post as a guest, your comment is unpublished.
    Ricardo Pacheco · 5 years ago
    I was able to get to the last step.
    I pressed F5

    and got this error:

    Compile Error

    Expected End Sub
  • To post as a guest, your comment is unpublished.
    Gwen Banger · 5 years ago
    Thank you kindly - worked in seconds. Much appreciated - Take Care
  • To post as a guest, your comment is unpublished.
    Bharat Solanki · 5 years ago
    excellent work...thanx
  • To post as a guest, your comment is unpublished.
    Ross · 5 years ago
    Wondering if anyone could help me.
    If i had tabs Alpha,Bravo,Charlie,Delta, Echo etc and i wanted to sort the tabs according to a list. lets say list contains Alpha, Charlie, Echo..is there anyway to do this? and the remaining tabs to alight behind the listed tabs in alphabetical order?

    thanks
  • To post as a guest, your comment is unpublished.
    Dellibabu · 5 years ago
    this macro really Excellent, thank you very much for giving it