【再質問】【マクロ】複数シートのデータを自動で並び替えしたい

いつもお世話になっております。

先日、下記にて質問をさせて頂きましたが私の質問の仕方がわかりづらく
申し訳ございませんでした。
↓
https://oshiete.goo.ne.jp/qa/11124818.html

自分なりにいろいろと調べ、下記までたどり着いたのですが
このマクロを希望条件に変更する為の知識がなく、再度質問させて頂きました。

●シート１のB5からP61の範囲でO列をキーとして降順に並べ替えをしたい。

Sub SortTest()
Worksheets("sheet1").Activate
Worksheets("sheet1").Range("B5:P61") _
.Sort Key1:=Range("O5"), order1:=xlDescending
End Sub

上記のマクロを下記希望条件で並べ替えができるように変更したいです。
どのように修正すればよろしいでしょうか？

＊希望条件
sheet1~sheet3の複数シートを対象とし
B5からO61のO列をキー、Q5~AE５のAD列をキー、AF5~AU5のAT列をキーとして降順に並べ替えをしたいです。
※対象列数は不規則です。

詳しい方、何卒ご教授願います。

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

• シートと参照先を増やしてしまったことが原因でしょうか？
  下記にて修正したモジュールを記載いたします。
  ↓
  ご教授頂きましたモジュール
  ①shts = Array～以降
  ➁cellRange = Array～以降
  
  ①2018.10月～2019.9月、第一四半期、第二四半期、第三四半期、第四四半期、上四半期、下四半期、年間
  ➁B5:P61,O5、Q5:AE5,AD5、AF5:AU5,AT5、AV5:BJ5,BI5、BK5:BZ5,BY5、CA5:CO5,CN5
  CP5:DE5,DO5、DF5:DM5,DM5、DN5:EC5,EB5、ED5:ES5,ER5、ET5:FI5,FH5
  FJ5:FY5,FX5、FZ5:GO5,GN5、GP5:HD5,HC5"、HE5:HT5,HS5、AF5:AU5,AT5
  
  知識不足で申し訳ございませんが
  何卒、ご教授願います。

    補足日時：2019/05/21 14:13

• 

  ご教授頂きましてありがとうございました。
  
  ご教授頂きました内容ですが、私の解釈が間違っていたら申し訳ありません。
  下記に修正という認識でしたが、修正マクロを実行したのですが
  並び順がとびとびになってしまい、降順で並べ替えになりませんでした。。。
  何が原因でしょうか？
  
  ★修正後マクロ
  　cellRange = Array("B5:P61,O61")
  
  何度も申し訳ございませんが、何卒ご教授願います。

  No.3の回答に寄せられた補足コメントです。 補足日時：2019/05/21 17:15

No.9 回答者： 高木さんWIN 回答日時： 2019/05/26 05:43

No.7です。

単独のグループではキチンと並び変わるけど、No.1さんのコードを参考にそちらで改良したものが正常に動かないと言うのであれば、その改良コードを提示された方が早いかもですね。
改良するべきポイント又は方法などが違っているのかも知れませんので。

この回答へのお礼

こんにちわ。

ご親切にアドバイス頂きましてありがとうございます。
再度質問し直してみます。

いろいろとありがとうございました。

お礼日時：2019/05/27 15:41

No.8 回答者： fujillin 回答日時： 2019/05/23 11:26

No1,No2です

大分検証していただいた後のようなので、遅まきながら感はありますが・・・

Sortの方法の件ですが、最初のご質問文に挙げられているのはSortメソッド、No5のお礼に挙げられているのはSortオブジェクトを利用した方法になっていますね。
No1の回答は、元のご質問文に合わせて前者の回答になっていますが、ご質問文のコードであれば動作しているのですよね？
（MSDNのメソッドにもあるので、現状のExcelならほぼ使えるメソッドだと思いますが）

いろいろ試行なさっていると思いますが、エラー内容は毎回最初のエラー、
『並べ替えの参照が正しくありません。参照が並べ替えるデータ内にあることと[最優先されるキー]ボックスが空白でないことを確認してください。』
が、続いている状態なのでしょうか？

このエラーだと、キーを含め範囲指定がおかしいとしか思えませんが、単独で
Sub TEST()
　Worksheets("Sheet1").Range("B5:P61").Sort _
　　Key1:=Worksheets("Sheet1").Range("O5"), order1:=xlDescending
End Sub
を実行してもエラーになりますか？
あるいはエラーはでないが降順にならない？
（シート名、セル範囲は実際に存在する内容にしてください）

