VBA 特定の範囲のシートを並べ替えをして、処理後にマクロ実行前のシートを表示させたい。

EXCEL2013を使用しています。
現在、以下のような構文を使用して、保存時に実行しています。

処理は、シート名を書き換えてシート保護をかけて先頭から9番目のシートのA1へ戻るようにしています。
これを、次のような処理にしたいと考えています。
１．シート名を書き換え
２．シート保護
３．先頭から9番目のシートから、31番目のシートをシート名でソート
４．マクロ実行前に作業していたシートへ戻る

ちなみに、書き換えたシート名が、"１．○○○"　や、"２．△△△"などになっているのですが、"."より前の値で並び替えられると便利になります。
また、デフォルトのシート名のままのものと、書き換えられたものがあり、書き換えられたものにだけ"."つきのシート名になります。

よろしくお願いいたします。

*******************************
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

'画面のちらつき防止
Application.ScreenUpdating = False

'シート名更新
For i = 1 To Sheets.Count
Sheets(i).Select
Sheets(i).Name = Range("A1").Value
Next i

'全シート保護
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Protect Password:="aoken", _
DrawingObjects:=False, _
Contents:=True, _
AllowInsertingRows:=True, _
AllowDeletingRows:=True, _
Scenarios:=True, AllowFormattingCells:=True

Next ws

Application.Goto Sheets(9).Range("A1"), True

End Sub
*******************************

質問者からの補足コメント

• さらに補足いたします。
  
  実は、"１．○○○" や、"２．△△△"の一ケタの数字は、2バイトの全角なのです。"10.□□□"は、1バイトの半角数字なのです。
  これを、順番にするのは難しいでしょうか？

  No.1の回答に寄せられた補足コメントです。 補足日時：2015/12/22 14:25

No.2 回答者： WindFaller 回答日時： 2015/12/22 19:29

こんばんは。

>順番にするのは難しいでしょうか？

そんなことはありませんが、質問のコードを書いた人が、シャチ君さんなら、お気づきではなかったでしょうか。半角にして、数字を取り出せばよいわけです。StrComv --vbNarrow, Val は、先頭から数字だけを出すもので、文字列はなくなってしまいます。

つまりここの部分ですね。
　If Val(Worksheets(j).Name) <> 0 And Val(sName) <> 0 Then　'数値の比較か？
　　　If Val(Worksheets(j).Name) < Val(sName) Then　　'数字を取り出す

**
このように半角にして比べれば、数値の比較は同じ並びになります。

　　　　 If Val(StrConv(Worksheets(j).Name, vbNarrow)) <> 0 And Val(StrConv(sName, vbNarrow)) <> 0 Then
　　　　　　　　If Val(StrConv(Worksheets(j).Name, vbNarrow)) < Val(StrConv(sName, vbNarrow)) <> 0 Then
　　　　　　　　
p.s.シートの並べ替えは、マクロ１年生ですと、やはり難しいですね。これは、手の内を明かすと、アルゴリズムのバブルソートという名前のテクニックを利用したものです。

No.1 回答者： WindFaller 回答日時： 2015/12/22 11:06

こんにちは。

>ちなみに、書き換えたシート名が、"１．○○○"　や、"２．△△△"などになっているのですが、"."より前の値で並び替えられると便利になります。

つまり、先頭の1. 2. が優先させられることになるのですか？文字列比較ですと、1の次が11 などが先になってしまうはずです。それを、数字で比較するようにしました。

質問のコードの様子ですと、BeforeSave イベントへの組み込み方はお分かりになるレベルだと思いますから、ソートの部分だけを提示します。以下のコードは、昔作ったもので、私のオリジナルのはずです。

'//

Sub Sample_SortWorksheet()
　　Dim sName As String
　　Dim iCount As Integer '念のためInteger型のままにしておく
　　Dim acsh As Worksheet
　　Dim i As Long
　　Dim j As Long
　　Set acsh = ActiveSheet
　　'Application.ScreenUpdating = False
　　iCount = Worksheets.Count
　　For i = 9 To iCount - 1 '9番目シートからの並べ替え／その前は手付かず
　　　　sName = Worksheets(i).Name
　　　　For j = i + 1 To iCount
　　　　　　If Val(Worksheets(j).Name) <> 0 And Val(sName) <> 0 Then　'数値の比較か？
　　　　　　　　If Val(Worksheets(j).Name) < Val(sName) Then　　'数字を取り出す
　　　　　　　　　　sName = Worksheets(j).Name
　　　　　　　　End If
　　　　　　Else
　　　　　　　　If Worksheets(j).Name < sName Then　'それ以外の文字の比較か？
　　　　　　　　　　sName = Worksheets(j).Name
　　　　　　　　End If
　　　　　　End If
　　　　Next
　　　　Worksheets(sName).Move before:=Worksheets(i)
　　Next
　　acsh.Select
　　'Application.ScreenUpdating = True

End Sub
'///

この回答へのお礼

早速の回答ありがとうございます。

これを、もう少しアレンジすれば、思った通りの挙動になりそうなのですが、この構文だと問題点が2つ発生しました。

① 10.がソートされない。
　シート名に"１.○○○"　や、"２.△△△"と番号を振っているのですが、十番目のシート名は、”10.□□□”なのですが、このシートがソートされません。

② 番号を振っているシートが一番最後に配列されてしまう。
　"１.○○○"のように番号の振られたシートが後尾に配列されてしまうようです。
　"１.○○○"を９番目のシートから並べることはできますでしょうか？
　ちなみに、ブックによって、１.～10.だったり、１.～３.までしかなかったりします。

よろしくお願いいたします。

お礼日時：2015/12/22 13:35