エクセル：別々のファイルにあるシートを1つのファイルでリアルタイムに管理したい

質問

バージョンはエクセル2000です。

北海道.xls、東北.xls、名古屋.xls、大阪.xls、広島.xls、九州.xlsという7つの支店のエクセルファイルがあり、
北海道.xlsには「sheet北海道」
東北.xlsには「sheet東北」
東京.xlsには「sheet東京」
名古屋.xlsには「sheet名古屋」
大阪.xlsには「sheet大阪」
広島.xlsには「sheet広島」
九州.xlsには「sheet九州」
が入っています。（1番左のシートを使っています。）
ファイルは共有フォルダに置き、各支店の担当者が各々の支店ファイルに不定期で新規案件情報（1案件につ1行）を書き込んでいきます。

今、新しく　全国.xls　というファイルをつくり、
そこに「sheet北海道」、「sheet東北」、「sheet東京」、「sheet名古屋」、「sheet大阪」、「sheet広島」、「sheet九州」と7つのシートを持ってきて、
全支店の管理を行う立場の方々が、全国.xlsを開くと各支店ファイルのその時点での状況が反映されているようにしたいです。

過去の質問を見て、シートを1つのファイルに集める（ブックの結合？）機能があることが分かり、過去の質問に載っていたマクロをコピーして使ってみたのですが、1回目はうまくいくのですが、2回目以降は前回の結合により作成された古い時点でのシートが残るので、消さなければならないのがネックです。（自動的に消す方法もあるのでしょうか？）
また、セルを１つ１つ参照させて作ってみたのですが、各支店のファイルの方で、行の削除や挿入を行うため、参照のリンクが切れて#REF!のエラーが出てしまいます。

上記のようなことができるかどうかと、できるならば、やり方を教えてください。

長文ですみません。分かりにくいかもしれませんが、よろしくお願いします。

回答No.1

pauNed

こんにちは。
取り込みたいデータの仕様にもよりますが（データベース仕様がベター）
[外部データの取り込み]機能を試してみてはいかがでしょう。
http://office.microsoft.com/ja-jp/excel/HP052005411041.aspx
http://www.eurus.dti.ne.jp/~yoneyama/Excel/filter2.htm

また、VBAを使うにしても、難しく考えず、
全国.xlsに各シートを作っておいて、（削除ではなくセル範囲のコピーでいいのでは？）

最初のBookを開く
↓
目的のシートを選択
↓
全セル範囲選択
↓
全国.xls選択
↓
貼り付け先シート選択・貼り付け
↓
最初のBookを選択・閉じる
↓
次のBookを開く。

という手順を[マクロの記録]録れば、それなりに処理できるはずです。

Sub Macro1()

　　ChDir "D:\test"
　　Workbooks.Open Filename:="D:\test\北海道.xls"
　　Sheets("Sheet北海道").Select
　　Cells.Select
　　Selection.Copy
　　Windows("全国.xls").Activate
　　Sheets("Sheet北海道").Select
　　Range("A1").Select
　　ActiveSheet.Paste
　　Application.CutCopyMode = False
　　Windows("北海道.xls").Activate
　　ActiveWindow.Close
　　'：
End Sub

これをBookOpen時に動くように体裁を整えて
Sub auto_open()
　　Const path As String = "\\192.168.1.1\office\" '目的のフォルダ
　　Dim v　As Variant
　　Dim vi As Variant

　　Application.ScreenUpdating = False '画面更新制御
　　'Loop基準の文字列を配列で作成し、繰り返し処理する
　　v = Array("北海道", "東北", "東京", "名古屋", "大阪", "広島", "九州")
　　For Each vi In v
　　　　With Workbooks.Open(Filename:=path & vi & ".xls", ReadOnly:=True)
　　　　　　.Sheets("sheet" & vi).Cells.Copy ThisWorkbook.Sheets("sheet" & vi).Cells(1)
　　　　　　.Close savechanges:=False
　　　　End With
　　Next vi
　　Application.ScreenUpdating = True
End Sub
こんな感じにすれば良いかと思います。