[[20140903163248]]　『１シートに行を追加したら他のシートにも反映され』（yukkie）　ページの最後に飛ぶ

『１シートに行を追加したら他のシートにも反映されるようにしたい』（yukkie）

sheet1に１行追加したら、sheet2の同じ箇所に１行追加されるようにしたい。

sheet1
名前　ふりがな　評価　　　・・・・
ｘｘ　ｘｘｘ　　ｘｘｘ　　・・・・
＊＊　＊＊＊　　＊＊＊　　・・・・
□□　□□□　　□□□　　←ここを追加
〇〇　〇〇〇　　〇〇〇　　・・・・

sheet2
名前　ふりがな　取得資格
ｘｘ　ｘｘｘ　　ｘｘｘ
＊＊　＊＊＊　　＊＊＊
〇〇　〇〇〇　　〇〇〇

名簿で入退社のタイミングで増えたり減ったり（非表示にしてあります）
する事がある為、sheet1に１行追加・非表示にした際にsheet2の情報も
１行追加・非表示になるようにしたいのですが良い方法ありませんか？

sheet1に入力できる限りその人の情報を入れておりますが、
資格は人によって多かったり少なかったりなので
シートを分けて管理することにしました。
トップで出てくるsheet1に氏名を追加・非表示にした時に、
sheet2にも１行追加されて、さらに名前まで出るようにできるように
したいです。
よろしくお願いいたします。

＜使用 Excel：Excel2013、使用 OS：Windows7 ＞

同じ個所に1行追加なら
CTRLを押しながら同じようにしたいシートを選択すればsheet1でやったことはsheet2にも同じように反映されます。

（デイト） 2014/09/03(水) 16:57

ありがとうございました。
何名かが作業するものですので、シート選択して1行追加で作業すると
忘れてしまう人もいる可能性があるので、最初に計算式をいれておいて
sheet1だけ修正すれば他のシートにも反映できるようにできないかな？と
思っての質問でしたので、他の方法もありましたら教えてください。

よろしくお願いいたします。
（yukkie） 2014/09/03(水) 17:19

 例えば、

 Sheet2 で
 A1=INDIRECT("Sheet1!A"&ROW(A1))&""

 などと入力して、適当に下にコピーしておくとそれに近いことはできます。
 Sheet1で行を挿入、削除すればSheet2にも反映されます。

 ただし、非表示にしただけでは反映されません。
 それも反映させるには、マクロになると思います。

（tora） 2014/09/03(水) 17:43

ありがとうございました。
反映させることが出来ました。

非表示や行を追加した時も反映させることはできますか？
マクロ勉強中なのでいい案があれば教えてください。

よろしくお願いいたします。
（yukkie） 2014/09/04(木) 09:40

 マクロは得意ではないのですが一例として、
 Sheet2のシートモジュールに

 Private Sub Worksheet_Activate()

    Dim i As Long

    For i = 1 To Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
        If Sheets("Sheet1").Rows(i).EntireRow.Hidden = True Then
            Me.Rows(i).EntireRow.Hidden = True
        Else
            Me.Rows(i).EntireRow.Hidden = False
        End If
    Next i

 End Sub

 のようにすれば、表示・非表示が反映されます。
 これは、Sheet1のA列でデータが入っている最後の行までをSheet2に反映させるものですから、
 それより下の行を非表示にした場合などは反映されません。
 実際にはどのような使い方をするかによって変更の必要があります。

 また、これだけではデータの変更は反映されませんので、前述の方法と併用する必要があります。
 その場合は、むしろマクロでデータも転記させた方が早いような気もしますが。

