マクロで、並び順・行列を入れ替えて貼り付け

質問

複数の棚式の表から、一つのシートに並び順・行列を変えて貼り付けて
集計表を作りたいと思っています。
マクロは記録から意味を調べたり、範囲の応用をするくらいの初心者です。

Sheet1が集計表、Sheet2～5がそれぞれの現場での棚の表です。

<Sheet1>　集計表
　|　　Ａ　　Ｂ　　　　Ｃ　　　　Ｄ　　　Ｅ　　Ｆ　　Ｇ　　Ｈ　　Ｉ
-----------------------------------------------------------
1|　　連番　店名　お勧め　品名　金額　棚番　棚行　棚列　備考　
2|　 　1　　　亜　　　●　　　ああ　　50　　1　　2　　　2　　aa
3| 　　2　　　亜　　　　　　　いい　　80　　1　　2　　　3　　
4| 　　3　　　亜　　　●　　　うう　　30　　2　　3　　　1　　bb
5| 　　4　　　亜　　　　　　　ええ　　20　　3　　1　　　1
6| 　　5　　　亜　　　　　　　おお　　90　　4　　2　　　2

<Sheet2>　棚1
　|　　Ａ　　Ｂ　　　Ｃ　　　Ｄ　　　Ｅ　
--------------------------------------------
1|　店名　　亜　
2|　品名　　　　　ああ　　　　他
3|　お勧め　　　　●　　　　　　　　　
4|　金額　　　　　50　　　　　　　　
5|　備考　　　　　aa　
6|　品名 　 いい　
7|　お勧め　　　　　　　　　　　　　　　　　
8|　金額　　　　　　　　　　　80　 　 他
9|　備考　　　　　　　　　　　　　　　　　　　　　　　
10|　：　　　　　　　　　　　　　　

<Sheet3>　棚2
<Sheet4>　棚3
<Sheht5>　棚4

以前、逆の方法（集計表→棚）の質問させて頂きました。http://oshiete1.goo.ne.jp/qa3489046.html
他の質問も調べたりして、なんとか応用できないものかと思ったのですが、
恥ずかしながら私のスキルでは対応できません。

今回は棚の表に棚番号、行、列の情報がありません。

現在途中まで組んでみたのは
○空のシートにSheet2!B2:E9をそのまま行列入れ替えで貼り付けて
一つのリストにしてから、改めて集計表に貼り付ける。
○棚番号、行、列はそれぞれ入力、貼り付けする。
と原始的なものです^^；

２つの質問で恐縮ですが、以下のマクロについて
ご指導頂けましたら幸いです。

1．Sheetから棚番号、各シートのセル位置から行列の番号の割り振り。
2．Sheet2～5の表をSheet1の形式に貼り付け。

「他」の部分は後でソートか置き換えで削除するので、
貼り付けの時は残っていても大丈夫です。

ちなみにお勧めや金額は空白の場合もあり、確実に入っているのは品名です。
自分の拙いマクロではSheet1で貼り付ける時に、D列（品名）を参照して、
xlUp、Offsetで左右に移動して貼り付けていました。

分かり辛い説明ですみません。
何かあれば補足させて頂きます。

よろしくお願いいたしますm(_ _)m

回答No.5

Wendy02 · ベストアンサー

こんばんは。

>シート1のA1が空白または「連番」と項目が入っていて、A2以降にデータがない場合

まさか、データがないということは、まったく、予想していなかったです。
　　　If Val(.Cells(i,1).Value )>0 Then　～

　　　End If
として、A列のデータをチェックする程度しか、今は思いつきません。

　With Sh0
　　For i = 2 To .Range("A65536").End(xlUp).Row
　　　If Val(.Cells(i,1).Value )>0 Then
　　　ArLists = Array(.Cells(i, 2).Value, .Cells(i, 3).Value, _
　　　　　　　　　　　.Cells(i, 4).Value, .Cells(i, 5).Value, _
　　　　　　　　　　　.Cells(i, 9).Value)
　　　If IsNumeric(.Cells(i, 6).Value) And _
　　　　 IsNumeric(.Cells(i, 7).Value) And _
　　　　 IsNumeric(.Cells(i, 8).Value) Then
　　　　 ListUp2Sheet Shes(.Cells(i, 6).Value), _
　　　　　　　　　　　　　　　 .Cells(i, 7).Value, _
　　　　　　　　　　　　　　　 .Cells(i, 8).Value, _
　　　　　　　　　　　　　　　　ArLists
　　　End If
　　End If
　　Next i
　　End With

