Excelで、ファイル名、シート名を指定するとその内容を同ファイル別シートに値だけ貼り付ける方法

Excelで、画像のようにA列にファイル名（ファイル名（仮）：Aファイル）B列にシート名（シート名（仮）：B）を記載した時に、同ファイルの別シートにAファイルのセルデータを値として貼り付けたいです。
ただし、Aファイルは100ファイルほどあり、各ファイルのシートBはすべて同じフォーマット（行数、列数、列タイトルは同じです）で貼り付け範囲はC4：AK50はすべてのシートで同じです。
Aファイルの値は、C4：AK50に、Bファイルの値はその次の行から（C51：AK97）に転記される感じです。以下、下行にCファイル、Dファイル～と続く動きです。
出来上がった転記先のシートのA列には各行にファイル名、B列の各行にシート名が入れば、各行の値がどのファイルのどのシート情報かわかるようになると助かります。
これらをマクロなどで、自動処理できるようにできませんでしょうか？

質問者からの補足コメント

• 

  tatsumaru77さま
  作成頂いたマクロ、完璧です！！実装、マクロ実行後、希望したものができました。素晴らしいですね。実は私はNPOで働いていて今回tatsumaru77さまがマクロを作成頂けたことで、多くの方に対し社会貢献を実施する為の事務処理の効率化が図れます。
  tatsumaru77さまの貴重な時間を頂き、感謝感謝です。ありがとうございました。

  No.6の回答に寄せられた補足コメントです。 補足日時：2024/02/28 10:13

No.6 ベストアンサー 回答者： tatsumaru77 回答日時： 2024/02/27 17:39

以下のマクロを標準モジュールに登録してください。

Option Explicit
Public Sub DATA集約()
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim wb As Workbook
Dim ws As Worksheet
Dim maxrow As Long
Dim row1 As Long
Dim row2 As Long
Dim bookName As String
Dim sheetName As String
Dim path As String
Dim wrng As String
Application.ScreenUpdating = False
Set sh1 = Worksheets("DATA集約対象先")
Set sh2 = Worksheets("DATA集約")
sh2.Rows("4:" & Rows.Count).ClearContents
maxrow = sh1.Cells(Rows.Count, 1).End(xlUp).row '1列目の最終行を求める
row2 = 4
For row1 = 2 To maxrow
bookName = sh1.Cells(row1, 1).Value
sheetName = sh1.Cells(row1, 2).Value
path = ThisWorkbook.path & "\" & bookName
Set wb = Workbooks.Open(path, ReadOnly:=True)
Set ws = wb.Worksheets(sheetName)
wrng = "C" & row2 & ":AK" & row2 + 46
sh2.Range(wrng).Value = ws.Range("B4:AJ50").Value
sh2.Cells(row2, "A").Resize(47, 1).Value = bookName
sh2.Cells(row2, "B").Resize(47, 1).Value = sheetName
row2 = row2 + 47
wb.Close
Next
Application.ScreenUpdating = False
End Sub

＞このような具体的な聞き方で、かつ対価無しで回答を得ようとしていることがガイドライン違反であればおっしゃってください
公序良俗に違反していませんので特に問題ないと思います。投稿が不快ならその質問に答えないという回答者の選択肢は尊重すべきと思いますが・・・。

この回答へのお礼

tatsumaru77さま
ありがとうございます、感謝です。外出中で実装はまだできていませんが、明日実装してみます。
VBAは全く書けない、わからない私で、丸投げして申し訳ないです。こういうことができるtatsumaru77さんは凄い！と思いました。しかも無報酬で、、。頭が下がります。私は65歳の爺さんですが、このような知識がある方を尊敬します。
ありがとうございます。実装後、また投稿します

お礼日時：2024/02/27 18:21

No.7 回答者： kanta2006 回答日時： 2024/02/27 17:46

ご自身の知識を高めるためにここを使っていただいているのであれば、具体的内容であってもいいと思うんです。

ただ、誰かに作ってもらって「どうやって動いているのか分からないけど、動作するコードを作ってくれたから、ありがたく使わせてもらおう」というのは、ここの趣旨とちょっと違うと思うんです。

