VBA　フォルダ内の特定ブック内のシートを特定し、それらのセルの値を全て1つにまとめる

Question

はじめまして。
excel2000ですが・・・

以下の過去ログと類似しているのですが、
http://oshiete1.goo.ne.jp/qa4134321.html

管理表.xlsと同一フォルダ内に、
予定表1.xls　（sheet1,　sheet予定1,　sheet予定2,　sheet2)
予定表2.xls　（sheet1,　sheet予定2,　sheet予定3,　sheet2)
予定表3.xls　（sheet1,　sheet2,　sheet予定1,　sheet予定5)
予定表4.xls　（sheet1,　sheet予定4,　sheet予定2,　sheet2)
予定表5.xls　（sheet予定2,　sheet予定1,　sheet1,　sheet2)
・
・
・
（管理表.xlsもあります）

が、あるとします。
フォルダ内の全ての「予定表*.xls」ブックの中の「sheet予定*」シートの一定の範囲の値を「管理表.xls」の「sheet1」にまとめたいと思っています。
ここでやっかいなのが、「sheet予定*」は、同一のフォーマットなので、まとめやすいと思ったのですが、セルが結合されていたり、マクロが入っていたりで、なかなか思うようにペーストできません。必要なのは値だけなので、「Sheet予定*」の一定範囲(B4:I4、B5:I5、B6:I6・・・)の値を「管理表.xls」の「sheet1」のそれぞれ1行ずつ（例えばA4:H4、A5:H5・・・）にまとめていきたいです（コピペではなく参照の方がよいのでしょうか）。

自分でやろうとして色々調べながらできたのは、フォルダ内のファイル名「予定表*.xls」の取得のみです。「sheet予定*」の値の参照もやってみましたが、上書きされ、結局最後にコピーした「sheet*」予定のセルの範囲のみが貼り付けられて終わってしまいました。

長々と書いてしまって申し訳ありませんが、わかる方いらっしゃいましたら、ご指導いただきたいと思います。

n-jun · Accepted Answer

ブックのループは出来るとして、それ以外についてのサンプル。

１つのブックをコピーします。
Sheet2はデータのない状態にしておいて下さい。
以下のコードを貼り付けます。

Sub　test()
　Dim　ws　As　Worksheet
　Dim　r　As　Range
　Dim　v,　i　As　Integer

　v　=　Array("J3",　"O3",　"G3",　"H3")

　Set　r　=　Worksheets("Sheet2").Range("A1")

　For　Each　ws　In　Worksheets
　　　　　If　InStr(ws.Name,　"予定")　Then
　　　　　　　　With　ws
　　　　　　　　　　　　　For　i　=　0　To　3
　　　　　　　　　　　　　　　　　r.Offset(,　i).Value　=　.Range(v(i)).Value
　　　　　　　　　　　　　Next
　　　　　　　　　　　　　r.Offset(1).Resize(32,　17).Value　=　.Range("B14:R45").Value
　　　　　　　　　　　　　Set　r　=　r.End(xlDown).Offset(1)
　　　　　　　　End　With
　　　　　End　If
　Next
　Set　r　=　Nothing
End　Sub
シート名に”予定”が含まれていると、そのシートのデータをSheet2に
代入していきます。
シートが変われば順次Sheet2に続けて代入します。

ご参考になれば。

n-jun · Answer

ANo.4です。

>ですが、1つ目のファイルは問題なく終わるのですが、2つ目のファイルに行ってから
>r.Offset(, i).Value = .Range(v(i)).Valueで止まってしまいます。
ブックのループは一切考慮していませんので、エラーになりますね。

例えば、
Set　r　=　Worksheets("Sheet2").Range("A1")
は
Set　r　=　ThisWorkbook.Worksheets("Sheet2").Range("A1")
とかブックを指定しなければなりません。

また
For　Each　ws　In　Worksheets
でも、どのブックのワークシート群なのかを指定しなければなりません。

基本的にサンプルですので、あとはどのように変更してエラーになったのかは、
エラーの発生した文とエラー内容のみではわからない時もあります。
全体のコードの提示が必要かと。
（提示したサンプルそのままでエラーになった場合なら、回答者側でも
　検証は出来ますけど）

CaveatEmptor · Answer

他の方の回答にあるように、マクロが入っていることは関係ないと思います。

セルの結合が原因でコピペができないのであれば、マクロでセルの結合を解除してコピペすれべいいのではないでしょうか？（開いたファイルを上書き保存せずに閉じれば、元データは変更されません）もしくは、行全体をコピペするという手もあると思いますが、ダメでしょうか？

n-jun · Answer

コピペより代入の方がよかったりするかも？

>セルが結合されていたり
どこがどのように結合されてるか見えないのでなんとも。。。

spnlight · Answer

(1)管理表.xlsにすべての予定表*.xlsをリンク貼付けするとか、（予定表1.xlsのsheet予定1のB4:I4、B5:I5、B6:I6・・・を管理表.xlsのsheet1のA4:H4、A5:H5・・・にリンク張付け→sheet予定2のB4:I4、B5:I5、B6:I6・・・をA10:H10、A11:H11・・・にリンク張付け）

(2)マクロで1予定表毎にファイルオープンして、データを自動でコピペするとか

>結局最後にコピーした「sheet*」予定のセルの範囲のみが貼り付けられて終わってしまいました
コピー→張付け→コピー→張付け・・・で出来ませんか？

>セルが結合されていたり、マクロが入っていたりで
どちらも影響が無いような気がするけど・・・現物見れないんで分かりませんねー

VBA フォルダ内の特定ブック内のシートを特定し、それらのセルの値を全て1つにまとめる

ブックのループは出来るとして、それ以外についてのサンプル。

ANo.4です。

他の方の回答にあるように、マクロが入っていることは関係ないと思います。

コピペより代入の方がよかったりするかも？

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング

VBA　フォルダ内の特定ブック内のシートを特定し、それらのセルの値を全て1つにまとめる