お礼

お礼が遅くなり、大変申し訳ございません。

残念ながら、やはり上手くいきません。。。
現在頂いているマクロもしっかり応用できるように勉強しないと
今後対応ができないので、今のスキルでは取り入れる事が難しそうです。
折角ご丁寧に対応してくださったのに申し訳ございませんでした。
どうもありがとうございました。

回答No.4

Wendy02

こんにちは。

もし、ブレークポイントを置くのでしたら、

●　　 ListUp2Sheet Shes(.Cells(i, 6).Value), _
　　　　　　　　　　　　　　　 .Cells(i, 7).Value, _
　　　　　　　　　　　　　　　 .Cells(i, 8).Value, _
　　　　　　　　　　　　　　　　ArLists

の部分で、ローカルウィンドウをごらんになってみてください。正しく、値が渡されているかです。

もしかしたら、もともと、この部分かもしれませんね。
中抜けというよりも、元の範囲が食い違っている可能性が強いです。

With Sh0　　'←シート1 ですね。

For i = 2 To .Range("A65536").End(xlUp).Row
　　　ArLists = Array(.Cells(i, 2).Value, .Cells(i, 3).Value, _
　　　　　　　　　　　.Cells(i, 4).Value, .Cells(i, 5).Value, _
　　　　　　　　　　　.Cells(i, 9).Value)

今まで、やってきたのですから大きな食い違いというより、おそらくは、コードの最初の設定部分などに問題があることが大きいです。

お礼

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

シート1のA1が空白または「連番」と項目が入っていて、
A2以降にデータがない場合はエラーにならないのですが、
A1に項目、A2～A4に連番を振って稼動した所、

ListUp2Sheet Shes(.Cells(i, 6).Value), _
.Cells(i, 7).Value, _
.Cells(i, 8).Value, _
ArLists

の部分で「インデックスに有効範囲がありません。」と
エラーが出ます。
この部分で問題があるのでしょうか？
度々本当に申し訳ございません。。。

回答No.3

Wendy02

こんばんは。

>棚1の1段目B2:E5、棚1の2段目B6:E9　・・　と続きます。
とありましたが、
「1列5行」＝ユニット
と　表の一覧の方では、２段目　B7:B11, ３段目　B12:B16
となっていましたので、表のほうを優先させました。

つまり、もし、それが違うと、以下の部分を変えなくてはなりません。
　With .Cells((Gyo - 1) * 5 + 2, Retu + 1) '★ここが大事

なお、店名は、一度、入れてしまうと、後は、チェックはされません。
また、渡すデータが増えた場合は、ArLists　に含めていくような形にします。また、その変数を、パラメータ配列にすれば、何個でも、自由に付け加えるようになります。

'--------------------------------------------------

'標準モジュール

Sub TestMacro2()
　　Dim Sh0 As Worksheet
　　Dim Sh1 As Worksheet
　　Dim Sh2 As Worksheet
　　Dim Sh3 As Worksheet
　　Dim Sh4 As Worksheet
　　Dim i As Integer
　　Dim Shes As New Collection
　　Dim ArLists As Variant

　　Dim LastRow As Long
　　Set Sh0 = Worksheets("Sheet1") '原本
　　Set Sh1 = Worksheets("Sheet2") '1
　　Set Sh2 = Worksheets("Sheet3") '2
　　Set Sh3 = Worksheets("Sheet4") '3
　　Set Sh4 = Worksheets("Sheet5") '4

　　Shes.Add Sh1
　　Shes.Add Sh2
　　Shes.Add Sh3
　　Shes.Add Sh4

