VBAがうまく動きません。

質問

エクセルVBAで実行時は正確に動かないが、ステップインでは正常に作動するのはなぜですか？

入力シートに入力された情報を元にデータシートから抽出を行い、新規シートを開き、そこでリストにしたい情報のみを編集（不要なタイトル行などの削除）して、自動で貼り付けと名前の定義を行うマクロを作っています。

ステップイン[F8]や実行[F5]では正常に作動するのですが、実際に使用してみると、抽出データが貼り付けされていない状態(セルは空白)となりますが、名前の定義は抽出データと同じ行まで定義されているので、貼り付けのみ上手くいっていないように思われます。

下記が作成したコードです。情報が足りないようでしたら、申し訳ありません。
お手上げ状態となっていますので、お力添えいただけると幸いです。

Dim syurui as String
Dim suuryou as Integer
Dim target as Range

Private Sub Worksheet_Change(ByVal target As Range)
If Intersect(target, Range("D7")) Is Nothing Then
Exit Sub
Else
Call 抜出
End If
End Sub

Sub 抜出()

Worksheets("データ").Activate
’後に出てくる名前初期化でエラーを防ぐため仮定義
ActiveWorkbook.Names.Add Name:="番号", _
RefersTo:=Worksheets("データ").Range("K2")

syurui = Worksheets("入力").Range("D9").Value

Worksheets("データ").Select
Set target = Worksheets("データ").Range("M2")
With Worksheets("データ").Range("D1")
.AutoFilter field:=4, Criteria1:=syurui
.CurrentRegion.SpecialCells(xlVisible).Copy
With Worksheets.Add
.Paste
’不要な行を削除
.Rows(1).Delete
.Range("A:D").Delete
.Range("B:F").Delete
’抽出した情報を貼り付け＆新規シート削除
.UsedRange.Copy target
Application.DisplayAlerts = False
.Delete
Application.DisplayAlerts = True
End With
.AutoFilter
End With

’抽出データの最終行を調べる
suuryou = Worksheets("データ").Cells(65536, "M").End(xlUp).Row

If suuryou = 1 Then
Worksheets("入力").Activate
Exit Sub
Else
Range("番号").Name.Delete
ActiveWorkbook.Names.Add Name:="番号", _
RefersTo:=Worksheets(”データ").Range("M2:M" & suuryou)
End If

Worksheets("入力").Activate
End Sub

回答No.2

mitarashi

#1です。それらしいモデルを作成して、ご提示のコードを入力シートのシートモジュールに貼り付けてやってみました。
Worksheets("入力").のD7セルのチェンジイベントで実行させたところ、
Range("番号").Name.Deleteでエラーが出て止まってしまいますね。
ActiveWorkbook.Names("番号").Deleteに書き換えると動作し、データの抽出・貼り付け、名前定義共に動作している様にみえます。当方xl2010です。
ただ、お示しのコードには、Worksheets(”データ")の抽出データ貼り付け先のクリアがないので、以前の残骸が残る事があります。

回答No.1

mitarashi

個々の部品にはおかしなところはなさそうですが、Sub 抜出は標準モジュールに書かれているのでしょうか？

下記を試してみてはいかがですか。

１．Sub 抜出がシートモジュールに書いてあるなら標準モジュールに移す。
２．モジュールレベルの変数は、いずれも Sub 抜出内での宣言で十分そうなので、移動する。
３．Withの入れ子はやめる。（ちょっと嫌な思い出があるもので）
４．他のシートに移らないでも処理ができる内容なので、Activateは止めて、ワークシート明示での実行とする。
　Range(hoge)とか、Cells(hoge)とかいったものには、すべてワークシート名を修飾する必要があります。
５．標準モジュールで使う分にはトラブルの元にはなっていないと思いますが、Targetという変数名はやめてユニークなものにする（例：TargetRange）

追伸：ワークシートの状況等明示いただけると、試しに動かしてみようかなという気になる回答者もいらっしゃるかも。