エクセルで複数シートの同じ位置にあるセルを一枚のシートにリンク貼り付けしたい。

質問

初めて質問いたします。

エクセルのブックの中に100枚のシートがあります。
全てのシートの同じ位置のセルのデータ(数字と文字)を
一枚のシートにリンク貼り付けでまとめたいのですが
マクロで　できるのでしょうか。

どのように書けばいいのか分からず困っています。
よろしくお願いいたします。

回答No.4

noname#52504 · ベストアンサー

・どの位置のセルを対象とするのか
・どのシートにまとめるのか
がわかりませんし、
・値の転記ではなくリンク貼り付けで
ということなので、
いっそ下記のような関数を定義した方が便利かもしれません。
（少し重くなる可能性がありますが…）

動作の概要
　第１引数で指定した番号のシートの、
　第２引数で指定したセルと同位置にあるセルの参照を返す。

'----------------------------↓　ココカラ　↓----------------------------------
Function Sample(ByVal シート As Integer, ByVal セル As Range) As Range
　Application.Volatile
　Set Sample = Worksheets(シート).Range(セル.Address)
End Function
'----------------------------↑　ココマデ　↑----------------------------------

例１
　任意のシートの任意のセルを
　　=Sample(3,B4)
　とすると、３番目のシートのB4セルの参照が返る。

例２
　任意のシートの任意の行のＡ列目を
　　=Sample(COLUMN(),$F$3)
　として、右方にフィルすると、各シートのF3セルの参照が順に返る。

※シート番号は、１始まりで左から数えた番号。
※非表示のシートも数に含める。
※グラフシートは数に含めない。

以上ご参考まで。

回答No.3

noname#79209

注意を忘れていました。
このマクロを実行する際は、必ずシート名を羅列するシートをアクティブにした状態で行ってください。
別のシート上で実行すると、そのシート上に書き込まれるので、
必要なデータが上書きされてしまいます。

これを防ぐには、羅列するシート名がSheet1だとすれば、
Sheets("Sheet1").Select
を、
Dim x As Integer
の次の行に挿入してください。

回答No.2

noname#79209

マクロ(VBA)を使って良いなら
http://akubizzz.hp.infoseek.co.jp/sub171.html
に以下のマクロがありました。

Sub シート名取得()
Dim thisSheet As Object
Dim x As Integer
'-----vizzarによる追加　ここから
Columns("A:A").Select
Selection.ClearContents
'-----vizzarによる追加　ここまで
x = 1
For Each thisSheet In Sheets
If thisSheet.Name <> ActiveSheet.Name Then
Cells(x, 1).Value = thisSheet.Name
x = x + 1
End If
Next
'-----vizzarによる追加　ここから
Cells(1, 1).Select
'-----vizzarによる追加　ここまで
End Sub

シート名を羅列したいシートを選びます（或いは追加）。
Alt+F8を押す。
マクロ名ボックスに「シート名取得」と入力し、「作成」ボタンをクリック。
開いたボックス内に

Sub シート名取得()

End Sub

と表示されているはずなので、
この空いている行に、上記のコードをコピぺします。
ここで、二行目からリストさせたいので、
x = 1
を
x = 2
に変更します。
もし、「Sub シート名取得()」と「End Sub」がそれぞれ二重になってしまったら、
一方を削除して、上記コードと同様になるようにしてください。
その後、表示されている、「Visual Basic」のウインドを右上の「×」を押して閉じます。
これで、マクロが設定できましたので、必要な都度、Alt+F8で出てくるパネルで「実行」ボタンを押せばワークシート名がリストされます。

後は、INDIRECT関数で、前回コメントした数式で各シートのセルを参照できます。

お礼

お礼が遅くなってすみません。

こちらの方法でやってみました。
INDIRECT関数で参照したいセル番号の前に"！"を入力すると言うことに
なかなか気づかず苦戦しましたがなんとかできました。

丁寧に教えていただきありがとうございました。

これで今月の集計から時間を短縮することが出来ます。
またよろしくお願いいたします。

回答No.1

noname#79209

要は、まとめる側のシートの100行分の各行に、他のシートの同じ位置のセルをまとめたいと言うことでしょうか？
たとえば、
Sheet101のB2にSheet1のK35、C2にSheet1のR58
Sheet101のB3にSheet2のK35、C3にSheet2のR58
といった具合でしょうか？
もしそうなら、そしてシート名がSheet1～Sheet100なら、
まとめるシートのA列をワーク列、さらに一行目は表題に使うとして、

A2に
="Sheet"&(ROW()-1)&"!"
（もし、Sheet35からSheet134なら、「="Sheet"&(ROW()+33)&"!"」）
B2に
=INDIRECT($A1&"K35")
C2に
=INDIRECT($A1&"R58")
として、下に99行分コピーすればOKです。

各シート名が非連続だったり単なる文字だったら、面倒ですがA列に各シート名を記入していってください。
B列とC列の数式はそのままで構いません。

補足

記入はここでいいのでしょうか。。。
回答ありがとうございます。

各シートには別々の名前がついています。
このシートは月ごとに増減があるため、シート名をひとつずつ書くのは難しいです。

左端のシートから、ひとつ右のシート　またひとつ右のシート
のように順番に読み込むことはできないのでしょうか。