別シートのセルの文字をシート名に自動で変更

質問

いつもありがとうございます。
下記の添付の「勤務表」のシートのA4の日付を変更すれば、「Sheet１」のシート名も自動的に変換できるようにとネット検索で見つけてきた下記のマクロ（？）の記述をセル名を変えてコピペし、「勤務表」のシートのA4の日付を変えて見たのですが、何の変化もありません。エラーメッセージも出ません。

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ERR_HANDLER

If Target.Address(False, False) = "勤務表!A4" Then
ActiveSheet.Name = Range("勤務表!A4").Value
End If

Exit Sub

ERR_HANDLER:

MsgBox "現在のセルの値はシート名にできません。"

End Sub

「勤務表」のシートのA4以下は、B1とE1の数値から自動で一ヶ月分の日付を入れる関数を入れて日付を作成しています。
=IF(DATE($B$1+1988,$E$1,ROW(A21))>DATE($B$1+1988,$E$1+1,20),"",DATE($B$1+1988,$E$1,ROW(A21)))

念のため、「Sheet1」内のA1でシート名が変わるかどうか試してますと、直接入力した文字の場合、シート名が入力した文字に問題なく変わりますが、関数で呼び込んだ文字は「現在のセルの値はシート名にできません。」のエラーメッセージが返って来ました。
このことから、関数で呼び込んだ文字では、シート名の自動変更はできないのが判りましたが、「勤務表」から呼び出した場合、エラーメッセージも出ないところを見ると、そのことの対処だけでは、解決しないような気がしています。

色々、ネットで検索したりもしましたが、未熟な私の知識と経験では、行き詰ってしまいました。

関数で呼び込んだ別シートのセルの日付で、シート名が自動で変更できるように、
お助け頂けないでしょうか？
当方はエクセル２０１０を使用しております。
よろしくお願いいたします。

回答No.6

keithin · ベストアンサー

おっと失礼，

変更前：
worksheets("Sheet1").copy(after:=worksheets(worksheets.count))

変更後：
worksheets("Sheet1").copy after:=worksheets(worksheets.count)

＃マクロが動かないのは，準備や手順に間違いがあるからです。
　回答の手順を見ながら，もう一回最初からやり直してください。

＃余談
しかし回答しながら不思議だったのは，２１日シートから翌２０日シートまでは毎月必ずある（要る）のですから，月替わりで３１日が無ければ３１日シートだけ使わずに放っておけば，毎日分のシートをわざわざ毎月新調しなきゃならない理由は何も無いように思えますけどね。

シートを新調するんじゃなく，先月分の毎日シートを「クリアする」みたいなのをマクロでやらせるなら，それはそれで理解出来ますが。

参考）
勤務表シートのマクロをすべて消去し，下記のマクロを代わりにコピーする
勤務表のＢ１，Ｅ１を書き換えると勤務表以外のシートのＢ２：Ｊ３０をクリアする

private sub worksheet_change(byval Target as excel.range)
　dim w as worksheet
　if target.address <> "$B$1" and target.address <> "$E$1" then exit sub

　for each w in worksheets
　if w.name <> "勤務表" then
　　w.range("B2:J30").clearcontents
　end if
　next
end sub

お礼

度々、お手数をおかけ、お助け下さっていることに感謝致します。
ありがとうございます。
やはり、うまくいきません。
本当に申し訳ありません。
今は、まだ、やり方のどこが悪いのか、研究しているところです。

