Excelマクロが別シートで止まる？1004エラーの直し方

「売上シートを開いていると動くのに、別のシートから実行すると止まる」

「同じマクロなのに、実行する人によって結果が変わる」

こんなときは、RangeとCellsが同じシートを見ているか確認してみてください。

今回は、実行時エラー1004のうち「参照するシートが混ざっているケース」の直し方です。1004にはほかにも原因があるため、エラー番号だけで決めつけないことが大切です。

■ まず、元のファイルを残します

ファイルを閉じた状態でコピーし、「売上集計_修正テスト.xlsm」のような別名にしてください。元のファイルはそのまま残します。

なお、メール送信や外部ファイルの上書きを行うマクロは、ブックをコピーしただけでは安全になりません。中身が分からない場合は元のマクロを繰り返し動かさず、後半の読取専用サンプルから確認しましょう。

操作手順はWindows版のデスクトップExcelを想定しています。会社のマクロ利用ルールは守り、実行のためにセキュリティ設定を一括で緩めないでください。

■ 原因は「売上シート」と「いま表示中のシート」の混在

たとえば、標準モジュールに次のようなコードがあるとします。

With ThisWorkbook.Worksheets("売上")

Set target = .Range(Cells(2, 1), Cells(10, 3))

End With

一見すると、「売上」シートのA2:C10を選んでいるように見えます。

でも、Withの中に書いたCellsでも、前にドットがなければWithの相手を参照しません。この標準モジュールの例では、Cellsがいま表示中のシートを見ます。

・.Range → 「売上」シート

・Cells → いま表示中のシート

別のシートを表示していると、異なるシートのセルで範囲を作ろうとして、エラーになる原因になります。

※シートのコード画面に書いたマクロでは、省略した参照先の扱いが異なります。この記事のサンプルは「挿入→標準モジュール」に置いてください。

■ 直す場所は、Cellsの前のドットです

先ほどの部分を、次の形にします。

With ThisWorkbook.Worksheets("売上")

Set target = .Range(.Cells(2, 1), .Cells(10, 3))

End With

これでRangeも2つのCellsも、同じ「売上」シートを参照します。

Withを使わずに書くなら、次の形でも構いません。

Set target = ws.Range(ws.Cells(2, 1), ws.Cells(10, 3))

こちらは、あらかじめwsに対象シートを設定しておく必要があります。

■ データを書き換えず、参照先だけ確認するサンプル

いきなり本番の転記処理を変えるのが不安なときは、空の練習用ブックで試せます。

1. 新しいブックを作り、シートを2枚用意します。

2. シート名を「売上」と「確認用」にします。セルへの入力は不要です。

3. 「練習用.xlsm」として保存します。

4. Alt＋F11を押し、左側で「練習用.xlsm」のプロジェクトを選びます。

5. 「挿入→標準モジュール」を選び、次のコードを貼り付けます。

Option Explicit

Public Sub CheckRangeSafely()

Dim ws As Worksheet

Dim target As Range

Set ws = ThisWorkbook.Worksheets("売上")

With ws

Set target = .Range(.Cells(2, 1), .Cells(10, 3))

End With

MsgBox "ブック：" & ws.Parent.Name & vbCrLf & _

"シート：" & ws.Name & vbCrLf & _

"範囲：" & target.Address(False, False), _

vbInformation, "参照先の確認"

End Sub

6. Excelの画面へ戻り、「確認用」シートを表示します。

7. Alt＋F8を押し、「CheckRangeSafely」を選んで実行します。

次の内容が表示されれば、この範囲の参照先は指定どおりです。

ブック：練習用.xlsm

シート：売上

範囲：A2:C10

「売上」シートを表示してもう一度実行しても、同じ内容になることを確認してください。

このサンプルは、セルの値や数式の変更、削除、保存、外部送信を行いません。表示したシートに左右されず、どこを参照しているかを確認するためのコードです。

■ ThisWorkbookへの置き換えには注意

ThisWorkbookは「このコードが入っているブック」を指します。

個人用マクロブックのPERSONAL.XLSBやアドインにコードを入れている場合は、作業中の売上ファイルを指すとは限りません。その状態で一律にThisWorkbookへ変更すると、別の問題が起きます。

このサンプルでは、コードと「売上」シートを同じ練習用ブックに置いてください。

また、「売上」というシートがなければ、別のエラーである「実行時エラー9」になります。実ファイルで使う際は、タブに表示されている名前と、コード内の名前を一致させます。

■ 本番の修正では、エラーが消えた後も確認します

読取専用サンプルで参照先を確認したら、テスト用コピーの元コードを見直します。Rangeだけでなく、CellsやRows.Countなども、意図したブック・シートを参照しているか確認してください。

元のマクロを安全に試せる状態で、次の3点をチェックします。

・別のシートを開いて実行しても、結果が変わらないか

・転記件数と合計が、手作業で確認した結果と一致するか

・意図していないシートやファイルが書き換わっていないか

まだ1004で止まる場合は、シート保護、Selectを使った操作、保存先など、別の原因が考えられます。On Error Resume Nextを足して無理に先へ進めると、処理漏れを見逃すことがあるため避けましょう。

まずは「どのブックの、どのシートの、どのセルか」をそろえる。開くシートによって動作が変わるマクロは、ここから確認すると原因を絞りやすくなります。

既存マクロの修正を相談する場合は、エラー画面、止まった行、正常時の出力例があると状況を伝えやすくなります。共有するサンプルは、個人情報や機密情報をダミーに置き換えてください。

Excel・VBAのご相談：

https://coconala.com/services/4437755

■ 動作確認の範囲

2026年10月8日、Windows版Excel 16.0の範囲参照をCOM（Excelを外部から操作する仕組み）経由で確認しました。「売上」を表示中、「確認用」を表示中、別ブックを表示中の3条件で、正しい参照はすべて売上!A2:C10になりました。異なるシートの参照を混ぜた式では、Excelエラー1004を再現しました。

記事に載せたVBAマクロそのものの実行と、メッセージ画面の表示は未検証です。上の練習用ブックで、利用環境での結果を確認してから適用してください。

参考：Microsoft公式ドキュメント

Worksheet.Range

Worksheet.Cells

Application.ThisWorkbook

Range.Address