Excelマクロ：複数シート同座標セルの値を、連番で一覧に参照したい

質問

マクロは素人なのでご教授お願いします。

Excelブック一つにシートが20あったとします。

○20シート全ての同座標セルに、ある商品の「合計」と「個数」の値がある
○「集計」シートが別にあり、「sheet1」から「sheet20」までを連番で一覧にしてある
該当のセルに「合計」と「個数」の値を参照させる

＜「集計」シート　結果例＞

　　　A　　　　　　B　　　　　 　C　
1　シート名　　合計数　　　個数(表見出し)
2　sheet1　　　　78　　　　　　5
3　sheet2　　　　90　　　　　 17
4　sheet3　　　 112　　　　　224

また、以前まったくの逆の条件で質問をしまして、良回答いただきました。

http://oshiete1.goo.ne.jp/kotaeru.php3?q=715150

参考までにご覧になってみて下さい。
よろしくお願い致します。

回答No.3

imogasi · ベストアンサー

#2です。
＞全sheet数はどこにも記載されていないがいいのか
ＦｏｒＥａｃｈ文を使ってますが、本回答の場合Ｓｈをワークシートに定義してＳｅｔしているので、ワークシート
１つ１つ（Ｅａｃｈ）のすべてに付いて、当たってくれます。それをＳｈ．○○で捉えて、処理を行います。
しかし合計を出すシートだけは、加算の対象からはずさないといけないので、If sh.Name = "sheet6" Then で外しています。
＞なんで行数を「i」に置き換えているのか。
Worksheets("sheet6").Cells(1, "A") = "合計" '項目見出し 、で１行目には見出し漢字をセットしたのですから
計数は第２行目以下に書きこまないといけません。
だからスタート行を指定するために、ｉ＝２としてます。
本番の表の構成から、第３行目からスタートもあり得ます。
＞なんで行数を「i」に置き換えているのか。
持っていく先のシートの指定行は、１データセットしたら
次は１行下へセットしないといけません。だからｉを変数にして、１づつ増やしていくわけです。そうしないと同じ
セルに次々書いて、最後のものしか残りませんから。
＞シート名の連番が飛び飛びになったり
全部のシートでなく、シート名を指定して処理をしたいときもあるでしょう。その場合は人間が指定してやらないといけません。実行時に、Ｉｎｐｕｔｂｏｘで聞いてやるとか、プログラムコードの中に、配列でシート名を定義します。
t=Ａrray("aaa","bb","c","計数１")（Ａｒｒａｙ関数）
(aaa,bb,等がシート名）でt(0)でシート名aaaを使えます。

お礼

imogasiさん、再びの詳しい回答ありがとうございます。

本当に助かりました。
これが出来るのと出来ないのとでは、毎日のお仕事の効率に影響があったんです。

これを機に、私自身もマクロのお勉強をしないといけない、とつくづく思いました。

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

回答No.2

imogasi

ＶＢＥの標準モジュールに下記を貼りつける。
集約シートはＳｈｅｅｔ６と仮定してます。
合計数だけを取り上げてます。
Sub test02()
Dim sh As Worksheet
Worksheets("sheet6").Cells(1, "A") = "合計" '項目見出し
i = 2
For Each sh In Worksheets
If sh.Name = "sheet6" Then '合計シートを除いた全シートにつき
Else
Worksheets("sheet6").Cells(i, "A") = sh.Name 'シート名
Worksheets("sheet6").Cells(i, "B") = sh.Range("b4") '
'各シートの合計セル
i = i + 1 '直下行を指す
End If
Next
End Sub
（変更すべき箇所）
　　　　　本テスト６シート－＞２０シート
　　　　　集約シートＳｈｅｅｔ６－＞　合計シート名は
　　　　　項目名－＞　合計数のほかに個数も増やす
　　　　　合計のあるセル－＞　Ｂ４で良いか？
　　　　　シート名が漢字名とか飛び飛びのシートの場合
　　　　　（本例と離れるが）
等の時の修正のし方わかりますかね。

お礼

imogasiさん、回答ありがとうございます。

出来ました！
以前の質問に加えて、詳しい回答嬉しいです。

ただ出来上がるのに時間かかりました。
詳しく回答いただいたのにもかかわらず、どの場所を、私のブック仕様に変更したらいいのか、イマイチ分からず、いろんなところをいじってました。

特に、
○全sheet数はどこにも記載されていないがいいのか
○なんで行数を「i」に置き換えているのか。
＞i = 2

などなどです。
もちろん、シート名の連番が飛び飛びになったり、「時の修正って何？」状態ですから、その対処も分かりません(-_-;)

これらVBにとっては初歩の初歩でしょうが、しばらく締め切らずにいるので、もしよろしければ、お時間のある時にお教えくださいｍ( _ _ )ｍ

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

回答No.1

ki-aaa

これで如何ですか。

B2セルとC2セルに次の式を入れて、下方向にコピーします。
B2: =INDIRECT(A2&"!B2") C2: =INDIRECT(A2&"!C2")

合計数はsheet1・・・sheet3のB2セルにあります。

お礼

ki-aaaさん、さっそくの回答ありがとうございます。

試してみたんですが、「＃REF」エラーが出てしまい上手くいきませんでした。
マクロに加えて関数もダメな私には何がいけないのかさっぱりです(^^;)

でも、関数でもやろうと思えば出来るんですね。
一つお勉強になりました。

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