Excel VBA：ひとつ前に開いていたシート名の取得

質問

Sheet1,2,3があり、全て同じ種類の「表」が書かれているとします。
例えばクラス名簿だとして、
Sheet1には出席番号１～１０の生徒のデータ、
Sheet2には出席番号１１～２０の生徒のデータ、のように。
各シートのうち、違うのはデータの中身であって、
[出席番号][生徒氏名]などの項目は同じです。
この時、
Sheet1で項目名を変更した時、Sheet2、Sheet3の項目名も同時に変更させたいです。
Sheet1の[出席番号]を[No.]に変更したら、Sheet2,3の[出席番号]も自動的に[No.]に変更させたいです。
また、変更可能なのはSheet1だけでなく、Sheet2の項目を変更した時もSheet1,3の項目を自動的に変更したいです。

常にSheet1の項目を参照するのであれば、
Private Sub Workbook_SheetActivate(ByVal ActSheet As Object)
SName = "Sheet1"
Sheets(SName).Range("A4:G4").Copy 'Sheet1の項目をコピー
Sheets(ActSheet.Name).Range("A4").Select
ActiveSheet.Paste '現在アクティブなシートにSheet1の項目をコピー
End Sub
で可能でした（諸事情によりセル内に「=Sheet1!A4」等と書きたくないです）。

このコードで、
参考するシート名を入れる変数SNameに「１つ前に開いていたシート名」を入れることができれば可能だと思うのですが、
そのようなデータを取得することはできるでしょうか？

よろしくお願いします。

回答No.6

KenKen_SP · ベストアンサー

私も単純に作業グループで操作すれば良いだけだと思いますが...

> １つ前に開いていたシート名...

というのは、「セルの変更があったシート」の内容を、それ以外の
シートに同期させる...という動作ですから、SheetChange イベントの
Sh が使えるかと思います。

値の変更のみ対応で良ければ、ThisWorkbook モジュールに下記のような
コードで。

Private Sub Workbook_SheetChange( _
　　　　ByVal Sh As Object, _
　　　　ByVal Target As Range)

　　Const ADDR = "A4:G4" ' 同期させるセルのアドレス
　　Dim S As Worksheet

　　If Not Intersect(Target, Sh.Range(ADDR)) Is Nothing Then
　　　 On Error GoTo ERROR_HANDLER
　　　 Application.EnableEvents = False
　　　 For Each S In ThisWorkbook.Worksheets
　　　　　 If Not S Is Sh Then
　　　　　　　S.Range(ADDR).Value = Sh.Range(ADDR).Value
　　　　　 End If
　　　 Next
　　End If

TERMINATE:
　　Application.EnableEvents = True
　　Exit Sub
ERROR_HANDLER:
　　MsgBox Err.Description, vbCritical
　　Resume TERMINATE
End Sub

書式の変更等まで同期させたいなら、VBA では難しいですから、やはり
作業グループの方法をとった方が手っ取り早いでしょうね。

なお、ActiveSheet.Previous はシートタブ上での並びでアクティブ
シートの一つ前のシートを単に返すだけですから、必ずしも前回の
アクティブシートを返すとは限りません。

お礼

回答ありがとうございます。
VBAは触り始めたばかりで、挙げて頂いたコード・手法の内容を確認するのにも時間がかかりそうですので、
取り急ぎお礼だけを。
挙げていただいた内容をこれからじっくり解析します。

作業グループにして使うのもアリですが、
私個人が使うマクロではなく、他の人間に使わせたいため、避けております。
また、
使う人間の、「項目を同時変更したい」という意思、を反映させたいのではなく、
使う人間が、「1箇所変更したら他の部分も変更されていた」、という状態にしたい、
という理由もあります。
助言、ありがとうございます。

回答No.5

Wendy02

こんばんは。

>Sheet1で項目名を変更した時、Sheet2、Sheet3の項目名も同時に変更させたいです。

それは、マクロの必要はないと思いますね。

同じ内容なら、Sheet1～Sheet3 のシートタブを選択し、作業グループにして入力を行えば、すべて、同時に同じ内容に変更されます。

>また、変更可能なのはSheet1だけでなく、Sheet2の項目を変更した時もSheet1,3の項目を自動的に変更したいです。

もちろん、これも可能です。アクティベートするシートを替えるだけです。

>変数SNameに「１つ前に開いていたシート名」

　On Error Resume Next
　sName = ActiveSheet.Previous.Name
　On Error GoTo 0

お礼

回答ありがとうございます。

>同じ内容なら、Sheet1～Sheet3 のシートタブを選択し、作業グループにして入力を行えば、すべて、同時に同じ内容に変更されます。

