VBA シートがあるはずなのに実行エラー９インデックスが有効範囲にありませんのエラーが出る

Question

if文による条件一致で読みにいくシートを分けるために
下記のコードを作成したのですが、「実行エラー９インデックスが有効範囲にありません」とのエラーが出ます。

Dim ws As Worksheet
         Set ws = Worksheets("レポート")
           If ws = "レポート" Then
          
              Worksheets("レポート").ExportAsFixedFormat _
                Type:=xlTypePDF, _
                fileName:=ExcelFilePath & ExFileName, _
                OpenAfterPublish:=True
          
          Else
              'Worksheets(Array("表紙", "検索", "表示箇所", "行動", "順位")).Select
              'ActiveSheet.ExportAsFixedFormat _
               'Type:=xlTypePDF, _
              'fileName:=ExcelFilePath & ExFileName, _
               'OpenAfterPublish:=True
          
          End if

とりあえずelse文以外は正常に動作するのではと思ったのですが
エクセルにレポートのシートはあるのに何故かエラーが出てしまい困惑しております。
こういった際のデバッグ方法等が不明瞭なので何かアドバイスかヒント頂けると幸いです。
宜しくお願い致します。

参考にした記事：　https://www.tipsfound.com/vba/19009

OS：macOS Catalina バージョン10.15.3
Excelバージョン： Microsoft Excel for Mac バージョン16.39

Answer 1

お望みのことは、
"レポート"という名前のレポートが存在したら、そのレポートをPDF出力、
存在しなければ、
"表紙", "検索", "表示箇所", "行動", "順位"シートをまとめてPDF出力ということですね。

    Dim ws As Worksheet
    
    On Error Resume Next
    Set ws = Worksheets("レポート")
    Select Case Err.Number
    Case 0
         On Error GoTo 0
         Worksheets("レポート").ExportAsFixedFormat _
             Type:=xlTypePDF, _
             Filename:=ExcelFilePath & ExFileName, _
             OpenAfterPublish:=True

    Case 9
        On Error GoTo 0
        Worksheets(Array("表紙", "検索", "表示箇所", "行動", "順位")).Select
        ActiveSheet.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:=ExcelFilePath & ExFileName, _
            OpenAfterPublish:=True
    Case Else
        MsgBox "エラー:" & Err.Number & " " & Err.Description
        On Error GoTo 0
    End Select

よく見たら、上記のコード、なんか遠回りのことをしてますね。

    Dim ws As Worksheet
    
    On Error Resume Next 'エラー無視
    Set ws = Worksheets("レポート")
    On Error GoTo 0 'エラー無視解除
    If ws Is Nothing Then '"レポート"シートが存在しなければ、
        Worksheets(Array("表紙", "検索", "表示箇所", "行動", "順位")).Select
        ActiveSheet.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:=ExcelFilePath & ExFileName, _
            OpenAfterPublish:=True
    Else
        ws.ExportAsFixedFormat _
             Type:=xlTypePDF, _
             Filename:=ExcelFilePath & ExFileName, _
             OpenAfterPublish:=True
    End If

Comments

[taka-hoop]

ご回答ありがとうございます！
まさにおっしゃる通りです。
一応下記のように丸々コードコピーして使わせてもらったのですがやはり同じインデックスが有効範囲にないとのエラーが出てしまいます。
下記作成したマクロの全貌です。
#########
Sub EXCELファイルPDF化03() 'フォルダのEXCELファイルの一括変換

Dim Button, T, I, L As Integer
Dim OpenExcelFileName, ExcelFileName, ExcelFilePath, ExFileName As String

Application.DisplayAlerts = False '確認メッセージを無効化します。

Button = MsgBox("EXCEファイルの一括PDF変を行いますか？", vbYesNo + vbQuestion, "確認")
If Button = vbYes Then

OpenExcelFileName = Application.GetOpenFilename 'ダイアログを表示取り込むフォルダーにあるファイルを選択します。

If OpenExcelFileName <> "False" Then
ExcelFileName = Dir(OpenExcelFileName) '指定したファイルパスからファイル名を代入します。
ExcelFilePath = Replace(OpenExcelFileName, ExcelFileName, "") '指定したファイルパスを指定します。（ファイルパスからファイル名を取り除く）

MsgBox ExcelFilePath & "この選択フォルダからPDFに変換します。"
Else
MsgBox "キャンセルされました"
Exit Sub 'キャンセルでプログラムを終了します。

End If

ExFileName = Dir(ExcelFilePath & "*.xls?") '指定したフォルダーから一件目のEXCELファイルを指定します。

Do While ExFileName <> "" '読み込むファイルがなくなるまで繰り返す。

'ActiveWorkbook の記述はいらなかった
'レポートの記述にいらないスペースがあったかも
Dim ws As Worksheet

On Error Resume Next 'エラー無視
Set ws = Worksheets("レポート")
On Error GoTo 0 'エラー無視解除
If ws Is Nothing Then '"レポート"シートが存在しなければ、
Worksheets(Array("表紙", "検索", "表示箇所", "行動", "順位")).Select
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
fileName:=ExcelFilePath & ExFileName, _
OpenAfterPublish:=True
Else
ws.ExportAsFixedFormat _
Type:=xlTypePDF, _
fileName:=ExcelFilePath & ExFileName, _
OpenAfterPublish:=True
End If

ActiveWindow.Close '読み込んだファイルを閉じます。

ExFileName = Dir() '次のファイルを指定する。

Loop

MsgBox "PDFファイルに一括変換しました。"
Else
MsgBox "処理を中断します"
End If

Application.DisplayAlerts = True '確認メッセージを有効化します。

End Sub

[hatena19]

