VBA（アプリケーション定義またはオブジェクト定義のエラー時の対応について）

前提・実現したいこと

①支店シートのB9～（空白行前）までに入力してある支店コード名のシートを作成し、
シートのA2にその支店コードを入力させたいです。
② ”☆”がつくシートを削除したいです。

発生している問題・エラーメッセージ

すべての支店コード分のシートが正常に作成され、値貼～不要な行削除が行われたあと、
②が実施されずに以下エラーとなりました。

実行時エラー'1004':
アプリケーション定義またはオブジェクト定義のエラーです。

＜黄色網掛け行＞
.Name = 支店コード.Value

該当のソースコード

VBAです。

Sub 作成()

①Dim 支店コード As Range
For Each 支店コード In Worksheets("支店").Range("B9", Worksheets("支店").Cells(Rows.Count, 2).End(xlUp))
Worksheets("☆原本").Copy After:=Worksheets(Worksheets.Count)
With ActiveSheet
.Name = 支店コード.Value
.Range("A2") = 支店コード.Value
End With

　（値貼）※コード省略
（並び替え）※コード省略
（不要な行削除）※コード省略

　Next 支店コード

②Dim WS As Worksheet
Dim Target As Variant
Dim i As Long
Target = Array("☆")
For Each WS In Worksheets
For i = 0 To UBound(Target)
If InStr(WS.Name, Target(i)) >= 1 Then
Application.DisplayAlerts = False
WS.Delete
Exit For
Application.DisplayAlerts = True
End If
Next i
Next WS

End Sub

試したこと

エラーになった後、②部分のコードのみを実施しましたが、エラーなく完了しました。

補足情報（FW/ツールのバージョンなど）

先ほど、別の質問で①の部分のコード（3行目For each~）を修正いただきましたので、続くコードが誤っているか
「支店」シートの支店コードに問題があるのではと考えています。

hex309

2021/11/02 08:35

エラー時の「支店コード」の値はわかりますか？

VVVVV

2021/11/02 11:46 編集

ご質問有難うございます。空白前の最後の支店コードは、"０６０”でした。このあと、異なる支店群団の支店コード（複数）を貼りなおして試しましたが、同様にすべての支店コード分のシートが正常に作成されたあと、”原本☆（２）”というシートが作成され、エラーとなりました。 .Name = 支店コード.Value の前に On Error Resume Next　を入れてみましたが、”原本☆（２）””原本☆（３）”・・・とシート作成が続いていったので、ココ↓で空白が空白として認識されていないのではないかと推測しています。 For Each 支店コード In Worksheets("支店").Range("B9", Worksheets("支店").Cells(Rows.Count, 2).End(xlUp)) 説明を割愛していましたが、「支店コード」も手入力をしたわけではなく、エクセルの関数を利用して他のシートからもってきていたものです。関数実施の結果を以下のコードで値にかえ、その結果、ある行から下は「支店コード」のない空白行のみになっています。 Range("A9:R73").Copy Range("A9").PasteSpecial _ Paste:=xlPasteValues, _ Operation:=xlNone, _ SkipBlanks:=False, _ Transpose:=False

Usirow

2021/11/04 01:46

気になって試してみましたが、シート名に空白を指定しようとしてエラーを吐いているようですね。関数による生成の場合、見た目空白のセルでも、実際は""（文字数０の文字列）が入っていて空白とみなされていない場合があるので、Worksheets("支店").Range("B9", Worksheets("支店").Cells(Rows.Count, 2).End(xlUp))が本当に狙い通りの範囲を指定しているかを確認をした方がいいかもしれません。

行動規範の内容に同意します

回答1件

ソース見ないまま書きます（すいません）
経験的によくあるエラーなので推測で書きます。

sheetが異なるところで命令が走ると1004よく出るんですよね。
自分ではActiveだと思っていてもそうではない状態になってると思います。
Sheet名を明確に指定してやってみると解決すると思います。

投稿2021/11/02 14:16

YagiYukio

総合スコア15

VVVVV

2021/11/02 14:54

ご回答有難うございます。ただ、支店シートのB9～（空白行前）までに入力してある支店コード名のシートを作成し、シートのA2にその支店コードを入力させるという対応は、空白行前までの支店コード分においては成功しているのです。そこで次の指令（②）にうつればいいのですが、何らかの理由でループし空白セルのシートが作成されている（名前が空白のままシートが作成できないので、おそらくコピー元の名前に（２）をつけた"☆原本（２）”になっている）のではと考えていますが、どうでしょうか。