今儿……我们说个常见但是对新手而言不太好处理的问题! 工作表重新排序,不像单元格中的数值、文本我们只要点一下按钮就可以排序了,工作表没有这样的功能,尤其当工作表顺序需要重新调整的时候,这个就是一个麻烦的问题!所以小编决定来给大家写一个模板 刚写完代码还是热乎的!我们一起来看看效果,本文模板文件下载见文末 '公众号:Excel办公实战 '作者:小易 '时间:20201025224850 '----------------------------------------------------- Sub 获取工作表名称() '新建一个工作表-重命名用于存放数据 On Error Resume Next Dim ws As Worksheet Set ws = Worksheets(newShtName) Dim Wb As Workbook Set Wb = ActiveWorkbook If Err <> 0 Then With Wb .Sheets.Add before:=.Sheets(1) ActiveSheet.Name = newShtName End With Err.Clear Else ws.Cells.Clear End If '获取工作表名称 Dim i As Integer Wb.Worksheets(newShtName).[a1:b1] = [{"序号","名称"}] For i = 2 To Wb.Worksheets.Count With Wb.Worksheets(newShtName) .Cells(i, 1) = i - 1 '序号 .Cells(i, 2) = Wb.Worksheets(i).Name End With Next End Sub Sub 工作表排序() Dim i As Integer Dim j As Integer Dim ws As Worksheet With ActiveWorkbook Set ws = .Worksheets(newShtName) For i = 2 To .Sheets.Count + 1 For j = .Sheets.Count To i Step -1 If ws.Cells(i, 2).Value = .Sheets(j).Name Then If i <> j Then .Sheets(j).Move before:=.Sheets(i) End If End If Next Next .Sheets(1).Select End With End Sub |
|