複数のエクセルシートの特定セルを別のブックのシートに行方向に転記してまとめたい

エクセル2010で、専用フォルダーに入っている複数のエクセルファイルの特定のセル（A1,B2,C3等）を別のブック（まとめ）に行方向に転記したいのですが。
まとめ用のシートの上の行から順に各シートのセル内容を転記していきたいのですが何か方法がありませんでしょうか？

例えば各ファイルに住所、TEL No、名前、その他の情報が、決まった書式のシートに入っていて、このシートが毎月新規データとして追加されるのですが、別のエクセルシートに必要な項目のみ転記して、一覧表を作成したいのです。

現在は、1つずつファイルを開いて、目的のセルのコピペでまとめのシートに貼り付けて作業していますので、これより少しでも簡単な方法があれば教えてください。

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

• ご丁寧な解説ありがとうございます。
  
  試行錯誤でがんばってはいるのですが、知識不足はいかんとも・・・
  何処の参照がどこに転記されるのかが今一合わせられません。
  また、ファイル名が縦に3個づつ表示されたり、目的のA列の1つのセルのした空白行が入って次のファイルのA列の値が転記されたりしています。
  
  但し、一応自動で各ファイルの特定セルが意図しない場所に転記されるようになったので後一歩？だと思うのですが、当方にはやはりハードルが高いと思います。
  
  誠に申し訳ないのですが、下記のようなことがしたいので具体的なコードを教えて頂きたいのですが。
  
  ファイルは月ごと／担当者ごとに決まったフォルダーに”HH ・・・”で保存されています。

  
  No.3の回答に寄せられた補足コメントです。 補足日時：2018/01/29 10:27

• 説明がへたくそで、何度も申し訳ありません。
  内容は実務と違うように変更してありますが、左の書式Ⅰような表（活動報告書）に入力されています。
  この表の中から必要な項目だけをⅡの活動結果一覧に抜粋したいのですが。
  このような表が週に数枚フォルダーに入ってきて、期末、年末に集計しています。

  
  No.4の回答に寄せられた補足コメントです。 補足日時：2018/01/30 08:41

No.5 ベストアンサー 回答者： hallo-2007 回答日時： 2018/01/30 20:30

難しいコードではありませんが、根気のいるコードになります。

GYOU1 = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
で、左の書式Ⅰような表（活動報告書）のデータの行数
GYOU2 = .Range("A" & Rows.Count).End(xlUp).Row + 1
で、活動結果一覧の最後の行番号を計算しています。

今回の場合
一枚のシートに、データが一行ですよね。
Workbooks.Open ThisWorkbook.Path & "\" & Buf
GYOU2 = .Range("A" & Rows.Count).End(xlUp).Row + 1
.Range("A" & GYOU2).Value = Sheets("Sheet1").Range("A2”）.Value
.Range("B" & GYOU2).Value = Sheets("Sheet1").Range("B2”）.Value
.Range("C" & GYOU2).Value = Sheets("Sheet1").Range("C2”）.Value
・・・・
とデータが入力されているセルを個々に指定します。

さて、既に手遅れですが、
配布する活動報告書の方にひと工夫しておくと、集計の際のVBAで楽できるよう方法があります。
例えば、一行目に、関数を入れておきます。
集計のデータと同じように一行になる様に
A1　＝A2
B1　＝B2
C1　＝C2
・・・・
そして、その1行目を非表示にしておきます。
集計する際、VBAでは
Workbooks.Open ThisWorkbook.Path & "\" & Buf
GYOU2 = .Range("A" & Rows.Count).End(xlUp).Row + 1
.Range("A" & GYOU2 & ":E" & GYOU2 ).Value = Sheets("Sheet1").Range("A1:E１”).Value
で済みます。
アンケートの集計
https://ameblo.jp/hokora2016/entry-12328732608.h …
が参考になるかもしれませんが
・集計用の行を準備したファイルを配布する。
・OutLookの受信フォルダから添付ファイルを自動保存する。
・保存したファイルの1行目だけを集計ファイルに追記していくマクロ
・不要となったファイルを削除するマクロ
ここまで出来れば、仕事が　ボタンワンクリックになりますね。

この回答へのお礼

ご丁寧な回答に感謝です！！

今日手の空いた時に何度か試行してみたのですがまだうまく動きませんが、落ち着いてやれば何とかなりそうです。（基本的にコピぺで出来るようにしてもらっているので）

ちなみに書式は別部署の管理しているファイルなのでいじれません。　これがネックでもあるのですが。

これに類似した作業はほかのファイルでもあるのでこれを見本に勉強して使えるようになります。
当方の質問の不備にも拘わらず何度も丁寧な対応に本当に感謝いたします。

今後もよろしくお願いします。

お礼日時：2018/01/31 21:18

No.4 回答者： hallo-2007 回答日時： 2018/01/29 21:09

う～ん、添付のエクセルをみても？？

どちらが
・各ファイルに住所、TEL No、名前、その他の情報
・別のブック（まとめ）
差し支えなけば
希望のシートの業況を張り付けてみて下さい。

No.3 回答者： hallo-2007 回答日時： 2018/01/27 12:52

.Range("A" & Rows.Count).End(xlUp).Row

については、
https://ameblo.jp/hokora2016/entry-12317456227.h …
で紹介しています。

No.2 回答者： hallo-2007 回答日時： 2018/01/27 10:47

>2つ目、3つ目のファイルを順番に開くたびに同じ行に上書きされてしまい

多分ですが
GYOU2 = .Range("A" & Rows.Count).End(xlUp).Row + 1
で、Aのデータが入力されている最後の行番号を探しています。
A列にデータが入っていますでしょうか。
検索する列を　B列、或いはC列にしてみては如何でしょうか。

No.1 回答者： hallo-2007 回答日時： 2018/01/25 20:18

ボタンをワンクリックするだけに出来ますが

VBAの力を借りる事になります。
https://ameblo.jp/hokora2016/entry-12319754037.h …
エクセルVBAで複数のファイルのデータをまとめる。
こんな感じの事ですが大丈夫ですか。

この回答へのお礼

ご回答ありがとうございます。

先ずはお礼と、申し訳ありませんが出来ればもう少し教えてください。
ご推察の通り、VBA初心者でこれが正常に動くまでに相当時間がかることが予想されますが、とりあえず試行錯誤してみた結果、やっと動くようにはなったのですが、やはりだめでした。

具体的には、3つのファイルで試行したのですが、1つ目のファイルの指定の行を参照してくるのですが、2つ目、3つ目のファイルを順番に開くたびに同じ行に上書きされてしまい、結果としては最後のファイルの参照結果だけが残るのですが。
上書きではなく、最下行への追加にするにはどこを修正すればよいのでしょう？

お手数をおかけしますが宜しくお願いします。

Sub ボタン1_Click()

Dim Buf As String
Dim GYOU1 As Long
Dim GYOU2 As Long
Buf = Dir(ThisWorkbook.Path & "\HH*.xlsx")
With ThisWorkbook.Sheets("Sheet1")
Do While Buf <> ""
Workbooks.Open ThisWorkbook.Path & "\" & Buf
GYOU1 = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
GYOU2 = .Range("A" & Rows.Count).End(xlUp).Row + 1
.Range("A" & GYOU2 & ":E" & GYOU2 + GYOU1 - 2).Value = Sheets("Sheet1").Range("A2:E" & GYOU1).Value
.Range("F" & GYOU2 & ":F" & GYOU2 + GYOU1 - 2).Value = Buf
ActiveWorkbook.Close SaveChanges:=False
Buf = Dir()
Loop
End With

End Sub

お礼日時：2018/01/26 13:30