'1.連番, 2.店名, 3.お勧め, 4.品名, 5.金額, 6.棚番, 7.棚行, 8.棚列, 9.備考
　　With Sh0
　　For i = 2 To .Range("A65536").End(xlUp).Row
　　　ArLists = Array(.Cells(i, 2).Value, .Cells(i, 3).Value, _
　　　　　　　　　　　.Cells(i, 4).Value, .Cells(i, 5).Value, _
　　　　　　　　　　　.Cells(i, 9).Value)
　　　If IsNumeric(.Cells(i, 6).Value) And _
　　　　 IsNumeric(.Cells(i, 7).Value) And _
　　　　 IsNumeric(.Cells(i, 8).Value) Then
　　　　 ListUp2Sheet Shes(.Cells(i, 6).Value), _
　　　　　　　　　　　　　　　 .Cells(i, 7).Value, _
　　　　　　　　　　　　　　　 .Cells(i, 8).Value, _
　　　　　　　　　　　　　　　　ArLists
　　　End If
　　Next i
　　End With
　　Set Sh0 = Nothing: Set Sh1 = Nothing
　　Set Sh2 = Nothing: Set Sh3 = Nothing
　　Set Sh4 = Nothing
End Sub
Private Sub ListUp2Sheet(sh As Worksheet, ByVal Gyo As Integer, ByVal Retu As Integer, ar As Variant)
'1.品名,2.お勧め,3.金額,4.備考"
　With sh
　　If .Cells(1, 2).Value = "" Then .Cells(1, 2).Value = ar(0)
　　With .Cells((Gyo - 1) * 5 + 2, Retu + 1) '★ここが大事
　　　.Offset(0, 0).Value = ar(2) '品名
　　　.Offset(1, 0).Value = ar(1) 'お勧め
　　　.Offset(2, 0).Value = ar(3) '金額
　　　.Offset(3, 0).Value = ar(4) '備考
　　End With
　End With
End Sub

お礼

お礼が遅くなり、申し訳ございません。

マクロを稼動してみたところ、エラーは発生しないのですが、
商品リストのシートにデータが引っ張られません。
「Private Sub ListUp2sheet・・・」にブレークポイントを
置いたのですが、中段されずにマクロが終わっているので
「Private Sub ListUp2sheet・・・」以降を参照していないようです。
色々と試して見たのですが上手くいかず、
私のスキルでは対応できないようです。。。
折角お時間を割いて作って頂いたのに、申し訳ございません。
ありがとうございました。

補足

素晴らしいマクロをありがとうございます！！！
実際の範囲が違うので、応用してまたご報告させて頂きます。
少しお時間を頂くかもしれませんが。。。^^；
取り急ぎ御礼までm(_ _)m

回答No.2

Wendy02

こんにちは。

前回の作者です。

集計表

1|　　連番　店名　お勧め　品名　　　金額　棚番　棚行　棚列　備考　
2|　 　1　　　亜　　●　　ああ　　　50　　1　　　　2　　　2　　aa
3| 　　2　　　亜　　　　　いい　　　80　　1　　　　2　　　3　　

これが、
<Sheet2>　棚1
　|　　Ａ　　Ｂ　　　Ｃ　　　Ｄ　　　Ｅ　
--------------------------------------------

マクロ自体は、そんなに難しくないように思いますが、それぞれの数字の意味がどういう対応しているのか、読めないです。

「棚割表の1つのシートに1枠 =5行×10列(仮)」

まず、この意味が分からないです。

1列目は、項目行であり、B1 に、店名が入れるのですから、
それぞれ、１行、1列ずれるわけで、左端上を、「B2」を記入の起点とするかと思います。

『棚行　2　　棚列　2』
は、

「C2:C5」（画面ではそう見えます）

これが分からないです。『棚行　2　　棚列　2』　と、それを結びつけるためには、どうしたらよいのか分かりません。

『棚行　2　　棚列　3』

これは、もっと分かりません。バラバラに入れているように見えます。

品名「いい」が、B6 ?
品名の「いい」の金額が、D8
のように見えます。

言い換えると、私は、このようにすれば、意味は分かるとは思いますが、どうも意味が違うようですね。

棚番＝Sheet名（または順番）　「１列４行」＝ユニット
====================================================
棚行:1,棚列:1　　　 棚行:1,棚列:2　 棚行:1,棚列:3
---------------------------------------------------
　B2:B5　　　　　　　 C2:C5　　　　　 D2:D5
====================================================
棚行:2,棚列:1　　　棚行:2,棚列:2　　 棚行:2,棚列:3
------------------------------------------------
　B6:B9　　　　　　　 C6:C9　　　　　　 D6:D9
====================================================
棚行:3,棚列:1　　　　棚行:3,棚列:1　　棚行:3,棚列:1
---------------------------------------------------
　B10:B14　　　　　　　C10:C14　　　　　D10:D14
---------------------------------------------------

アドレスで説明すればはっきりするのですが、絵的に、ずれているのか、位置関係が読めません。いずれにしても、マクロで解決するなら、数学的に位置関係を決定をしなければなりません。

>「他」の部分は後でソートか置き換えで削除するので、
>貼り付けの時は残っていても大丈夫です。

それに「他」とは、中途半端な位置にきているようですが、一体どこから来るものなのでしょうか？

補足

ご回答ありがとうございます。
前回も大変お世話になりました。

＞「棚割表の1つのシートに1枠 =5行×10列(仮)」
＞まず、この意味が分からないです。