Dir関数で指定したフォルダー内のエクセルファイルのファイル名を取得はしてますが、そのファイルを開いてませんね。
Worksheets("レポート") だと、現在アクティブなブックのシートを参照することになりますが、それには"レポート"はないのでは。

Dir関数で取得したファイル名のブックに"レポート"シートがありそれをPDF出力するのなら、そのブックを開かないとだめでしょう。

【エクセルVBA】ワークブックを開くWorkbooks.Openの使い方とエラーの対処法
https://www.excelspeedup.com/workbookopen/

[taka-hoop]

ご返答ありがとうございます！
一応下記コードであれば分岐などはできないですがレポートのあるファイルは自動でPDF出力ができるのですが、Dir関数で取得したファイル名がおかしいという事でしょうか？

非常に詳しく説明して頂いてるのに理解が及んでおらず大変申し訳ないのですが
宜しくお願い致します。

以下レポートだけPDF出力できるコードです
###############################

Sub EXCELファイルPDF化03() 'フォルダのEXCELファイルの一括変換

Dim Button, T, I, L As Integer
Dim OpenExcelFileName, ExcelFileName, ExcelFilePath, ExFileName As String

Application.DisplayAlerts = False '確認メッセージを無効化します。

Button = MsgBox("EXCEファイルの一括PDF変を行いますか？", vbYesNo + vbQuestion, "確認")
If Button = vbYes Then

OpenExcelFileName = Application.GetOpenFilename 'ダイアログを表示取り込むフォルダーにあるファイルを選択します。

If OpenExcelFileName <> "False" Then
ExcelFileName = Dir(OpenExcelFileName) '指定したファイルパスからファイル名を代入します。
ExcelFilePath = Replace(OpenExcelFileName, ExcelFileName, "") '指定したファイルパスを指定します。（ファイルパスからファイル名を取り除く）

MsgBox ExcelFilePath & "この選択フォルダからPDFに変換します。"
Else
MsgBox "キャンセルされました"
Exit Sub 'キャンセルでプログラムを終了します。

End If

ExFileName = Dir(ExcelFilePath & "*.xls?") '指定したフォルダーから一件目のEXCELファイルを指定します。

Do While ExFileName <> "" '読み込むファイルがなくなるまで繰り返す。

'ActiveWorkbook の記述はいらなかった
'レポートの記述にいらないスペースがあったかも
Workbooks.Open fileName:=ExcelFilePath & ExFileName, ReadOnly:=True, UpdateLinks:=0 'EXCELファイルを読み取り専用で読み込む
ExFileName = Left(ExFileName, InStr(ExFileName, ".") - 1) ' ファイル名から拡張子を取り除く（.xls?）
Worksheets("レポート").ExportAsFixedFormat _
Type:=xlTypePDF, _
fileName:=ExcelFilePath & ExFileName, _
OpenAfterPublish:=True

ActiveWindow.Close '読み込んだファイルを閉じます。

ExFileName = Dir() '次のファイルを指定する。

Loop

MsgBox "PDFファイルに一括変換しました。"
Else
MsgBox "処理を中断します"
End If

Application.DisplayAlerts = True '確認メッセージを有効化します。

End Sub

[hatena19]

今度のコードは Workbooks.Open でブックを開いてますね。

まず、ブックを開く、そのブックに"レポート"シートが存在するかチェック、それからPDF出力、という処理にしないとダメですよ。ブックを開かずにシートチェックすれば当然エラーになります。

よく、考えて処理を組んでください。

それでもうまくいかないなら、この質問の本筋からずれてきますので、もう一度新規に質問を立て直した方がいいと思います。このコメントスペースで続けるのはつらいです。

[taka-hoop]

ご返答ありがとうございます！

ご丁寧に教えて頂き本当にありがとうございます！
hatena19さんに頂いたコードと解説のおかげでかなり道が開けたように感じました！
ブックを開いてシートチェックをちょっと意識して考えてみます

記述して頂いたコードをもっとじっくり読み解き処理を考えてみます！
それでもまた詰まるようならまた質問させて頂きたいと思います！

本当にご丁寧にありがとうございました！

Answer 2

全部検証していませんが、この部分がすでにおかしいです・

If ws = "レポート" Then

→正
if ws.name = "レポート" then

sub test()

dim i as doubule
dim str as string

For i = 1 To Worksheets.Count

  str = Sheets(i).Name
　　　
　　if str="レポート" then
      処理
    else
　　　　処理
    end　if
  
next

end sub

Comments

[taka-hoop]

ご回答ありがとうございます！
ご指摘頂いた通りに修正を試みたんですが、同じエラーが出てしまいます。
せっかく教えて頂いてたのにタイポ申し訳ありません！

[mako1972]

なるほど。この場合、VBAのサンプルで提示したように、setにこだわらず
したほうがいいですね。

[mako1972]

一旦取り消しますね。
よく考えると他のレスの継続案件ですね。
確か、PDFを１ページ目に作るということ。
今回の質問は１ページとそれ以外で
PDFを作りたいという質問ですか？？

最強VBA１位のhatena19さんが回答されていますので、そちらにのっかるほうが良いと思います。

[taka-hoop]

ご返答ありがとうございます！
やりたい事としてはエクセルを開いてDBと言うシート以外をPDFで保存を繰り返すと言った感じで
レポートとDBがセットのエクセルファイルとDBと"表紙", "検索", "表示箇所", "行動", "順位"がセットのエクセルファイルがあるフォルダを見に行って自動でPDF出力したい言う感じでございます！

[mako1972]

ちょい。
VBAランク１位のすごい人が回答してんのよ＾＾。

[taka-hoop]

ご返答ありがとうございます！
ご指摘ありがとうございます！お返事させて頂きました！