どちらでもない場合は、コードの問題よりも最初にデータとして与えている、「シート名」及び「セル範囲の指定」におかしな内容が混ざっていないかを確認してみてください。

もしも上記のTEST()が実行できない場合は、基本的な構文が通じていない（？）という可能性がありますので、考え方を変える必要があるのかも知れません。

この回答へのお礼

お世話になっております。
返信が遅くなり、申し訳ありません。

>Sortの方法の件ですが、最初のご質問文に挙げられているのはSortメソッド、No5のお礼に挙げられているのはSortオブジェクトを利用した方法になっていますね。
>No1の回答は、元のご質問文に合わせて前者の回答になっていますが、ご質問文のコード>であれば動作しているのですよね？
>（MSDNのメソッドにもあるので、現状のExcelならほぼ使えるメソッドだと思いますが）
>いろいろ試行なさっていると思いますが、エラー内容は毎回最初のエラー、
>『並べ替えの参照が正しくありません。参照が並べ替えるデータ内にあることと[最優先されるキー]ボックスが空白でないことを確認してください。』
>が、続いている状態なのでしょうか？
→動作はしております。
[最優先されるキー]~のエラーは出ておりませんが、並び替えが降順にならずバラバラな並び順になってしまいます。

>このエラーだと、キーを含め範囲指定がおかしいとしか思えませんが、単独で
>Sub TEST()
>Worksheets("Sheet1").Range("B5:P61").Sort _
>Key1:=Worksheets("Sheet1").Range("O5"), order1:=xlDescending
>End Sub
>を実行してもエラーになりますか？
>あるいはエラーはでないが降順にならない？
>（シート名、セル範囲は実際に存在する内容にしてください）
→上記単独のマクロであれば、降順に正しく並び替えられます。

それを複数シート、複数列を選択して一括で並び替えするにはどうすればよいでしょうか？

何度も申し訳ございませんが、ご教授頂けますと幸いです。

お礼日時：2019/05/24 15:10

No.7 回答者： 高木さんWIN 回答日時： 2019/05/23 06:42

No.6です。

＞シートですが、まとまっています。
＞どのように修正すれば良いか…。

まず『ブック全てのシートが対象』ではなく『ある部分にまとまっているシートのみが対象』と考えた場合、その『ある部分』がどこなのかが重要ですね。
シートインデックスＮｏは一番左を 1 として右にカウントしていきます。
なので、
・1~〇
・△~最後のシート
・□~◇（途中の任意のシートＮｏ．）
によってループさせる範囲が変わりますから、その情報は必要です。

ただ私の方では古い手段で書ける部分は何とかいけるかもですが、ＳＯＲＴオブジェクトについては作成・検証ができませんしね。
あとは並び替えようとしている範囲においてまだデータが存在してないシートもあるのでは？
その判定をどこでとるのか、それとも年度末（データ入力が全て完了した後）に実行予定なのでしょうか？
確かに一気に並べ替える訳ですから毎月等でやる必要はないでしょうけど、年度途中での纏めなどしないのかな？って。

この回答へのお礼

お世話になっております。
返信が遅くなり、申し訳ありまえせん。

親身にご教授頂きましてありがとうございます。
条件等、再度見直してみたいと思います。

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

お礼日時：2019/05/24 14:59

No.6 回答者： 高木さんWIN 回答日時： 2019/05/22 18:36

No.5です。

No.4に記載しておけば良かったですね。
マクロの自動記録で出来上がったマクロを実行させたらどうなるかと言う検証はされてましたかね。
仮にそちらで問題なければＳＯＲＴメソッドではなく新しいＳＯＲＴオブジェクトで作成した方が良いのかもですけど、
私の所持しているバージョンでは検証が出来ないのですよ。
理由は掴めないままではありますが。

あと別の確認ですが並び替えるシートはそのブック内の全てのシートですか？
それとも左から何枚目まで（１～２０：実際のシート数は２５でも）とか、後ろの何枚とか纏まってはいませんか？
もし纏まっているならシート名を指定せずインデックスＮｏを使ったループで出来るのですが。。。

この回答へのお礼

こんばんは。

マクロの自動記録度は問題ありませんでした。

シートですが、まとまっています。
>インデックスNo.を使ったループ
→知識不足で申し訳ありません。
どのように修正すれば良いか…。

何度も申し訳ありません。

お礼日時：2019/05/22 22:04

No.5 回答者： 高木さんWIN 回答日時： 2019/05/22 16:11

No.4です。

No.3のお礼についてです