ロジック（しかもバラバラな・・・）と＃１の補足で
お伝えしているのが違うので、余計に混乱させてしまったかもしれません。
申し訳ありません。

まずSheet2～5の1行目は、店名、日付、時間等が入るタイトル的な行に
なります。2行目以降に店名等は出てきません。
項目行はなく、ロジックで言うとB2:E5が1段の棚になるので、
それを枠と表現させて頂きました。
棚1の1段目B2:E5、棚1の2段目B6:E9　・・　と続きます。
実際の1段の項目数、列数は異なりますので、＃1の補足では
キリがよい所で棚の1段(1枠)＝5行×10列　とさせて頂きました。
＃この先、棚の1段＝5行×10列として、補足させて頂きます。

Wendy02さんがアドレスで表してくださったのが正にその通りです。

棚番＝Sheet順番-1(Sheet5には棚4なので4)　「1列5行」＝ユニット

A1　店名　　C1　日付　　E1 時間
====================================================
棚行:1,棚列:1　　　 棚行:1,棚列:2　 棚行:1,棚列:3
---------------------------------------------------
　B2:B6　　　　　　　 C2:C6　　　　　 D2:D6
====================================================
棚行:2,棚列:1　　　棚行:2,棚列:2　　 棚行:2,棚列:3
------------------------------------------------
　B7:B11　　　　　　　 C7:C11　　　　 D7:D11
====================================================
棚行:3,棚列:1　　　　棚行:3,棚列:2　　棚行:3,棚列:3
---------------------------------------------------
　B12:B16　　　　　　　C12:C16　　　　　D12:D16
---------------------------------------------------

「他」は棚割表で入っている場合があり（全てではありません）、
商品名の行には「商品名」、「他」、「空白」が混在しています。

実際のデータでは空白も含め400点（400ユニット）なので、
現在は棚番号、棚行、棚列は指定して割り付ける形にしています。
範囲が変わる事も考えられるので、その点を
Sheetの順番、セルの位置から値を取れないかと思った次第です。

以上の補足でお分かり頂けましたでしょうか？
説明が上手くできず申し訳ありません。
よろしくお願いいたします。

回答No.1

imogasi

確認と補足を願います。下記Ａ，Ｂ，Ｃの点。
Ｓｈｅｅｔ１の名前「集計表」は集計して内容なので「集約表」が適当かとおもう。
ーーー
○は対応項目あり、従って代入、Ｘは対応項目なし、右記の処理をする、という意味。
Ｓｈｅｅｔ１ーーーーＳｈｅｅｔ２－Ｓｈｅｅｔ５・・
連番ーーーーーーーーＸ　　＜ーー自動
店番ーーーーーーーー○
お勧めーーーーーーー○
品名ーーーーーーーー○
金額ーーーーーーーー○
備考ーーーーーーーー○
棚番ーーーーーーーーＸ　　＜ーー自動
棚行ーーーーーーーーＸ　　＜＝＝空白を入れる（Ａ）
棚列ーーーーーーーーＸ　　＜＝＝空白を入れる（Ｂ）
ーー
（Ａ）（Ｂ）は空白でよいか
＝＝＝＝
例えばＳｈｅｅｔ２で
品名の出現する行を察知するのは、文字列では何も無いですね
５行おきに規則正しく品名が出てきて、終わり行まで、狂うことは無いですね？（Ｃ）
＝＝＝
以上Ａ，Ｂ，Ｃについて補足お願いします。

補足

ご回答ありがとうございました。
補足させて頂きます。

まず、おっしゃる通り集計表は不適当ですね^_^；

棚割表の1つのシートに1枠5行×10列(仮)として、10枠(仮)あります。
途中、余分な行見出し等は入っていませんので、
(C)のお答えとしては、狂うことはありません。

そのシートが4つあるので、品名は400まで取れますが、
実際にデータが入っているのは最大100です。

(A)(B)棚行、棚列は必要です。
セル位置から情報が取れないのであれば、
棚割表からブランクを含めて5行×10列を貼り付けた後に、
任意で値を組み込まないといけないかと思っています。
（現在のマクロでは、Sheet1のフォーマットに
棚番、棚行、棚列を割り振り、その脇に貼り付けています）

ちなみにお勧めをキーにソートをかけて、
連番は、お勧め以外の品で1～、お勧めで101～となります。

補足としては以上でよろしいでしょうか？
何かございましたら、また補足させて頂きますので、
どうぞ宜しくお願いいたしますm(_ _)m