余談で申し訳ありませんが、
元々、勤務表シートと３１枚の日付を付けた（21、22、…31、1、2、3、…19、20）シートを用意しておき、月毎に変わる「勤務表」シートのA４～A３４の日付を各日付のシートのA2セルに関数で呼び込んで、出欠勤者のリストを１ヶ月分をまとめて作成することにしました。
その理由は、
(1)自分が休日などで、パソコンを触ったこともないパートさんにも、プリントアウトしてもらう必要上、最小限の操作でプリントアウトしてもらえるよう、事前に１ヶ月分用意しておきたかった。
(2)日付シートにはその日の出欠勤者の名前とその日の作業を指示するもので、作業内容を記録として残しておきたい。
当初は、毎日、「今日の出欠勤者」という一枚のシートで日付ごとに呼び出す方法を考えましたが、以上の(1)、(2)の理由から、３１日分前もって作成することとしたのです。
ところが、途中で、勤務の締めの関係で、「月度」始めが２１日から始まり、途中に月末が来る関係で、例えば、２８日までしかない「２月」で見ると、３１日まである大の月を基準にシート名を付けると、「29」シートが３月１日の出欠勤者のリスト、「30」シートが３月２日の出欠勤者のリスト、「31」シートが３月３日の出欠勤者のリストというようにワークシートの日付とずれてしまうことに気がついたのです。
普通のカレンダーの様に１日～３１日迄の順に並んでいれば、２月は「29」～「31」シートを無視するだけで済むのですが・・・。
それなら、日付シートのシート名を勤務表シートの日付によって変えることが出来ればと考えたわけです。
（１ヶ月分のシートを毎月、新調する発想は思いもよらなかったのです。）
そこで、もともと各日付シートのA2にタイトルとして日付を関数で呼び込んでいたので、そのA2の日付を参照して、同一シート内のセルの値（この場合、日付）でワークシートの名前を変えるマクロを各日付シートに仕込みたかったのです。
ところが、ネットで検索したマクロ構文ではうまく行きませんでした。
今にして思えば、関数で呼び込んだ日付が問題だったようなのですが…。
とうとう、行き詰り、困り果てて質問したところ、シートが「日付の変更」で新調できると言うではありませんか。
思はず大感激したものでした。
しかし、マクロ初挑戦の私では、やはり、無理だったのでしょうか。
多くの方々のお手を煩わせた上に、未だ完成させることができません。
関数で呼び込んだ日付では、Worksheet_Changeが起こらないということも今回やっと理解できたくらいです。
どうも、ありがとうございました。

補足

後先が逆になり、申し訳ありませんが、参考）の構文を試して見ましたが、「実行時エラー’１００４’結合されたセルの一部を変更することができません」のメッセージで検証することが出来ませんでした。
ありがとうございました。。

回答No.5

excelist

No1の者です。

No4さんのマクロは以下のように修正すれば動作します。

worksheets("Sheet1").copy(after:=worksheets(worksheets.count))
↓
worksheets("Sheet1").copy after:=worksheets(worksheets.count)

一応No3の補足に対してコメントします。
「例えば勤務表のA4の日付を～～～」という点についてですが、別のシートに「=勤務表!A4」と記述したとして、【そこの日付が変わるタイミング】というのは【「勤務表」シートのＡ４の値が変わるタイミング】となります。今回はさらに【「勤務表」シートのＡ４の値が変わるタイミング】というのが【「勤務表」シートのＢ１またはＥ１の値が変わるタイミング】となります。ですのでWorksheet_Changeは「勤務表」シートに挿入する、という結論に至ります。

なお、Worksheet_Changeというのは「セルが編集されたとき」に動作するマクロで、「セルの値が変わったとき」に動作するというわけではありません。例えばＦ２キーで一旦セルを編集状態にして、値を変えずにＥｎｔｅｒキーを押した場合でもWorksheet_Changeは動作します。また例えば別シートの値を表示する関数（例えば=勤務表!A4）が入力されている場合、別シートの値を変えて「セルの値は変わった」としても「セルは編集されていない」のでWorksheet_Changeは動作しません。関数の結果の表示が変わっただけで、式が編集されたわけではないからです。

このあたりは文字で説明してもややこしいかと思います。もしがんばる気が残っているのであればいろいろなシートに下記のWorksheet_Changeを挿入してみて、どのタイミングで動作しているか確認してみると理解が深まると思います。

Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox "編集されたセル＝" & ActiveSheet.Name & "の" & Target.Address(False, False)
End Sub