作ったマクロを私が使うだけならそれで問題ないのですが、
複数の人間に使ってもらう予定なので、「シートタブを選択し、作業グループにする」という手順を加えたくないのです。
また、今回は「項目を同時変更」という例を挙げましたが、
実際は、
使う人間が「全シートを同時に変更したい」と思うものではなく、
「1箇所変えたら他の部分も自動的に変わっている」ようにしたいのです。
そんな理由でマクロを選んでおります。
説明不足で申し訳ありません。

>>変数SNameに「１つ前に開いていたシート名」

ありがとうございます。
他の方の回答を含め、確認に時間がかかりそうなので(自身のスキルが足りません)、
取り急ぎお礼だけ申し上げます。

回答No.4

venzou

キッチリ書くなら、こんな感じかと。

Const HeaderAddress = "A4:G4"

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim r As Range
Dim i As Integer
Application.EnableEvents = False

If Target.Areas.Count = 1 Then
　Set r = Intersect(Target, Range(HeaderAddress))
　If Not (r Is Nothing) Then
　　CopyToSheets r
　End If
Else
　For i = 1 To Target.Areas.Count
　　Set r = Intersect(Target.Areas(i), Range(HeaderAddress))
　　If Not (r Is Nothing) Then
　　　CopyToSheets r
　　End If
　Next
End If
Application.EnableEvents = True
End Sub

Private Sub CopyToSheets(r As Range)
　Worksheets("Sheet1").Range(r.Address).Value = r.Value
　Worksheets("Sheet2").Range(r.Address).Value = r.Value
　Worksheets("Sheet3").Range(r.Address).Value = r.Value
End Sub

回答No.3

noname#187541

No.2です。訂正です。

ThisWorkbookモジュールのどのイベントに入れるか書いてませんでした。

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Application.EnableEvents = False
If Target.Row = 1 Then
Worksheets("Sheet1").Range(Target.Address).Value = Target.Value
Worksheets("Sheet2").Range(Target.Address).Value = Target.Value
Worksheets("Sheet3").Range(Target.Address).Value = Target.Value
End If
Application.EnableEvents = True
End Sub

回答No.2

noname#187541

こんばんは。

各シートで項目名を変更したとき、その変更をSheet1～Sheet3に反映させたいということでいいでしょうか。

項目名が1行目にあるとします。ThisWorkbookモジュールに

Application.EnableEvents = False
If Target.Row = 1 Then
Worksheets("Sheet1").Range(Target.Address).Value = Target.Value
Worksheets("Sheet2").Range(Target.Address).Value = Target.Value
Worksheets("Sheet3").Range(Target.Address).Value = Target.Value
End If
Application.EnableEvents = True

どうでしょうか。セルに書き込む部分はシートが多い場合ループで回すという方法をとるといいでしょう。

お礼

回答ありがとうございます。
VBAは触り始めたばかりで、挙げて頂いたコード・手法の内容を確認するのにも時間がかかりそうですので、
取り急ぎお礼だけを。
挙げていただいた内容をこれからじっくり解析します。

ループで回す、という方法は、
結果的にセルに計算式を書き込むことには変わらないのですよね？
書かなかったのですが、他に
・マクロで作成したファイルをCSVで出力
・１つのシートの項目数が最大3000になる(実際作るのはクラス名簿ではありませんから)
・項目名だけでデータの無い部分はCSVに出力したくない
という条件があります。
この場合、セルに計算式を書き込むと、データの有無に関わらずCSVファイルは常に3000行で出力されてしまいました。
（データの無い行は,,,,のように「，」だけ。これが3000行）
ファイル容量が大きくなるし、無駄も多いので、これは諦めました。
的外れな返答をしていたら申し訳ありませんが、
何か巧いやり方があるのでしょうか？（いくらでもありそうで収拾つかなくなりそうですが）

回答No.1

venzou

>そのようなデータを取得することはできるでしょうか？
多分出来ないと思いますので、自分で変数に保存しておく必要があるでしょう。

-------------------------------
'前に開いたシートを保存する変数
Dim OldSheet As Variant

'起動時に最初のシートを設定
Private Sub Workbook_Open()
　Set OldSheet = ActiveSheet
End Sub

Private Sub Workbook_SheetActivate(ByVal ActSheet As Object)
　'念のためEmptyかチェック
　If Not IsEmpty(OldSheet) Then
　　'前のシートの内容を、今のシートにコピー
　　OldSheet.Range("A4:G4").Copy ActSheet.Range("A4")
　End If
　Set OldSheet = ActSheet
End Sub
-------------------------------

こんな感じでどうでしょう？

#各ワークシートの Worksheet_Change で、処理した方が確実なような気もしますが・・・。

お礼

回答ありがとうございます。
VBAは触り始めたばかりで、挙げて頂いたコード・手法の内容を確認するのにも時間がかかりそうですので、
取り急ぎお礼だけを。
挙げていただいた内容をこれからじっくり解析します。