（tora） 2014/09/04(木) 10:53

 全部転記してから不要なところだけ消す案
 出力先のシートモジュールに入れてください。

    Private Sub Worksheet_Activate()
        Dim データ As Range
        Dim 検索範囲 As String
        Dim 最大行数 As String
        Dim 非表示
        Dim 式 As String
        Dim i As Long

        '//更新の問い合わせ
        If MsgBox("データを更新しますか？", vbYesNo) = vbNo Then Exit Sub

        '//データ入力範囲の取得
        Set データ = Sheets("Sheet1").Range("A1").CurrentRegion  '<--●実際に合わせてください。データが入力されている表範囲の一番左上です。

        '//非表示行の検索
        検索範囲 = "Sheet1!A1:A100"     '<--●実際に合わせてください。データが必ず入力されている列範囲です。
        最大行数 = Evaluate("ROWS(" & 検索範囲 & ")")
        式 = "IF(SUBTOTAL(103,OFFSET(" & 検索範囲 & ",ROW(1:" & 最大行数 & ")-1,,1))=1,""-"",ROW(" & 検索範囲 & "))"
        非表示 = Filter(Application.Transpose(Evaluate(式)), "-", False)

        '//データの転記と非表示行の削除
        With Sheets("Sheet2")           '<--●実際に合わせてください。出力先のシート名です
            .Cells.ClearContents
            .Range("A1").Resize(データ.Rows.Count, データ.Columns.Count).Value = データ.Value
            For i = UBound(非表示) To LBound(非表示) Step -1
                .Cells(非表示(i), 1).EntireRow.Delete
            Next i
        End With
    End Sub
（稲葉） 2014/09/04(木) 11:31

コードを教えて下さいましてありがとうございました。
内容を現在作成中のExcelシート名にあわせたものに致します。

これを機にマクロの勉強をいたします。

ありがとうございました。
（yukkie） 2014/09/04(木) 16:52

INDIRECTで引っ張ってきたい情報を他のシートに持ってくることはできました。
Private Sub Worksheet_Activate()

    Dim i As Long
    For i = 1 To Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
        If Sheets("Sheet1").Rows(i).EntireRow.Hidden = True Then
            Me.Rows(i).EntireRow.Hidden = True
        Else
            Me.Rows(i).EntireRow.Hidden = False
        End If
    Next i
 End Sub
を使って自動的に
INDIRECTの計算式に入っている箇所は反映することに成功しました。

あと一息なのですが、
Sheet1に１行追加すると、INDIRECTの計算式が入っているとこは1行あくのですが
計算式が入っていない箇所は1行挿入されていませんでした。

sheet1
名前　ふりがな　評価　　　・・・・
ｘｘ　ｘｘｘ　　ｘｘｘ　　・・・・
＊＊　＊＊＊　　＊＊＊　　・・・・
□□　□□□　　□□□　　←ここを追加すると
〇〇　〇〇〇　　〇〇〇　　・・・・

Sheet2の名前ふりがなは反映されます。
でも、取得資格の所は手入力なので、1行したに下げたいのですが
下記のようになってしまい下がりません。

sheet2
名前　ふりがな　取得資格（手入力）
ｘｘ　ｘｘｘ　　ｘｘｘ
＊＊　＊＊＊　　＊＊＊
□□　□□□　　〇〇〇　
〇〇　〇〇〇

どのようにすれば、1行まるまる挿入することが出来ますか？
（yukkie） 2014/09/08(月) 14:18

 まだ見ておられますか？

 運用方法を見直すのが良いと思いますが。

 今の状態だと
 Sheet2の3行目に入力されている資格が　誰が取得したものか
 Sheet1のデータが変わってしまったらわからなくなります。

 Sheet1への行の挿入もマクロで行う事にしてはどうでしょう。
 　
（HANA） 2014/09/17(水) 15:02

ありがとうございました。
かなり時間が経ってしまいましてすみません。

Sheet１への挿入もマクロで行う事は思いつきませんでした。
色々探してみてわからなかったらまたこちらで相談いたします。

（yukkie） 2014/12/16(火) 09:13

コメント返信：

[ 一覧(最新更新順) ]

[[20140903163248]] 『１シートに行を追加したら他のシートにも反映され』（yukkie） ページの最後に飛ぶ

[[20140903163248]]　『１シートに行を追加したら他のシートにも反映され』（yukkie）　ページの最後に飛ぶ