Excelの行列入替を双方向で同期する方法

質問

• ExcelのSheet1とSheet2にあるデータベースを双方向で同期する方法を教えてください。入力箇所が点在しており、値だけでなくセルの色やフォントも同期させたいです。
• Sheet1には特許情報をまとめたデータベースがあり、Sheet2にはSheet1のデータベースを行列入れ替えてコピペしたものがあります。Sheet1を使いたい場合とSheet2を使いたい場合が混在しており、都度検索で入力を行っています。
• Excelのリンクや関数ではなく、VBAを使用して双方向でデータベースを同期させる方法を教えてください。使用しているのはWindows7とエクセル2010です。

回答No.6

eden3616 · ベストアンサー

以下の方法のうち、

・エリアを決め込む方法
・変更があるたびに対象範囲を行列入替でコピーし直す方法

前者は処理速度は格段にあがりますが、行削除・入替に対応できないため後者で作成し直しました。

以下のVBAコードと入れ替えて使用してください。

■VBAコード

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'On Error GoTo era
'型宣言
Dim myRng As Range
Dim mySt(1) As Worksheet
Dim myDb(1) As Range
Dim myArea(1) As Range
Dim myRow As Long
Dim myCol As Long
Dim flag As Integer
Dim myBas As Range
Dim myBef As Range

'DB範囲設定（基準位置設定）
'1つ目のデータベースを設定
Set mySt(0) = Sheets("Sheet1")
Set myDb(0) = mySt(0).Range("A1")
'2つ目のデータベースを設定
Set mySt(1) = Sheets("Sheet2")
Set myDb(1) = mySt(1).Range("A2")

'対象シート判定
Select Case ActiveSheet.Name
Case mySt(0).Name
　　flag = 0
Case mySt(1).Name
　　flag = 1
Case Else
　　flag = -1
End Select
If flag = -1 Then Exit Sub
'基準セルを記憶
Set myBas = myDb(flag)
'最大変更範囲取得
Set myArea(0) = mySt(flag).Range(myDb(flag), myDb(flag).SpecialCells(xlCellTypeLastCell))
'反転コピー
Application.EnableEvents = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
flag = Int((2 - flag) / 2)
mySt(flag).Activate
Set myBef = Selection
Set myArea(1) = mySt(flag).Range(myDb(flag), myDb(flag).SpecialCells(xlCellTypeLastCell))
myArea(0).Copy
myArea(1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
myBef.Select
mySt(Int((2 - flag) / 2)).Activate
GoTo owari:
era:
MsgBox "エラー番号:" & Err.Number & vbCrLf & "エラー内容：" & Err.Description
owari:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

お礼

ありがとうございます！！！
動作確認致しました！
・行や列の削除、入替え
・行や列、或いはDB全体のその場でのコピペ
・関数の入ったセルの動作も問題なく
全てがスムーズに行える様になりました。

この度は長期間に渡り、何度も最後までお助け頂きありがとうございました。

社内ではエクセルDBが推奨されており、
ご教示頂いた内容は全社的に横展開して活用して行こうと思います。

本当にありがとうございました。