[[20150518104008]]　『昇順マクロをすっきりさせたいです。』（ぺもと）　ページの最後に飛ぶ

『昇順マクロをすっきりさせたいです。』（ぺもと）

いつもお世話になってます。
今回、同じシートに10個の表を作成し、それぞれをテーブル設定してフィルタをつけました。
それぞれのテーブルの「No,」という項目を昇順にするマクロを組み、ボタンを押すことによって、10個の表の「No,」を昇順にする作業が一気に可能となりました。

しかし、ボタンを押すことによって何度も画面が切り替わるような動きとなり、かっこわるいです。
以下、マクロを記述しますが、このマクロをすっきりさせたいと思っています。
前回教えていただいたマクロも、回答者様のおかげですっきりしたマクロになりました。
でも、その原理というか、なぜそのようにすっきりさせることができるのかわかりません。

Sub Macro1()

    ActiveWorkbook.Worksheets("月間実績 (2)").ListObjects("テーブル6").Sort.SortFields. _
        Clear
    ActiveWorkbook.Worksheets("月間実績 (2)").ListObjects("テーブル6").Sort.SortFields.Add _
        Key:=Range("テーブル6[[#All],[No,]]"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("月間実績 (2)").ListObjects("テーブル6").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    ActiveWorkbook.Worksheets("月間実績 (2)").ListObjects("テーブル7").Sort.SortFields. _
        Clear
    ActiveWorkbook.Worksheets("月間実績 (2)").ListObjects("テーブル7").Sort.SortFields.Add _
        Key:=Range("テーブル7[[#All],[No,]]"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("月間実績 (2)").ListObjects("テーブル7").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

　　　　　　　　　　　　　　　　　　　　　　　　・
　　　　　　　　　　　　　　　　　　　　　　　　・
　　　　　　　　　　　　　　　　　　　　　　　　・
End Sub

上2行なんかは、似ているコードなのでまとめることができるのかなぁと思ったりしますが、根本が分かりません。

お忙しいところすみませんが、ご教授お願いいたします。

＜使用 Excel：Excel2007、使用 OS：Windows7 ＞

 トライ＆エラーでまずはやってみたらどうですか？

 同じようなことがあっても、最初から頼っていたら自分の身につきませんよ！

 WithとApplication.ScreenUpdatingで調べてみてください。
（稲葉） 2015/05/18(月) 11:33

 Withは既にありました・・・
 ヒントの出し方が悪かったですね。
 こういうときは、共通するところとしないところを探して見てください。
 共通しないところは、ListObjects("テーブル7")
                                          ~~この部分ですね。

 なので、この部分をFor文で繰り返ししてはどうですか？
 もし6,7,8,9・・・とカウントアップしているなら
 Dim i As Long
 For i = 6 To 9
     (省略).ListObjects("テーブル" & i)

 こんな感じで回すことができますね！
（稲葉） 2015/05/18(月) 11:38

稲葉さん、コメントありがとうございます。

ヒントを調べてみました。

マクロの冒頭に、Application.ScreenUpdating = false
終わりに、Application.ScreenUpdating = True

をいれることによって画面の切り替わりがなくなりました。
ありがとうございます。

なるほど、Forというコードを使うと繰り返し…メモメモ
共通部分・共通でない部分を探してみるところからはじめてみます。
（ぺもと） 2015/05/18(月) 11:49

Forを使ってみましたところ、構文エラーと出て、
「⇒」の行が赤くなりました。
何が原因でしょうか。

Sub Macro1()

Application.ScreenUpdating = False
Dim i As Long
For i = 6 To 9

    ActiveWorkbook.Worksheets("月間実績 (2)").ListObjects("テーブル" & i).Sort.SortFields. _
        Clear
  ⇒ActiveWorkbook.Worksheets("月間実績 (2)").ListObjects("テーブル" & i).Sort.SortFields.Add _
        Key:=Range("テーブル" & i "[[#All],[No,]]"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
Next
    With ActiveWorkbook.Worksheets("月間実績 (2)").ListObjects("テーブル" & i).Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

End Sub
（ぺもと） 2015/05/18(月) 13:59

（追記）
ActiveWorkbook.Worksheets("月間実績 (2)").ListObjects("テーブル" & i).Sort.SortFields.Add _

        Key:=Range("テーブル" & i "[[#All],[No,]]"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal

この3行が赤くなりました。
（ぺもと） 2015/05/18(月) 14:11

 >Range("テーブル" & i "[[#All],[No,]]")
 iの後ろに「&」がない。

 なお、「i」と「&」の間にはスペースを入れること。
（ねむねむ） 2015/05/18(月) 14:14

ねむねむさん、コメントありがとうございます。

 ActiveWorkbook.Worksheets("月間実績 (2)").ListObjects("テーブル" & i).Sort.SortFields.Add _
        Key:=Range("テーブル" & i & "[[#All],[No,]]"), SortOn:=xlSortOnValues, Order:=
        xlAscending , DataOption:=xlSortNormal

このように直してみましたが、やはり同じように赤字になります。
（ぺもと） 2015/05/18(月) 14:23

 もし、こちらの掲示板への転記時にミスがないのであれば
 >         Key:=Range("テーブル" & i & "[[#All],[No,]]"), SortOn:=xlSortOnValues, Order:=
 のところ、行末に行継続文字（ _）がないようだが。
（ねむねむ） 2015/05/18(月) 14:51

お返事ありがとうございます。
なぜか _ が抜けていたので、付け足し、実行してみました。
が、しかし…

今度は、「'Range'メソッドは失敗しました:'_Global'オブジェクト」
というエラーが出まして、デバッグを押したところ

ActiveWorkbook.Worksheets("月間実績 (2)").ListObjects("テーブル" & i).Sort.SortFields.Add _

        Key:=Range("テーブル & i & [[#All],[No,]]"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal

この3行が黄色になりました。

以下、現在のコード全体です。

Sub Macro1()

Application.ScreenUpdating = False
Dim i As Long
For i = 6 To 15

    ActiveWorkbook.Worksheets("月間実績 (2)").ListObjects("テーブル" & i).Sort.SortFields. _
        Clear
    ActiveWorkbook.Worksheets("月間実績 (2)").ListObjects("テーブル" & i).Sort.SortFields.Add _
        Key:=Range("テーブル & i & [[#All],[No,]]"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
Next
    With ActiveWorkbook.Worksheets("月間実績 (2)").ListObjects("テーブル" & i).Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
Application.ScreenUpdating = True

End Sub
（ぺもと） 2015/05/18(月) 15:03

 こっちだと再現できないですねぇ。
 [[#All],[No,]]
            ~~
            ↑
 テーブルの見出しに、 このカンマ、ついてます？

 もう一度、項目名見直してみてください。

 また、Forの外にApply出すと、Forで回した一番最後しか並べ替え実行されませんよね。
 もう一度確認してみてください。

 それから、範囲指定のところ、
 ＞ActiveWorkbook.Worksheets("月間実績 (2)").ListObjects("テーブル" & i)
 これが3か所ありますが、1か所にまとまります。

 Withの書き方ですが、Withの中にWithを重ねることができます。
 参考にしてください。

 With Workbook("TEST")
     With .Sheet("Sheet1")
         With .Range("A1")
             .Value = "Hi!"
         End With
     End With
 End With

（稲葉） 2015/05/18(月) 15:08

少し横から #ALL邪魔なのでなくします
ActiveWorkbook.Worksheets("月間実績 (2)").ListObjects("テーブル" & i).Sort.SortFields.Add _

        Key:=Range("テーブル" & i & "[No]"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
で赤くならないです。

ちょっとこれを題材にしてプログラムを私なりに考えてみました。

終わったら提示してみます。
（デイト） 2015/05/18(月) 15:39

稲葉さん
[No,]にはカンマがついてます。
Withでまとめてみました。#Allも削除し、実行できました。
このコード、どうでしょうか？

Sub Macro1()

Application.ScreenUpdating = False
Dim i As Long
For i = 6 To 15

    With Sheets("月間実績 (2)")
         .ListObjects("テーブル" & i).Sort.SortFields.Clear
         .ListObjects("テーブル" & i).Sort.SortFields.Add _
          Key:=Range("テーブル" & i & "[No,]"), SortOn:=xlSortOnValues, Order:= _
          xlAscending, DataOption:=xlSortNormal
    End With
    With ActiveWorkbook.Worksheets("月間実績 (2)").ListObjects("テーブル" & i).Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
Next
Application.ScreenUpdating = True

End Sub

デイトさん、コメントありがとうございます。
お忙しいところ、考えてくださってありがとうございます。
もしよろしければ、[#All]がいらない理由を教えていただけませんか？
（ぺもと） 2015/05/18(月) 16:06

 動いて、自分が分かり易ければいいと思いますよ！
 ちなみに、自分なりにまとめるとこんな感じになりました。
 Sortメソッドに明るくないので、プロパティ全部書いてます。

    Sub TEST()
        Dim i       As Long
        Dim WS      As Worksheet
        Dim tbl     As ListObject
        Dim colName As String
        Set WS = ActiveWorkbook.Worksheets("Sheet1")
        colName = "No"
        Application.ScreenUpdating = False
        For i = 1 To 3
            Set tbl = WS.ListObjects("テーブル" & i)
            With tbl.Sort.SortFields
                .Clear
                .Add _
                    Key:=tbl.ListColumns(colName).DataBodyRange, _
                    SortOn:=xlSortOnValues, _
                    Order:=xlAscending, _
                    DataOption:=xlSortNormal
            End With
            With tbl.Sort
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
        Next
        Application.ScreenUpdating = True
    End Sub
（稲葉） 2015/05/18(月) 16:52

2015/05/18(月) 15:03　に動かなかったのは　"テーブル"のダブルクォーテーションがなかったからです。
検証してきずきました

ALLがいらない理由はわかりませんすいません。が#ALLはheaderを含むという意味ですsortでは問題ありませんでしたが以下のような場合は注意が必要です。
テーブルの行数を知りたいような場合

MsgBox ActiveSheet.Range("テーブル1[[#All],[No,]]?").Count
MsgBox ActiveSheet.Range("テーブル1[No,]").Count

上はヘッダーを含み下はヘッダーを含みません1違う数字になります。
（デイト） 2015/05/18(月) 16:59

私が作成したのは今のシートのすべてのListObjectをソートするものを作成しました。いずれ私自身も参考にします。

Sub テーブル全体を選択する()

Dim i As Long

 For i = 1 To ActiveSheet.ListObjects.Count
  With ActiveSheet.ListObjects(i).Sort

  .SortFields.Clear
  .SortFields.Add _
        Key:=Range(ActiveSheet.ListObjects(i) & "[No]"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply

End With
Next

End Sub
（デイト） 2015/05/18(月) 17:02

おはようございます。昨日はお返事ができずすみません。

稲葉さん
本当にご指導ありがとうございます。
次回、また違ったマクロを組むときのための勉強になりました。
でも、まだまだ勉強不足なのでがんばります。

デイトさん
検証もしていただきありがとうございます。
自分ひとりだとなかなかミスに気づきませんのでありがたいです。
header、sortについて勉強してみます。今の私にはまだ難しいようです…。
お忙しい中、マクロを組んでいただきありがとうございます。
参考にさせていただきます。
（ぺもと） 2015/05/19(火) 08:58

コメント返信：

[ 一覧(最新更新順) ]

[[20150518104008]] 『昇順マクロをすっきりさせたいです。』（ぺもと） ページの最後に飛ぶ

[[20150518104008]]　『昇順マクロをすっきりさせたいです。』（ぺもと）　ページの最後に飛ぶ