知識を得るために使っていただいているなら、問題ないと思います。

この回答へのお礼

ご指摘ありがとうございます。勉強になりました。いろいろな感じ方をするかたは当然いらっしゃいますね。
今回は助けてくれた方がいたので、本当に助かりました。その方への感謝もさることながら、いつもそのような方がいらっしゃるとは限らないということを認識し、甘え過ぎないようにもしなくてはいけないですね。kanta2006さん、ありがとうございました。

お礼日時：2024/02/27 18:24

No.5 回答者： tatsumaru77 回答日時： 2024/02/27 09:34

不明点です。

１．生成指定シートについて
ファイル名とシート名を指定するシートということですが、
①このシートのシート名は何でしょうか。
②ファイル名とシート名を指定するのは、1行目からですか。
それとも、１行目は見出しで、２行目からでしょうか。
それとも、提示された図のように４行目からでしょうか。
③ファイル名の指定のしかたですが、
フルパスで指定　　　例　C:\sample\Book1.xlsx
ファイル名のみ指定　例　Book1.xlsx
拡張子なしで指定　　例　Book1

上記のどの方法でしょうか。
ファイル名のみ指定及び拡張子なしで指定の場合は、特定のフォルダ（例えばc:\sample等）内に
指定された全てのブックが格納されているという前提になります。
ばらばらのフォルダ内にブックが格納されているなら、フルパスで指定する必要があります。
又、拡張子なしで指定するなら、拡張子は.xlsx前提となります。（Boook.xlsmなどは指定できません）

２．生成先シートについて
①このシートのシート名はなんでしょうか。
②Ａ列に設定するファイル名は、Book1.xlsxのように拡張子を含めますか、
それともBook1のように拡張子を含めませんか。
③ファイルＡの内容をC4:AK50に転記するとのことですが、
転記元の範囲も、C4:AK50で良いのでしょうか。
C1:AK47
A1:AI47
A4:AI50
等が考えられます。

この回答へのお礼

tatsumaru77さま　見ていただいてありがとうございます。kanta2006さんのご指摘のように、「丸投げ」的な私の質問で申し訳ないです。よろしいでしょうか？

tatsumaru77さまのご質問にお答えします。
＊＊＊＊＊
１．生成指定シートについて
ファイル名とシート名を指定するシートということですが、
①このシートのシート名は何でしょうか。

→［DATA集約対象先］としようと思います。

②ファイル名とシート名を指定するのは、1行目からですか。
それとも、１行目は見出しで、２行目からでしょうか。

→［2行目］としようと思います。

③ファイル名の指定のしかたですが、

◯ファイル名のみ指定　例　Book1.xlsx　
→ファイル名+拡張子　です。としようと思います。

上記のどの方法でしょうか。
ファイル名のみ指定及び拡張子なしで指定の場合は、特定のフォルダ（例えばc:\sample等）内に
指定された全てのブックが格納されているという前提になります。
→はい、全てのブックを指定されたフォルダに集約します。フォルダ名は［集約FLD]とします。その同一フォルダ内にマクロをセットしたBookを置くイメージです。

２．生成先シートについて
①このシートのシート名はなんでしょうか。
→［DATA集約］としようと思います。

②Ａ列に設定するファイル名は、Book1.xlsxのように拡張子を含めますか、
それともBook1のように拡張子を含めませんか。
→拡張子を含んで良いと思います。

③ファイルＡの内容をC4:AK50に転記するとのことですが、
転記元の範囲も、C4:AK50で良いのでしょうか。
→転記元はB4:AJ50です。転記先はC4:AK50です。転記先のA列にファイル名、B列にシート名です。シート名は”2024-02”　”2024-03”などとなる予定です。4～47行のデータ転記が行われたあとにファイルBのデータが48行目から始まるイメージです。

このような具体的な聞き方で、かつ対価無しで回答を得ようとしていることがガイドライン違反であればおっしゃってください、、、。私は逆に「対価を払いますので作成ください」はkanta2006さんのおっしゃるように業務発注となるので「教えて！goo」には相応しくない、と思っていました、、、

