エクセル 大量のシートから、指定したセルをコピーして、別のシートにまとめて貼り付ける

エクセルで、１ブックの中に、100枚のシートがあります。

この１００シートの『セルB12』だけをそれぞれ切り取って、

新しい『sheet1のA1』に、１００セル分貼り付けたいのですが、

VBAでやるしかないでしょうか？

VBAでやるしかないとしたら、コードを記載して頂けたら助かります。

宜しくお願いいたします。

No.8 回答者： goomania 回答日時： 2023/11/07 23:38

No.7です。

大変失礼しました。前回回答の数式中に

切取り対象り最右シート名!

という部分が何回か登場しますが、お気づきのように、これは誤りです。正しくは

切取り対象の最右シート名!

です。

お詫びして訂正いたします。

No.7 回答者： goomania 回答日時： 2023/11/07 23:14

ご質問の内容を忠実に実行するとなると、No.3さん、No.5さんの回答で解決なのですが、No.1さんのご指摘のように、1つのセルに100セル分の内容を詰め込んでも、100セル分を連結することで新たな情報が得られる場合を除き、ただただ判りにくくなるだけです。

というわけで、ご質問者のご希望が本当にご質問の説明文のとおりなのか疑問です。

勝手な推測ではありますが、仮に、
>『sheet1のA1』に、１００セル分貼り付けたい
というご説明が、実は
>『sheet1のA1以下』に、１００セル分貼り付けたい
という意図だとすると、
TEXTSPLIT関数の使えるバージョンなら、sheet1のA1に

=TEXTSPLIT(TEXTJOIN( ",", TRUE, 切取り対象の最左シート名:切取り対象り最右シート名!B12),,",")

とすれば、スピル機能により、A1セル以下に切り取った各シートのB12の内容の一覧が表示されます。

TEXTSPLIT関数の使えないバージョンの場合は、sheet1のA1に

=TRIM(MID(SUBSTITUTE(TEXTJOIN( ",", TRUE, 切取り対象の最左シート名:切取り対象り最右シート名!$B$12),",",REPT(" ",LEN(TEXTJOIN( ",", TRUE, 切取り対象の最左シート名:切取り対象り最右シート名!$B$12)))),(ROW(A1)-1)*LEN(TEXTJOIN( ",", TRUE, 切取り対象の最左シート名:切取り対象り最右シート名!$B$12))+1,LEN(TEXTJOIN( ",", TRUE, 切取り対象の最左シート名:切取り対象り最右シート名!$B$12))))

を入れて、下方向へフィルコピーするということになると思うのですが、勝手な推測ですので、当たっているかどうかは全くわかりません。

No.6 回答者： 銀鱗 回答日時： 2023/11/04 13:59

こんな感じになる

No.5 回答者： 銀鱗 回答日時： 2023/11/04 13:54

(´・ω・`) なんか難しいことを言ってる気がするんだけど、

　=TEXTJOIN( "," , TRUE , Sheet1:Sheet100!B12 )

これで良いジャン。
カンマ区切りでA1セルにB12セルの値を並べてくれます。

・・・

なお、空白セルは無視する設定にしていますので、
空白セルは空白セルで
　…,,…
のようにカンマが2つ並んだ方が良いなら数式中の「TRUE」を「FALSE」にしてください。

なお、シートは連続して並んでいる必要がありますが、シート名は任意でOK。
一番左のシート名を
　Sheet1
一番右のシート名を
　Sheet100
と置き換えればOK。

一番左のシートを「ここから」
一番右のシートを「ここまで」
　=TEXTJOIN(",",TRUE,ここから:ここまで!B12)
としても大丈夫。

なお、何も記入していないシートを「ここから」「ここまで」として
表示させたいシートを挟み込むようにすれば、範囲を指定して表示させるなんてこともできます。
（これをやる場合は、空白を無視する設定…例に挙げた数式…にすることを勧めます）

・・・

これ、3D計算（通称「串刺し計算」）と呼ばれるものです。
普通は合計値を求めるときに使われますが、このように文字列を結合することもできる。

No.4 回答者： ssawatake 回答日時： 2023/11/04 13:36

>>新しい『sheet1のA1』に、１００セル分貼り付けたいのですが、

新しい『sheet1のA1～A100』に、
と言えないのかい？

No.3 回答者： enunokokoro 回答日時： 2023/11/04 13:36

No.2の回答者です。

100セル分を一つのセルに結合したいということなら、
　=TEXTJOIN("",,sheet1:sheet100!B12)
とかで対応するなどで可能かと。

それとNo.2の回答の一部を修正しておきます。
　=INDIRECT("Sheet" & ROW() & "!B12")
B12セルを参照するはずがB2と記載していましたね。

No.2 回答者： enunokokoro 回答日時： 2023/11/04 13:22

INDIRECT関数で可能ですよ。

https://hoshino-wp.com/excel-indirect/
シート名が「sheet1」のようなものなら、以下で対応。
　=INDIRECT("Sheet" & ROW() & "!B2")

どうしてもVBAで処理したいなら他の回答をお待ちください。

No.1 回答者： ssawatake 回答日時： 2023/11/04 13:10

『sheet1のA1』は1個のセル。

そこへ100セル分を貼り付ける、という事の意味(何をしたいのか)が解らない。