お礼

丁寧な、ご指導ありがとうございます。
ご指示に従って、構文の下から５行目を変更しましたが、何もおこりませんでした。
もちろん、No4のkeithin様の条件も勘案し、作業したのですが、うまく行きませんでした。
関数での引用の変更では、うまくいかない理由はよくわかりました。
ありがとうございました。

回答No.4

keithin

結局丸投げで全部の段取りをやってもらわないと、ご自分では収拾できそうにありませんね。
次の通りに作成してください。勝手に違うことをすると失敗します。

準備：
勤務表シートを用意すること
毎日のひな型になるSheet1シートを用意すること

ヤリタイ事：
勤務表でＡ列の日付を計算しなおすと、自動で日数分のシートをSheet1をひな型にして３１枚（または必要数）日付をシート名にして作成させたい

手順：
勤務表シートのシート名タブを右クリックしてコードの表示を選び、現れたシートの記載内容を全部消してから、下記をコピー貼り付ける
＃他のマクロが残っていると動かないので、きれいに全部消すこと

private sub worksheet_change(byval Target as excel.range)
　dim w as worksheet
　dim h as range
　if target.address <> "$B$1" and target.address <> "$E$1" then exit sub

　for each w in worksheets
　if w.name <> "勤務表" and w.name <> "Sheet1" then
　application.displayalerts = false
　w.delete
　application.displayalerts = true
　end if
　next

　for each h in range("A4:A34")
　if h <> "" then
　worksheets("Sheet1").copy(after:=worksheets(worksheets.count))
　activesheet.name = format(h.value, "mmdd")
　end if
　next
end sub

補足

何から何まで、お世話になりありがとうございます。
指示通りにしたのですが、何も起こりません。
構文を入力するとご指示の構文の下から５行目の

worksheets("Sheet1").copy(after:=worksheets(worksheets.count))

が、赤文字になっています。

素人過ぎて申し訳有りませんが、今一度、お助けいただけないでしょうか？

回答No.3

excelist

No1の者です。

どのタイミングでシート名を変更したいのか！を整理しないとですね～

Worksheet_Change というのは、それを記述しているシート内のセルが編集されたときに動作するマクロです。「勤務表」シートのＡ４の値が変更されたときにシート名を変更するのであれば、「勤務表」シートにWorksheet_Change を挿入する必要があります。もしも「勤務表」シートのＢ１やＥ１の値がSheet1の値を元にしている関数で（=Sheet1!A1など）、Sheet1の変更タイミングで名前を変えたいのであればSheet1にWorksheet_Changeを挿入します。

また、今回の場合だとＡ４には関数が入っており、Ｂ１またはＥ１の値が編集されたときにＡ４の表示が変わるので、If Target.Address(False, False) = "A4" Then　ではだめでした（すいません）。
No2さんが書いてくれたコードのように、編集されたセルがＢ１またはＥ１の場合に動作するようにしてください。

最後に名前を変更したいシートの指定方法がいくつかありますので、参考までに記載しておきます。

１．シート名で指定する＝worksheets("Sheet1")
⇒シート名が変わると動かなくなる

２．シート番号で指定する＝worksheets(1)
⇒シートの並び順で1,2,3・・・というように指定できます。今回の場合だとSheet1は左から２番目のシートですのでworksheets(2)と指定できます。シートの並び順を変えると不具合がおきる。

３．シートオブジェクト名を指定する＝Sheet2
シートにはオブジェクト名がついています。シート名が変わってもシートの並び順が変わっても問題はありません。オブジェクト名はＶＢエディタのプロジェクトエクスプローラで確認でき、プロパティウィンドウで変更もできます。シートを挿入する都度Sheet1、Sheet2、Sheet3・・・のような名前になります。対象となるシートを削除しない限り動作するコードになるので有用ですが、あまり知識がない場合はお勧めできないかもです。

補足