お礼日時：2024/02/27 16:32

No.4 回答者： kanta2006 回答日時： 2024/02/26 21:02

例えば、自分でここまでやってみたけど、この部分をどう実現したらいいのか分からないので教えて下さい。

とかいうのは、具体的であっても全然OKです。

ダメなのは、やりたい事だけを示して、丸ごと作ってもらうこと。
これは無償で業務委託していることになってしまうので、駄目です。

分からない事を教えてもらって理解するということと、丸ごと作ってもらって内容は分からないけど便利に使わせてもらってます、というのとは全く違います。

Q&Aサイトは知識を教え合うのが目的であって、誰かに作業をやってもらうのを目的にするべきではないんです。

ただ、この私の説教じみた回答も、本当はガイドライン違反なのですが。
元の質問に回答していませんので。

この回答へのお礼

kanta2006さま　私は「教えてgoo」の使い方を正しく理解していないのですね。おっしゃるとおりかもしれません。
ただ、知識がある方に「対価は払えませんが、助けて頂けないですか？」は聞いてよいのかと思っていました。確かに、厚かましいですね、、。ご厚意に甘えていますね。

お礼日時：2024/02/27 16:39

No.3 回答者： kanta2006 回答日時： 2024/02/26 20:23

ここで誰かにマクロを作ってもらおうとされているのなら、それは作業依頼なのでガイドライン違反です。

ガイドラインを読んで下さい。

誰かにヒントを教わったり、やり方を教わったりして、最終的にマクロを自分で作るならOKです。

そもそも、誰かに作ってもらいたいなら、ChatGPTに聞けばコードを作ってくれますよ。
それなら誰からも違反だと言われる事はありません。
ChatGPTに依頼してはいかがですか？

ただし、ChatGPTに作ってもらうには、ChatGPTに質問を投げかける側の質問力が試されますけど。

この回答へのお礼

ご指摘、アドバイスありがとうございます。申し訳ないです、知りませんでした。ここでこのような具体的な依頼はガイドライン違反なのですね？
親切で対価を要求しない回答者の方が答えていただいた場合もガイドライン違反となるのでしょうか？
だとすると今までの方にもご迷惑をおかけしたのかもしれません、、とても助かったので感謝していたのですが。

お礼日時：2024/02/26 20:39

No.2 回答者： fujillin 回答日時： 2024/02/26 18:45

こんばんは

内容をイマイチ理解できませんけれど・・・

＞これらをマクロなどで、自動処理できるようにできませんでしょうか？
処理のルールが決まっているのなら、マクロを作成すれば自動処理できるようにすることは可能です。

（多分違うだろうとは思いますが）「自動」の意味が、エクセルを開くことなく自動的に実行される（例えば、ある時刻になると）などである場合には、トリガーによってはエクセルのマクロだけでは無理な場合もあります。
そのような場合でも、タスクスケジューラ等を併用することで、可能にすることはできるでしょう。
（電源を落としたままでは、難しいですが）

この回答へのお礼

わかりにくくてすみません。「自動」の意味は裏で動く（ある時刻になると発動する）ということではありません。シートにファイル名とシート名を入力し、マクロボタンを押すと別シートに書き込みされる、という意味です。

お礼日時：2024/02/26 19:25

No.1 回答者： banzaiA 回答日時： 2024/02/26 17:30

マクロでできるでしょうが、質問者さんは、そのマクロをメンテナンスできますか？

この回答へのお礼

読んで頂きありがとうございます。マクロの「メンテナンス」が必要なのでしょうか？以前もこの教えてgooでマクロを作成して頂いたことが複数ありますが、メンテナンス、、ということをしたことがありません。何年もその時のものを使用させて頂いています。多少のVBAの中のテキストを必要に応じて変更したことはあります、、それらを「メンテナンス」といいますか？すみません、知識が乏しいので教えて頂けると幸いです。

お礼日時：2024/02/26 19:30