こちらは自作ＰＣでMicrosoftOfficeの新しいのは随分と購入してないんですよね。（主にvisualstudioなどの言語勉強とネットが目的なので）
昔やってた事から回答をしてはみましたが、最近の物がどう変わっているのかもわからないです。
ググると２００７以降でＳＯＲＴメソッドからＳＯＲＴオブジェクトになってるみたいですし、うちに放置されているのは２００３までですから実際に検証とかは無理かな。

＞O列の数字の降順に並べ替えができません。。。

では昇順に並び変わるの？
それともデタラメになるの？

まず最初の範囲を手作業で並び替えたらどうなるのか？
同じように変になるのか、逆にちゃんと並び変わるのか？
ちゃんと並ぶなら
『マクロの自動記録』（名称変わっている？）
http://www.asahi-net.or.jp/~ef2o-inue/vba_k/sub0 …
を使ってどのような作業コードが出来ているか？
http://www.asahi-net.or.jp/~ef2o-inue/vba_k/sub0 …
で最近のバージョンをお持ちの方ならわかるかも。

もしデタラメであるなら前回の質問では数値にカンマが入ってましたけど、あれ自体はセルの書式設定と思ってますが当たってますか？

この回答へのお礼

お世話になっております。

お忙しいところ何度もお手数をおかけして申し訳ございません。

>では昇順に並び変わるの？
>それともデタラメになるの？
→デタラメになります。

>まず最初の範囲を手作業で並び替えたらどうなるのか？
>同じように変になるのか、逆にちゃんと並び変わるのか？
→B5~O63選択し、「データ」→列　最優先されるキー「O列」→大きい順では降順に並べ変えられます。

