Excelセル参照できない、シートのコピーをするとメッセージが出て終了できなくなる

最近、Excelで資料を作成した際に、立て続けにエラーと言いますか、まさかの事象(×2)に遭遇しました。

その事象と解決方法を残しておきます。

Excelのバージョンは 2010 です。

1.セル参照できない

(1) 事象

一つ目は、セル参照しようとしたのに、何故か、その内容が文字列と解釈されているようで、そのまま文字列が表示されてしまうというものです。

Excelを使用されている方でしたら、セル参照はご存知だと思いますが、あるセルの内容と同一の値を出したい場合に「=セル位置」(ex.=A3)で指定するものですね。

今回の状態は次のようなものです。

セル A7 にある版数の内容を参照したのですが、ご覧のように文字列「=A7」が表示されてしまいます。

これでは、セル参照の意味を持ちませんね。「さあ困った」・・・。

(2) 解決方法

この場合の解決方法を示します。

まず、セル参照できないセルで、右クリックし、「セルの書式設定」をクリックします。

次に表示形式をクリックしてください。

表示形式を見ると「文字列」になっていますね。

「標準」をクリックして変更します。

ここで、F2キー(編集)をクリックします。

Enterをクリックすると、セル参照した値が表示されるはずです。

2.Excelのワークシートのコピーで

(1) 事象

Excelでワークシートを同じブック内にコピーしようとしたんですが、以下のメッセージが表示されました。

メッセージの内容を示します。

「移動またはコピーしようとしている数式またはシートには、移動またはコピー先のワークシートに既にある名前○○○が含まれています。この名前を使用しますか?
・コピーまたは移動先のシートに定義されている名前を使用する場合は、[はい]をクリックします。
・数式またはワークシートで参照する範囲の名前を変更する場合は、[いいえ]をクリックし、[名前の重複]ダイアログボックスに新しい名前を入力します。」

「はい」をクリックすると、エラーメッセージの〇〇〇が変更されて、同じメッセージが表示されます。

赤枠の内容が先ほどと変わっています。

何度、「はい」をクリックしても、延々と表示されてしまいます。

「閉じる」ボタン、または「いいえ」をクリックすると、「名前の重複」ウィンドウが表示されます。ここで適当な名前を入力しても、「変更前」が 変更されて、同じメッセージが表示されます。

こちらも、 何度、適当な名前を入力しても、延々と表示され続けます。

こちらも、赤枠の内容が変わります。

やってられません!

結局、タスクマネージャーを起動して、Excelを強制終了させるしかないようです。

(2) 解決方法

以下の画像はExcel2010ですが、「数式」タブから「名前の管理」ボタンをクリックしてみましょう。

「名前の管理」に定義された名前が山ほど表示されました。しかも、何かエラーが出ているようです。

これらを全て選択して削除しましょう。

これで、シートのコピーができるはずです。ところが、再び、同じメッセージが表示されたのです。

(3) 新たな事象と解決方法

色々ググると、上述した(1)、(2)で終わっているサイトの他に、以下の問題を指摘しているサイトがありました。

非表示になっている名前の定義が存在する

この事象を解決するには、VBAを使用して、見えない名前をみえるようにしなければなりません。

少し、面倒ですが、参考サイトの通りに実施したところ、見事に問題をクリアできました。

以下、ご確認ください。

① Visual Basic Editor 起動

まずは Alt + F11 で Visual Basic Editor を起動します。

② Module 追加

次に Alt + I + M で Module を追加します。

以下のコードをコピーして貼り付けます。このコードは、非表示の名前の定義を全て表示させるための処理です。

Public Sub VisibleNames()
Dim name As Object
For Each name In Names
If name.Visible = False Then
name.Visible = True
End If
Next
MsgBox “すべての名前の定義を表示しました。”, vbOKOnly
End Sub

コピーが終わったら、Visual Basic Editor を閉じてください。

③ マクロの実行

次に、上記で作ったマクロを実行します。

メニューから「表示」タブを選び「マクロ」>「マクロの表示」を選びます。

一番、右側にあります。

実行できる処理が一覧で出ます。60爺の場合は、一つしか出ませんでした。この VisibleNames を選んで「マクロの実行」をクリックします。

次のダイアログが表示されたら処理完了です。

④ 再び「定義名称」削除

上記 (2) で行った定義された名前を削除します。

「数式」タブから「名前の管理」ボタンをクリックしてみましょう。

先程クリアしたのに、今回のマクロ処理により、新たな名前が追加されていますね。しかも、またもやエラーが出ているようです。

これらを選択して削除しましょう。

これで、sheetのコピーを行うと何の問題もなくコピーが出来ました。

なお、以下の記事も同様の対処をしています。

Excelのシートのコピーで名前の重複による膨大なエラー解消方法

この方法は、再度の定義名称削除が不要なので楽だと思います!

※参考
[Excel] シートで非表示になっている名前の定義を消す方法