複数のキーを参照して、別ブックに転記する方法

質問

KeyIDと項目名を参照して各セルの記載を別ブックに転記したいのですが、VBAで動かす方法をどなたかご教示いただけますでしょうか。
マスターブックから、複数のブックへ、KeyIDを元に適切な項目名のセルに転記をさせたいです。
また逆に、複数ブックからマスターブックへも同じ方法で転記可能なVBAを組みたいと思っております。
KeiIDに対して、200以上の項目列があるので、毎回Vlookupをかけるのがかなりの手間で、何とか自動化できないか考えたのですが、複雑すぎてお手上げ状態です・・・
皆様のお知恵を拝借したく、何卒よろしくお願いいたします。

作業の流れとしては、マスターブックを開きマクロを走らせると、任意のブックを開くように指示が出るので、それを開く。（ここまでは作成済み）
複数ブックのKeyIDが数千行あるので、それをマスターブックの数万行の中のKeyIDと合致するかを確認し、合致する場合は、200項目の中で一致するセルに複数ブックの内容を転記する。またその逆もできるようにする、という感じです。

それぞれのブックは以下のような作りになっています。
複数のブックの項目名は、ブックごとに異なりますが、マスターブックには全ての項目名が入っています。
KeyIDおよび項目名のある列・行は、三つのブックでそれぞれ異なります。
KeyIDがどちらかに存在しない場合もあるので、その場合は無視する、という設定にしていただけますと助かります。

マスターブック
　　　A列　　　B列　　C列　 　　D列　　　　E列　　　　F列　～200列
23行　名前　　 住所　　KeyID　　電話番号　　作業予定　　作業実績

ブック１
　　　A列　　　B列　　　C列　　　　D列　～150列
12行　KeyID　　 住所　　作業予定　　作業実績

ブック２
　　　A列　　B列　　　C列　～100列
5行　 名前　　KeyID　　作業実績

どうぞよろしくお願いいたします。

回答No.2

ushi2015 · ベストアンサー

こんにちは
多分、相当時間が掛かります。
Sub test()
　　Dim mBk As Workbook
　　Dim tBk As Workbook
　　Dim mSh As Worksheet
　　Dim tSh As Worksheet
　　Dim v　　 As Variant
　　Dim vv　　As Variant
　　Dim mKeyC As Long
　　Dim tKeyC As Long
　　Dim mLrow As Long
　　Dim tLrow As Long
　　Dim tCol As Long
　　Dim i　　 As Long
　　Dim j　　 As Long
　　Dim k　　 As Long
　　Dim x()
　　Dim y()
　　Dim z　　 As Variant

　　'処理対象ブックは開いておくとする
　　Set mBk = Workbooks("マスターブック.xlsx")
　　vv = Array("ブック１.xlsx", "ブック２.xlsx")

　　'処理対象シートは1番目のシートとする
　　Set mSh = mBk.Worksheets(1)
　　mKeyC = Application.Match("KeyID", mSh.Rows(1), 0)
　　mLrow = mSh.Range("A1").CurrentRegion.Rows.Count
　　Application.ScreenUpdating = False
　　For Each v In vv
　　　　Set tBk = Workbooks(v)
　　　　Set tSh = tBk.Worksheets(1)
　　　　tKeyC = Application.Match("KeyID", tSh.Rows(1), 0)
　　　　tLrow = tSh.Range("A1").CurrentRegion.Rows.Count
　　　　tCol = tSh.Range("A1").CurrentRegion.Columns.Count
　　　　For i = 1 To tCol
　　　　　　If i <> tKeyC Then
　　　　　　　　ReDim Preserve x(j)
　　　　　　　　ReDim Preserve y(j)
　　　　　　　　x(j) = i
　　　　　　　　y(j) = Application.Match(tSh.Cells(1, i), mSh.Rows(1), 0)
　　　　　　　　j = j + 1
　　　　　　End If
　　　　Next
　　　　With tSh
　　　　　　For i = 2 To tLrow
　　　　　　　　z = Application.Match( _
　　　　　　　　　　.Cells(i, tKeyC), mSh.Cells(1, mKeyC).Resize(mLrow), 0)
　　　　　　　　If Not IsError(z) Then
　　　　　　　　　　For j = 0 To UBound(x)
　　　　　　　　　　　　If .Cells(i, x(j)) <> mSh.Cells(z, y(j)) Then
　　　　　　　　　　　　　　.Cells(i, x(j)).Interior.Color = 65535
　　　　　　　　　　　　　　.Cells(i, x(j)) = mSh.Cells(z, y(j))
　　　　　　　　　　　　End If
　　　　　　　　　　Next
　　　　　　　　Else
　　　　　　　　　　.Cells(i, tKeyC).Interior.Color = 14404237
　　　　　　　　End If
　　　　　　Next
　　　　End With
　　　　j = 0
　　　　Erase x: Erase y
　　Next
　　Application.ScreenUpdating = True
End Sub

お礼

ありがとうございます！こちらで思い通りのものが出来上がりそうです。マクロを回してる間に別のことができるので、残業時間も減らせそうです。つたない説明にも関わらず、お知恵をお貸しいただき、本当にありがとうございました。

回答No.3

imogasi

多分方向性が間違っていると思う。
データべースソフトにはファイル結合の仕組みがある。それを前提にファイルをわざと分割している。ふつう使うときは結合を指示して使う。
最低でもＭＳＡｃｃｅｓｓ（データベースソフト、さらにはＡＣＣＥＳＳのＶＢＡやプログラム）でも使う方法を勉強し（スクールにでも通って勉強して）考えるべき問題ではないか。使う道具を間違えるとコードが複雑になるばかりで、応用も聞かない。
ーー
こんな無料のコーナーに質問を挙げて、コピペして、ちょこっと済まそうというのはおかしい。
有料になろうが、業者に頼んで、確実なものを作ってもらうべき課題ではないか。
ーー
質問表現も、作業的に１歩１歩例を挙げて、説明した方がよいのでは。どのファイルの項目を見てどこへ持ってくるのか。
ごたごたしてよくわからない。KeyID　などなに？説明不足では？社内用語では。

お礼

ありがとうございます。通常はACCESSでやるものなのですね。業務に追われ目の前のものを終わらせることばかり考えてました。データベースについて調べてみたいと思います。

回答No.1

ushi2015

こんにちは
ブック１、ブック２の、住所・作業予定・作業実績・名前列等のセルが空欄
の場合にマスターブックからデータを転記するのですか？
それとも毎回全データについて、マスターブックから転記するのですか？
また、複数のブックからマスターブックに転記する場合、同じKeyIDの行が
複数ブックに存在するケースは無いのですか？
有るとしたらどのブックのデータを転記するのですか？

補足

ご質問ありがとうございます。
マスターブックからブック１、ブック２に転記する際には、マスターが常に正解として転記したいので、空欄であってもそうでなくてもそのまま転記したいです。
また、複数ブックからマスターブックに転記する場合、同じKeyIDが複数ブックに存在する可能性はありますが、基本的には各ブックごとに項目が異なるので、同じKeyIDかつ同じ項目になることはありません。
すいません、最初の説明が間違っていたのですが、例えば、ブック１の項目は住所と作業予定、ブック２の項目は名前と作業実績、という具合にKeyIDだけが同じになる予定です。
でも確かに不安なので、転記する際に、転記前の記載から変更があった場合は、セルを黄色く塗りつぶしするという作業を加えていただけると、目で確認できていいかなと思います。データ量が多くなった場合、かなり遅くなってしまうでしょうか。
説明がうまくできておらず申し訳ありません。
どうぞよろしくお願いいたしす。