エクセルでマクロを使用してシート２の表から行単位でシート１の表の指定位

質問

エクセルでマクロを使用してシート２の表から行単位でシート１の表の指定位置に値を表示させたい

シート２の
Ａ列に番号（連番）
Ｂ列に商品名（数字とアルファベットの組み合わせ）
Ｃ列に仕入先（数字４桁）
Ｄ列にメーカー（数字４桁）
Ｅ列に区分（数字とアルファベットの組み合わせで３ケタ）
が入っていて約５，０００行のデータが有ります。

シート１にレイアウトした表があって
シート１のＢ４にシート２のＡ２の値（番号連番）
シート１のＢ５にシート２のＢ２の値（商品名）
シート１のＢ６にシート２のＣ２の値（仕入先）
シート１のＢ７にシート２のＤ２の値（メーカー）
シート１のＢ８にシート２のＥ２の値（区分）
が表示されるようにしてあります。

マクロを利用して、作成したシート１のボタンを押すと
シート１のセルＢ４の番号欄が１から２（シート２のＡ３の値）に繰り上がり、
シート１の表のＡ列の番号と同じ行のそれぞれの値を
上記の規則で表示させるようにできますか？

シート１のＢ４が２になるとシート１の３行目をさしますので
シート１の表には、
シート１のＢ４にシート２のＡ３の値（連番）
シート１のＢ５にシート２のＢ３の値（商品名）
シート１のＢ６にシート２のＣ３の値（仕入先）
シート１のＢ７にシート２のＤ３の値（メーカー）
シート１のＢ８にシート２のＥ３の値（区分）
が表示されます。

シート１のボタンをクリックするごとにＢ４のセルは３，４，５，６と繰り上がり
シート２のＡ列の番号と一致した行のそれぞれの値を表示させていく。

回答No.1

tom04 · ベストアンサー

こんばんは！
参考になるかどうか判りませんが・・・
ごく単純にやってみました。

↓の画像でSheet1にコマンドボタンを一つ貼り付けます。

デザインモードで貼り付けたコマンドボタン上でダブルクリックし
↓のコードを貼り付けてみてください。

Private Sub CommandButton1_Click()
Cells(4, 2) = Cells(4, 2) + 1
If Cells(4, 2) = Worksheets("sheet2").Cells(Rows.Count, 1).End(xlUp).Row Then
Cells(4, 2) = 1
End If
End Sub

そして、Sheet1のB5セルに
=IF(B4="","",VLOOKUP($B$4,Sheet2!A:E,ROW(A2),0))

という数式を入れ、オートフィルB8セルまで下へコピーすると
画像のような感じになります。

これで何とか希望に近い形にならないでしょうか？

以上、参考になれば良いのですが
他に良い方法があれば読み流してくださいね。m(__)m

お礼

素晴らしいです。
思っていた物が完璧に出来あがりました。
社内でも「そんなの出来ない」と皆に反対されていましたが
完成品を見せたら、みなびっくりしてました。
特に数字の繰上げ。みんなにも
「ここ（番号）は手入力で１，２，３，４と繰り上げていくしか無いだろう。
それなら後はVLOOKUPで簡単だけど」
と言われてました。
感謝感謝です。
この後、このシート１のセルＣ４に
エクセルにアドインしたソフトでＢ列の値からバーコードを生成するように
関数式を入力しましたがそれもうまく出来ました。
クリックするごとにバーコードのオブジェクトもＢ列の値の変化に伴いキチンと変化します。
本当にありがとうございました。

回答No.2

imogasi

質問は丁寧で長いがが、ボタンをクリックするごとに、シート２の１行ずつ下のデータを順に、シート１
の項目を指定行に表示したいということか。
質問表現を工夫してほしい。文章で要点を併用して表現することも望ましい。
ーー
もしそうなら、クリックから次のクリックを関連付けることが難しく、PUBLIC変数でも使わないとダメと思うので、やや難しく好ましくない要求だが、こういう課題なのか？？。
普通はシート１に表示したい連番でも入力して、クリックし、シート２からその連番を元にデータ行をさがし、シート１に表示するのが素直。
質問のままだと
例データ　Sheet2
氏名 住所 従業員数
山田 東京都 23
小島 神奈川県 6
野島 千葉県 35

標準モジュールに
Public n
Sub test01()
n = 1
End Sub
Sheet1にボタンを貼り付け、そのシートモジュールに
チェックとか一切してなくて、モデル的な説明を断っておく。
Private Sub CommandButton1_Click()
n = n + 1
Dim sh1, sh2
Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")
sh1.Range("B3") = sh2.Cells(n, "A")
sh1.Range("B5") = sh2.Cells(n, "B")
sh1.Range("B7") = sh2.Cells(n, "C")
End Sub
この初期化はTest01の実行しかなく、操作性が良くない。もうひとつの初期化用のボタンを貼り付ければ簡単だが。