[[20050201113206]] 『シートのソート』(Null) >>BOT

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『シートのソート』(Null)
Excel:Excel2003
OS:WinXP SP2

シート名称が 
ID1 ID2 ID3

などと付いていて それが 
ID1 ID3 ID2

というように 順番に並んでいないときに一括でソートする機能はないでしょうか?
ご教授お願いいたしますmm


 ベタですが。
Sub Test()
Dim i As Integer
Application.ScreenUpdating = False
Sheets.Add
ActiveSheet.Name = "Kari"
For Each Sh In Worksheets
    Worksheets("kari").Cells(i + 1, 1).Value = Sh.Name
    i = i + 1
Next Sh
    Range("A1").Sort Key1:=Range("A1"), Orientation:=xlTopToBottom
    For i = 1 To Worksheets.Count
        MyName = Worksheets("Kari").Cells(i, 1).Value
        Worksheets(MyName).Move Before:=Sheets(i)
    Next i
Application.DisplayAlerts = False
Sheets("Kari").Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

 (川野鮎太郎)

 マクロしかないのであれば一応挑戦
Sub シートの並び替え()
Dim ShName As String, T() As String, s() As String
Dim ss(1) As Integer
ss(1) = 32
T = Input_Name()
T = Sort_Name(T)
Call move_Name(T)
MsgBox ("Complete")
End Sub
Function Input_Name()
Dim n As Worksheet
Dim T() As String

ReDim T(1 To ActiveWorkbook.Worksheets.Count) As String

For Each n In ActiveWorkbook.Worksheets

    If Left(n.Name, 1) = "x" Then
        T(n.Index) = n.Name
    Else
        T(n.Index) = "none"
    End If
Next
Input_Name = T
End Function
Function Sort_Name(T() As String)
Dim n As Integer, s As Integer
Dim Ttable() As String, T_temp As String
ReDim Ttable(1 To UBound(T)) As String
For n = 1 To UBound(T) - 1 Step 1
    For s = n To UBound(T) Step 1
        If Ttable(n) < T(s) And Not T(s) = "none" Then
            T_temp = Ttable(n)
            Ttable(n) = T(s)
            T(s) = T_temp
        End If
    Next s
Next n
Sort_Name = T
End Function
Function move_Name(T() As String)
Dim n As Integer
Dim e As Worksheet
Application.ScreenUpdating = False
For n = 1 To UBound(T) Step 1
    If Not T(n) = "" Then
        For Each e In ActiveWorkbook.Worksheets
            If Sheets(n).Name = e.Name Then
                e.Move Before:=Sheets(1)
            End If
        Next e
    End If
Next n
Application.ScreenUpdating = True
End Function

 (川野)さんの ソースよりぜんぜん長いですねorz
 シートを作っちゃえばソート機能をつかえるわけですね(≧∇≦)b
 微調整しつつ がんばります
 ありがとうございましたぁ
 (Null)

コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.