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ベストアンサー
- 回答日時:
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で、自分では学が無くまだ理解しきれてはいないのですが、是非理解してマスターしたいと思いました。
本当にありがとうございました!
No.4
- 回答日時:
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
- 回答日時:
No.2です。
② Sheet1に入力した内容が、そのままSheet2の同じ行・列のセルに反映されます。
Sheet2に入力した内容を編集すれば、同様にSheet1へ反映されます。
③ 「結果は、①ー2と同じように反映されます。」
結果は、②と同じように反映されます。訂正します。
反映されたら必ず、Sheet3など別シートを選択してシートのW選択を解除してください。
Sheet1・Sheet2を選択状態のまま作業を続ければ厄介なことになります。
以上、訂正と追記です。
最初、”VBAを使うな”を制限がかけられていた時に似たような工程で作ったのですが、上司が使う際に”Shiftを押しながら…”を言うのは無理そうだなと判断して途中で投げてしまいました;
でも、同じデータを使って表を別の形態にする時などには使えそうです。
ありがとうございました。
No.2
- 回答日時:
こんばんは。
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
- 回答日時:
VBAの力を借りるしかないと思いますが
はたしてそれが正しいのか?ですね。
>まとめ用の別シートを作成しても
これが正解だと思います。
とにかく正しいデータのシートを準備して
必要な情報がSheet1、Sheet2に表示される関数にしてしまう方法です。
>エクセル下手の上司がどうしても!
一案ですが、エクセルVBAのイベントに 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列が同じになるようにしたいのです
Sheet2のイベントにはその逆を入れます。
Private Sub Worksheet_Activate()
Column("Z").Value=Sheets("Sheet1").Column("AA").Value
End Sub
№4-5さんもおっしゃっていましたが、並び替えに不安を覚えましたが、Worksheet_Activate() のイベントを知らなかったので勉強になりました!
私の文章を活かしての説明も判り易かったです。
ありがとうございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) Excelにて、行の最後のセルの値をコピーして別sheetに張りつけるVBAコードをご教授願います 3 2022/11/20 14:35
- Excel(エクセル) SUMIFSと日付変換 10 2023/04/16 15:38
- その他(Microsoft Office) 従業員増減対応で当番種類の増減対応な当番表 21 2022/07/19 07:30
- Visual Basic(VBA) 3つのプロシージャをまとめたら実行時エラー発生で対応不能 6 2022/05/17 01:47
- Visual Basic(VBA) VBA 検索と入力 Excel ブック ぶぶぶ シート ししし 列V 検索対象の列です 最終行は、お 6 2023/05/17 01:40
- Excel(エクセル) Excel_マクロ_複数のシートのVLOOKUPで表示された#N/A以外に色付けをしたいです 1 2023/02/16 22:37
- Visual Basic(VBA) Changeイベントで複数セルへの貼り付けおよび値削除時に1個目のセルのみエラーになる 3 2022/12/21 09:07
- Visual Basic(VBA) エクセルのマクロで対象ごとにシート分けしてその内容をセルに書き込みたい 9 2022/08/24 13:23
- Visual Basic(VBA) VBA 別sheetからの転記なのですが 2 2023/05/22 15:55
- Excel(エクセル) エクセルのイベントVBAを複数のシートで動かしたい 1 2022/12/07 16:55
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Word2016でExcelデータを差込し...
-
【Excel VBA】CSV取込時、数字...
-
EXCELの列の幅
-
エクセルで前年同日・前月同日...
-
エクセルでページ毎の計をつけ...
-
EXCELで2つのシートから一致し...
-
excelの列がいっぱいになり列を...
-
Excel 表の必要箇所だけを抜き...
-
エクセルで電話番号にハイフン...
-
EXCELで不良率を出そうと思って...
-
エクセルVBAで複数列データを1...
-
ExcelのIF関数について
-
ピボットテーブル作成後、重複...
-
マクロ VBA 他のブックのデータ...
-
エクセル:最新データ12件で...
-
セル入力文字が、「右のセルに...
-
エクセル、ページをまたがった...
-
パワポの複数ページにまたがる...
-
wordで罫線が引けない・・・
-
パワーポイントの表
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Word2016でExcelデータを差込し...
-
【Excel VBA】CSV取込時、数字...
-
EXCELで2つのシートから一致し...
-
Excel 表の必要箇所だけを抜き...
-
EXCELの列の幅
-
エクセルでページ毎の計をつけ...
-
エクセルで前年同日・前月同日...
-
excelの列がいっぱいになり列を...
-
EXCEL2007で2つのシートのどっ...
-
エクセルで電話番号にハイフン...
-
EXCELで不良率を出そうと思って...
-
エクセルVBAで複数列データを1...
-
ExcelのIF関数について
-
順位表において先週と今週の数...
-
エクセルで2列以上のものを同時...
-
ピボットテーブル作成後、重複...
-
エクセルの余白を0にしても列...
-
(形式直し)Sheetの表のサイズ...
-
エクセルで縦線のいっぱい入っ...
-
SUMPRODUCT関数で複数条件適用...
おすすめ情報