☰

按字母顺序对工作表进行排序 VBA

tag-icon • tag-icon microsoft-excel vba macros sorting
按字母顺序对工作表进行排序 VBA

我正在努力解决将所有工作簿合并为一个后如何对工作表进行排序的问题。

这是我的代码：

Dim wbDst As Workbook
Dim wbSrc As Workbook
Dim wsSrc As Worksheet
Dim mypath As String
Dim strFilename As String
Dim saveFolder As String

Dim i%, j%

Application.DisplayAlerts = False
Application.EnableEvents = False
Application.ScreenUpdating = False
Application.AskToUpdateLinks = False

mypath = "C:\Acct. Docs" 'Change to suit
Set wbDst = Workbooks.Add(xlWBATWorksheet)
strFilename = Dir(mypath & "\*.xlsx", vbNormal)
Dim fname As String

saveFolder = "C:\FORMATTED"

If Len(strFilename) = 0 Then Exit Sub

Do Until strFilename = ""
    Set wbSrc = Workbooks.Open(Filename:=mypath & "\" & strFilename)
    Set wsSrc = wbSrc.Worksheets(1)
    wsSrc.Copy After:=wbDst.Worksheets(wbDst.Worksheets.Count)
    wbSrc.Close False

    For i = 1 To wbDst.Worksheets.Count - 1
        For j = 1 To wbDst.Worksheets.Count
            If wbDst.Worksheets(j).Name < wbDst.Worksheets(i + 1).Name Then
                wbDst.Worksheets(j).Move Before:=Sheets(i + 1)
            End If
        Next j
    Next i
    strFilename = Dir()
Loop


wbDst.Worksheets(1).Delete
fname = Sheets("Sheet1").Range("C5").Text
ActiveWorkbook.SaveAs saveFolder & "\" & fname & " Account Documentation.xlsx", FileFormat:=51
wbDst.Close

Application.DisplayAlerts = True
Application.EnableEvents = True
Application.ScreenUpdating = True

子目录结束

我希望工作表按字母顺序排序。提前谢谢您！

答案1

哦！我已经搞明白了！！：D 我只是在循环中交换了条件：D

 For i = 1 To wbDst.Worksheets.Count
        For j = 1 To wbDst.Worksheets.Count - 1
            If wbDst.Worksheets(i).Name < wbDst.Worksheets(j + 1).Name Then
                wbDst.Worksheets(i).Move Before:=Sheets(j + 1)
            End If
        Next j
    Next i

相关内容