お忙しいところを、丁寧に指導頂きましてありがとう御座います。
例えば勤務表のA4の日付をの名前を変更したいシートのA1に
=勤務表!A4
で持って来て、そのA１の日付の変更にて、シート名を変えられないかと、色々ご教示頂いたマクロを組み合わせて、以下の用に記述したのですが、A1セルはやはり関数のせいなのか、根本的に間違っているのか、勤務表の日付を変更しても、まったく反応がありません。
素人知識ではやはり、限界が有ります。
一ヶ月分（３１シート）を毎月度末に作らなければなりません。
何か、よい手法はないものでしょうか？

以下をシート名を変更する各シートに記述しようとしました。

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ERR_HANDLER

If Target.Address(False, False) = "$B$2" Then
ActiveSheet.Name = Format(Range("$B$2").Value, "mmdd")
End If

Exit Sub

ERR_HANDLER:

MsgBox "現在のセルの値はシート名にできません。"

End Sub

回答No.2

keithin

ヤリタイ事：
勤務表のＡ４が変わった事をうけてシート１のシート名を変更したい

手順：
勤務表シートのシート名タブを右クリックしてコードの表示を選ぶ
現れたシートに下記をコピー貼り付ける

private sub worksheet_change(byval Target as excel.range)
　if target.address <> "$B$1" and target.address <> "$E$1" then exit sub
　on error goto errhandle
　worksheets("Sheet1").name = format(range("A4").value, "mm_dd")
　exit sub

errhandle:
　msgbox "シート名を変更できません
end sub

捕捉：
１．マクロを記入するのは勤務表なので間違えないこと
２．Ａ４は計算式なので，今のマクロでは拾えないので間違えないこと
　（どうしたらよかったのかは，回答したマクロを参考に。）
３．「シート名を変えられない」原因になるのは
　a. Sheet1がそもそも準備されていない場合
　b. 変更後のシート名が不適切な場合
　c. 変更後のシート名が「既にある」場合
　などが考えられるので，状況に見て適切に対処を考えること

お礼

ありがとうございました。
指示通りして見ましたら、完成しました。

ところが、一度シート名を変更すた後、勤務表のB1、E1でカレンダーを変更し、シート名を変更しようとしても「シート名を変更できません」となり、変更できません。
私の説明不足で誠に申し訳なかったのですが、同じものを繰り返し変更して使いたいと思うのですが、この場合何か良い手立てはないものでしょうか？
一度変更をかけるとシート名が代わり、補足３、aの条件が満たせなくなるようです。
それと、未熟過ぎて申し訳ないのですが、最初は変更するシート側にマクロを記入するものと思い込んでいたので、あらかじめ用意してある３１日分のシートにすべてコツコツとマクロをコピペして記述すれば、一ヶ月ぶんが出来ると思っていたのですが、一度に一ヶ月分変更できるような方法は無いものでしょうか？
お手数をおかけしますが、今一度、お助け頂けないでしょうか？
よろしく、お願いいたします。

回答No.1

excelist

いくつかの問題点があるようです。

まずWorksheet_Changeをどのシートに挿入しているかです。やりたい事から想像すると「勤務表」シートに挿入していると思われますのでその前提で以降話します。まずは次の１行に問題があります。

If Target.Address(False, False) = "勤務表!A4" Then

Address関数の戻り値の記述が間違っているため、このIf文はFalseと判断されています。

If Target.Address(False, False) = "A4" Then

でよいかと思います。

次にシート名ですが・・・「／」はシート名に使えないような気がします。format関数を用いて適当な文字列に変えてやる必要があるかと思います。（当方Excel2010でないのでウソついてるかも知れないです）

ActiveSheet.Name = format(Range("勤務表!A4").Value,"mmdd")

補足

早々ご回答ありがとうございます。
Worksheet_ChangeをSheet１に入れて、勤務表シートのA4の月日を呼び出して、Sheet1のシート名にしようとしています。

／はやはり、使えないようです。

関数で呼び出した値をシート名にすることは問題ないのでしょうか？