EXCEL2007で2つのシートのどっちのセルに入力しても相互できるようにしたい

1つのBook内にSheet1・Sheet2があり、どちらのシートもA列には同じ会員の個人名が300人ほど入力されています。B～D列もフリガナ・生年月日など同じ値が入力されているのですが、E列～はSheet1には個人の年齢によって今後変化する会費の計算用の関数など、Z列には備考があります。Sheet2のE列～は住所などの値（これも今後変更の可能性あり）、そしてAA列にはこちらも備考があります。

備考を会費を見ながらも、住所を見ながらも変更したい為、
Sheet1のZ列を変更したらSheet2のAA列も同じになり、
Sheet2のAA列を変更してもSheet1のZ列が同じになるようにしたいのです。

”私はあなたで、あなたは私。でも一番最新ではこうだったよね”という感じでしょうか（汗）

しかも会員の個人情報（行）の順番は今度入会・退会があった際には増減して、B列のあいうえお順で並び替えもしたいです。

…「見る用のシートと書く用のシートを固定すれよ！」とは私も思うのですが、エクセル下手の上司がどうしても！って言うんです；_；

まとめ用の別シートを作成しても、VBAでも構いません。
どなたかご教授していただけないでしょうか。
よろしくお願いいたします。

No.5 ベストアンサー 回答者： MarcoRossiItaly 回答日時： 2015/05/30 14:36

No.4 です。

ごめんなさい、コードに致命的な誤りがあるので、訂正いたします。

No.4 のコードでは、並べ替えを行う列の範囲が A・B 列（Sheet1）、B・C 列（Sheet2）のみになっています。正しくは、Sheet1 であれば、Sort メソッドの行中、

×　Cells(Rows.Count, "b").End(xlUp)
○　Cells(Cells(Rows.Count, "b").End(xlUp).Row, "z")

というふうになっていなければなりませんでした。失礼しました。修正後のものを次のとおり載せます。


'Sheet1

Private Sub Worksheet_Change(ByVal Target As Range)
　　If Intersect(Target, Union(Columns("b"), Columns("z"))) Is Nothing Then Exit Sub
　　'シートの 2 行目以下を並べ替え
　　Range(Range("a2"), Cells(Cells(Rows.Count, "b").End(xlUp).Row, "z")).Sort _
　　　　key1:=Range("b2"), order1:=xlAscending, Header:=xlNo
　　Application.EnableEvents = False
　　Columns("a:z").Copy Destination:=Worksheets("Sheet2").Columns("b")
　　Application.EnableEvents = True
End Sub



'Sheet2

Private Sub Worksheet_Change(ByVal Target As Range)
　　If Intersect(Target, Union(Columns("c"), Columns("aa"))) Is Nothing Then Exit Sub
　　'シートの 2 行目以下を並べ替え
　　Range(Range("b2"), Cells(Cells(Rows.Count, "c").End(xlUp).Row, "aa")).Sort _
　　　　key1:=Range("c2"), order1:=xlAscending, Header:=xlNo
　　Application.EnableEvents = False
　　Columns("b:aa").Copy Destination:=Worksheets("Sheet1").Columns("a")
　　Application.EnableEvents = True
End Sub

この回答へのお礼

即戦力になりました。
すごくキレイなVBAで、自分では学が無くまだ理解しきれてはいないのですが、是非理解してマスターしたいと思いました。
本当にありがとうございました！

お礼日時：2015/06/01 14:59

No.4 回答者： MarcoRossiItaly 回答日時： 2015/05/30 10:52

No.1 さんのアイディアだと、シートが 3 枚以上ある場合に、新しいデータを古いデータで上書きしてしまう可能性が生まれてしまいそうな気がしますね。

それを防止するために、別のコードも用意しなければならないかも。



Excel のセルにあらかじめ数式を記入しておく方法では、参照元・参照先の入替えが不可能なので、質問文の内容を完全には達成することができません。質問者さん同様、元データが複数ある状態は危険も伴うため好ましいとは思いませんが、VBA を使えば不可能というわけではありません。

以下は、別法。シートは 3 枚以上でも構いません。

Sheet1 のシート見出しを右クリックして「コードの表示」から表示される画面に次のコードを貼り付け。


Private Sub Worksheet_Change(ByVal Target As Range)
　　If Intersect(Target, Union(Columns("b"), Columns("z"))) Is Nothing Then Exit Sub
　　'シートの 2 行目以下を並べ替え
　　Range(Range("a2"), Cells(Rows.Count, "b").End(xlUp)).Sort _
　　　　key1:=Range("b2"), order1:=xlAscending, Header:=xlNo
　　Application.EnableEvents = False
　　Columns("a:z").Copy Destination:=Worksheets("Sheet2").Columns("b")
　　Application.EnableEvents = True
End Sub


Sheet2 についても同様に操作して、次のコードを貼り付け。