>ちゃんと並ぶなら
>『マクロの自動記録』（名称変わっている？）
>http://www.asahi-net.or.jp/~ef2o-inue/vba_k/sub0 …
>を使ってどのような作業コードが出来ているか？～
※以下、省略いたします。
→記録したものが下記となります。
Sub Macro1()
Macro1 Macro
Range("B5:O63").Select
ActiveWorkbook.Worksheets("sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("sheet1").Sort.SortFields.Add2 Key:=Range _
("O5:O63"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("sheet1").Sort
.SetRange Range("B5:O63")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

＞もしデタラメであるなら前回の質問では数値にカンマが入ってましたけど、あれ自体はセルの書式設定と思ってますが当たってますか？
→仰る通りはい、数字のカンマは書式設定です。

お礼日時：2019/05/22 16:39

No.4 回答者： 高木さんWIN 回答日時： 2019/05/21 18:37

No.3です。

補足について回答します。

コードについては作成していないので推測での回答にもなりかねませんが、

＞cellRange = Array("B5:P61,O61")

並べ替える基準セルって

＞➁B5:P61,O5

セルＯ５ではないでしょうか？
なので、

cellRange = Array("B5:P61,O5")

このようになると思いますよ。
と言うよりも最初からこの部分はこうなっているのですよね？

＞Q5:AE5,AD5、AF5:AU5,AT5

を Q5:AEx,AD5　AF5:AUy,AT5
xやyはその範囲での最終行に書き換えてください。（或いは全てのグループが同じ最終行になるのですかね？）

この回答へのお礼

こんにちわ。

ご返信が遅れてしまい、申し訳ありません。

>>並べ替える基準セルって
>>➁B5:P61,O5
>セルＯ５ではないでしょうか？
>なので、
>>cellRange = Array("B5:P61,O5")
>このようになると思いますよ。
>と言うよりも最初からこの部分はこうなっているのですよね？
→仰る通り、そのようになっております。
ですが、それで検証してもO列の数字の降順に並べ替えができません。。。
他に何か原因がかんがえられますでしょうか？
本来、高木さんWIN様にお尋ねすることではないかと存じますが
もし何かアドバイスございましたらご教授頂けますと幸いです。

お礼日時：2019/05/22 13:43

No.3 回答者： 高木さんWIN 回答日時： 2019/05/21 16:24

横入りをお許しください。

補足にあるセル範囲について実際のシートと見比べて頂ければわかるかもですが、

＞➁B5:P61,O5、Q5:AE5,AD5、AF5:AU5,AT5、～

最初の並べ替えの範囲は５行目から６１行目なのに対し、それ以降列は複数の範囲になってますがそもそも５行目だけしか指定していません。
推測ですけど並べ替えたいデータ範囲毎に最終行を取得する必要があるのではないでしょうか？
確かにそれとエラー内容とが一致するかは検証しきれてはいませんが、少なくとも１行を並べ替えるって点が気になるものでしたので。
試しに３グループ程実際のデータ最終行で指定し検証されてみるとか如何でしょう。

この回答へのお礼

はじめまして。

ご指摘頂きましてありがとうございます。

>＞➁B5:P61,O5、Q5:AE5,AD5、AF5:AU5,AT5、～
→ご指摘の通りですね。初歩的な間違い、大変失礼いたしました。
一度その部分を修正し、再度検証してみたいと思います。

お礼日時：2019/05/21 16:50

No.2 回答者： fujillin 回答日時： 2019/05/21 14:33

No1です

＞下記、エラーが出てしまいました。
＞実行時エラー’1004’
＞並べ替えの参照が正しくありません。参照が並べ替えるデータ内にあるこ＞とと[最優先されるキー]ボックスが空白でないことを確認してください。
意味は、メッセージの通りでしょうから、
　・指定したキーとする列が、指定範囲の外にある
　・指定したキー列の値が空白である
などに該当するような、不合理な設定をなさっているものと推測されます。

データが空白である可能性が存在するなどであるなら、並べ替え前に、可能な範囲でチェックを行うようにしておく方が良いかも知れませんね。

補足で挙げられている②のデータは、最初のひとつ以外は意味をなしていませんけれど・・・？？？

この回答へのお礼

何度もご教授頂きましてありがとうございます。

エラーについては再度、確認してみます。

>データが空白である可能性が存在するなどであるなら、並べ替え前に、可能な範囲でチェックを行うようにしておく方が良いかも知れませんね。
→アドバイス頂きましてありがとうございます。

>補足で挙げられている②のデータは、最初のひとつ以外は意味をなしていませんけれど・・・？？？
→「B5:P61,O5」のみ有効ということでしょうか？

>sheet1~sheet3の複数シートを対象とし
>B5からP61のO列をキー、Q5~AE５のAD列をキー、AF5~AU5のAT列をキーとして降順>に並べ替えをしたいです。
→私の説明不足で申し訳ありません。
上記質問で、出したい結果が下記としたいのです。

B5からP61のO列をキーで並べ替え(O列が数量の達成額なのでその降順並べ替え)
Q5~AE５のAD列をキーで並べ替え(AD列が売上の達成額なのでその降順で並べ替え)
AF5~AU5のAT列をキーで並べ替え(AT列が粗利の達成額なのでその降順で並べ替え)

なので、それぞれで並べ替えが必要になってきます。

こういった条件では並び替えは難しいでしょうか？
知識不足で申し訳ございません。

お礼日時：2019/05/21 14:47

No.1 回答者： fujillin 回答日時： 2019/05/21 12:47

こんにちは

何となく規則性がありそうだけれど、はっきりとはしないので、配列に定義しておいて、それを参照しながらループする方法での例です。

また、ご質問文中の
＞Q5～AE５のAD列をキー、AF5～AU5のAT列をキー
部分は、間違えだとは思いますが、ひとまずそのままにしてあります。


Sub Sample()
　Dim shts, cellRange
　Dim sh As Long, rg As Long, ary

　shts = Array("Sheet1", "Sheet2", "Sheet3")
　cellRange = Array("B5:O61,O5", "Q5:AE5,AD5", "AF5:AU5,AT5")

　For sh = LBound(shts) To UBound(shts)
　　For rg = LBound(cellRange) To UBound(cellRange)
　　　ary = Split(cellRange(rg), ",")
　　　Worksheets(shts(sh)).Range(ary(0)).Sort _
　　　　Key1:=Worksheets(shts(sh)).Range(ary(1)), order1:=xlDescending
　　Next rg
　Next sh
End Sub

この回答へのお礼

こんにちわ。

今回もご教授頂きましてありがとうございます。

>＞Q5～AE５のAD列をキー、AF5～AU5のAT列をキー
>部分は、間違えだとは思いますが、ひとまずそのままにしてあります。
→大変失礼いたしました。
本来、対象範囲の1列前をキーとしたい為、「B5からO61のO列をキー」が誤っており
正しくは「B5からP61のO列をキー」となります。
記載間違い、ご指摘頂きましてありがとうございます。
ただ、その条件ではない対象範囲(対象列数が不規則な為)もある為
ご教授頂きましたマクロを私の環境に当てはめて、修正しました。
そのマクロで実行しましたところ下記、エラーが出てしまいました。

実行時エラー’1004’
並べ替えの参照が正しくありません。参照が並べ替えるデータ内にあることと[最優先されるキー]ボックスが空白でないことを確認してください。

文字数制限がある為、修正しましたモジュールは補足に記載させて頂きます。
恐れ入りますが、再度ご教授頂けますと幸いです。

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

お礼日時：2019/05/21 14:01