Private Sub Worksheet_Change(ByVal Target As Range)
　　If Intersect(Target, Union(Columns("c"), Columns("aa"))) Is Nothing Then Exit Sub
　　'シートの 2 行目以下を並べ替え
　　Range(Range("b2"), Cells(Rows.Count, "c").End(xlUp)).Sort _
　　　　key1:=Range("c2"), order1:=xlAscending, Header:=xlNo
　　Application.EnableEvents = False
　　Columns("b:aa").Copy Destination:=Worksheets("Sheet1").Columns("a")
　　Application.EnableEvents = True
End Sub


並べ替えのキーにする列は「Sheet1 の」B 列あるいは「Sheet2 の」C 列と想定しているところですので、違う場合は、適切に修正してください。また、Sort メソッドの行において並べ替えの対象をシートの 2 行目以下と想定していたり、コピペする範囲を並べ替え対策のため Columns("a:z")、Columns("b") などと書いていますが、実際のシートの状況に合わせて、調節してください。

なおマクロなので「元に戻す」ボタンが効きません。記入を間違えたときは、保存せずにファイルを閉じて、「保存しますか」との問いにも「いいえ」を選択してください。つまり「B、C、Z、AA 列の記入を始める前に保存→ 記入→間違えた場合は保存せずに閉じる」というふうに操作してください。その旨の注意書きでもどこかに書いておいたほうがよいかもしれませんね。

No.3 回答者： henakichi 回答日時： 2015/05/30 01:23

No.2です。

② Sheet1に入力した内容が、そのままSheet2の同じ行･列のセルに反映されます。
Sheet2に入力した内容を編集すれば、同様にSheet1へ反映されます。

③ 「結果は、①ｰ2と同じように反映されます。」
結果は、②と同じように反映されます。訂正します。

反映されたら必ず、Sheet3など別シートを選択してシートのW選択を解除してください。
Sheet1･Sheet2を選択状態のまま作業を続ければ厄介なことになります。

以上、訂正と追記です。

この回答へのお礼

最初、”VBAを使うな”を制限がかけられていた時に似たような工程で作ったのですが、上司が使う際に”Shiftを押しながら…”を言うのは無理そうだなと判断して途中で投げてしまいました；
でも、同じデータを使って表を別の形態にする時などには使えそうです。
ありがとうございました。

お礼日時：2015/06/01 14:54

No.2 回答者： henakichi 回答日時： 2015/05/30 00:39

こんばんは。

Sheet2のAA列（備考）に、Sheet1のZ列（備考）を反映させるとして。
データは、2行目から入っているとします。

① Sheet2のAA2セルへ下記の数式を入れて下まで引っ張っていけば、Sheet1のZ列が反映されます。
=IF(Sheet1!Z2="","",Sheet1!Z2)

② Sheet1のZ列を一旦逃がすため、AB列にZ列をコピーする。
(Sheet1･Sheet2ともデータ入力なしのAB列ということで)
次に、キーボードのCtrlを押したまま、Sheet1とSheet2を選択しておく。
Sheet1のAB列のデータを変更すれば、Sheet2のAB列にも反映されます。
その後、反映されたSheet2のAB列をZ列に上書きする。

③ 行の増減も、キーボードのCtrlを押したまま、Sheet1とSheet2を選択しておく。
結果は、①ｰ2と同じように反映されます。

④ 並べ替えは、列を選択しておいてSheet上のメニューから。
データ → 並べ替え → (左から) フリガナ･値･昇順 → OK。
※フリガナは、質問者さん作成のB列のことです。

趣旨が合えばですが、状況が分かり辛くてすみません。

No.1 回答者： hallo-2007 回答日時： 2015/05/29 21:03

ＶＢＡの力を借りるしかないと思いますが

はたしてそれが正しいのか？ですね。
＞まとめ用の別シートを作成しても
これが正解だと思います。
とにかく正しいデータのシートを準備して
必要な情報がＳｈｅｅｔ１、Ｓｈｅｅｔ２に表示される関数にしてしまう方法です。

＞エクセル下手の上司がどうしても！
一案ですが、エクセルＶＢＡのイベントに　Worksheet_Activate()　があります。
つまり他のシートからこのシートに移動した時に実行されるイベントです。
＞Sheet1のZ列を変更したらSheet2のAA列も同じになり
Sheet2の名前のタグをクリック、コードの表示をクリック
Private Sub Worksheet_Activate()
Column("AA").Value=Sheets("Sheet1").Column("Z").Value
End Sub
とすれば、Sheets2へ移動した時に、Sheet1のZ列が全て入れ替わります。

＞Sheet2のAA列を変更してもSheet1のZ列が同じになるようにしたいのです
Sheet２のイベントにはその逆を入れます。
Private Sub Worksheet_Activate()
Column("Z").Value=Sheets("Sheet1").Column("AA").Value
End Sub

この回答へのお礼

№4-5さんもおっしゃっていましたが、並び替えに不安を覚えましたが、Worksheet_Activate()　のイベントを知らなかったので勉強になりました！
私の文章を活かしての説明も判り易かったです。
ありがとうございました。

お礼日時：2015/06/01 14:48