【ExcelVBA】インデックスが有効範囲にありません。の理由が分かりません

下記、VBAソースの一部ですが、やりたい処理は、
本体のマクロブックのSheet1のA2セルにフォルダのパスが、
C2、D2セルにセル位置が記載されています。
そのフォルダ内のすべてのエクセルを順番に開いて指定シートの指定範囲をコピーしたいのです。
各ブックのシート名は異なるのですが、VBAエディタから見たシートの番号はすべて同じなので、シート番号から特定して処理しようと考えました。

開いたブックのSheetIDが28のシートに対して、LeftUp(左上)から右下(RightDown)の範囲をコピーしたいのですが、
「インデックスが有効範囲にありません。」というエラーでストップしてしまいます。

LeftUp = Cells(2, 3).Value ' C2
RightDown = Cells(2, 4).Value ' D2
buf = Dir(Sheets("Sheet1").Range("A2").Value & "*.xlsm")

Workbooks.Open Worksheets("Sheet1").Range("A2").Value & "\" & buf, UpdateLinks:=0
Worksheets(28).Range(LeftUp & ":" & RightDown).Copy

原因を教えて頂けると助かります。

No.3 回答者： Qchan1962 回答日時： 2024/03/17 16:21

既に解決されていると思いますが

VBAのオブジェクトの扱いには癖があるのかも知れません
下記はブックオブジェクト名、ブック名　シートオブジェクト名、シート名をA,B,C列に書き出す　処理コードです
新規シート（A:C列に書き込みをしてよいシート）を表示して試してください

Sub test()
Dim VBcomp As Object
Dim i As Integer
For Each VBcomp In ThisWorkbook.VBProject.VBcomponents
If VBcomp.Type = 100 Then
i = i + 1
Cells(i, 1).Value = VBcomp.Name
If VBcomp.Name = "ThisWorkbook" Then
Cells(i, 2).Value = "←Workbook_Object：ブック名→"
Else
Cells(i, 2).Value = "←Worksheet_Object：シート名→"
End If
Cells(i, 3).Value = VBcomp.Properties("name")
End If
Next
End Sub


ThisWorkbook.VBProject.VBcomponentsの中から
VBcomponentを抜き出し.Nameでオブジェクト名を取得しています
また、所謂　ブック名、シート名はオブジェクトのプロパティから取得しています
VBcomp.Properties("name")

シートを扱う際にWorksheets ("シート名")とかSheets ("シート名")とすると思いますが　オブジェクト（名）をそのまま使用する事も出来ます
シートオブジェクト名が　Sheet1　の場合
Sheet1.Range("A1").Activate
この場合　Sheet1のProperties("name")は不問です

ブックで考えると馴染みがあるかも知れません
myBookPath = ThisWorkbook.Path
ここで使われている　ThisWorkbook　がブックのオブジェクト名になります
ブック名はThisWorkbook.Properties("name")で取得されるxxx.xlsmです
（マクロ有効ブックで保存済みの場合）
つまり書き方として　
myBookPath = Workbooks("xxx.xlsm").Path
myBookPath = ThisWorkbook.Path
は同じパスが返ります

シートオブジェクト名も変更が可能ですのでVBEが誰でも開ける環境では
別の方法でシートを特定する必要があるかも知れません

No.2 回答者： fujillin 回答日時： 2024/03/13 12:04

No1です。

＞左側にシート名が縦に並んで表示されますよね。
多分、エディター画面の「プロジェクト エクスプローラー」と言われるもののことだと想像します。
https://learn.microsoft.com/ja-jp/office/vba/lan …
エディター画面のレイアウトは自由に変更できますし、各ペインも別ウィンドウとして表示（/非表示）することも可能なので、固定ではありませんから・・

仮に、プロジェクト エクスプローラーで、
　Sheet9(hoge)
のように表示されている場合、()内の「hoge」がシートのタブに表示されるシート名です。
これに対して「Sheet9」の部分が、No1に挙げたオブジェクト名（＝コードネーム）にあたります。
シート名を用いてシートを指定する場合は、
　Worksheets("hoge")
のように指定しますが、コードネームで指定する場合は、直接に、
　Sheet9
のようにして指定することが可能です。
例えば、
　MsgBox Sheet9.Range("A1")
とすれば、コードネームSheet9のシートのA1セルの値が表示されます。
（Sheet9というコードネームのシートが存在しなければエラーになります）

とは言え、このコードネームも変更が可能です。
リボンの開発のプロパティから、シートのプロパティの「オブジェクト名」を変更すれば、コードネームを変更できます。
ですので、コードネームを利用する場合であっても、安全を期すのなら、ブック内に当該シートが存在することを確認する処理を入れておくのが確実でしょう。
（シートのコードネームは、No1に挙げた方法で取得できます。）

また、固定のシートとして利用するのなら、デフォルトで設定される「Sheet〇〇」のようなコードネームではなく、特別な名称に設定しておく方が、いろいろな操作によって紛れてしまう可能性を防止できると考えられます。
（例えば、シートをコピーして前のシートを削除したりした場合）

この回答へのお礼

ありがとうございます。
まだ解決できてません。
「プロジェクト エクスプローラー」に、
Sheet28(鈴木次郎)となっている時、

Sheets(”鈴木次郎”).Range(LeftUp & ":" & RightDown).Copy
と同じ動きを、コードネームで指定する場合はどう記述すれば良いでしょう？

本来、当該シートの存在チェックとか必要なのは理解していますが、
このブックに関しては、数人で使用するもので、シートの複製をしないという前提になっている運用になっているのでチェック不要です。
ブックをもらった人はSheet28のシート名を自分の名前に書き換えるだけです。

ここでは、フォルダ内のブックのSheet28の(LeftUp & ":" & RightDown)範囲を、マクロ実行しているブックの「出力」シートの上から順に書き出していきたいのです。

お礼日時：2024/03/13 13:31

No.1 回答者： fujillin 回答日時： 2024/03/13 01:20

こんばんは

どこでエラーが出ているのか記載がありませんけれど、勝手な想像で
＞Worksheets(28).Range(LeftUp & ":" & RightDown).Copy
ではないかと仮定しました。
違う場合は、以下はお門違いですのでスルーしてください。


想像するところ、原因は以下の2点のどちらかではないでしょうか？

＞SheetIDが28のシートに対して～
SheetIDなるものは存在しないと思いますが、何を意図しているのでしょうか？
シート固有の名称としてはオブジェクト名：Worksheet.CodeName は存在しますけれど・・
ご提示の
＞Worksheets(28)
は、ブックのシートタブの左から順に28番目のシートを意味します。
（順序を入れ替えても28番目のシートなので、固定のシートを意味しません）
開いたブックに、シートが28シート以上存在すればエラーにはなりませんけれど、28番目のシートが存在しなければここでエラーになります。

＞Range(LeftUp & ":" & RightDown)
引数が有効なセル範囲を指定する文字列になっていなければエラーになります。
　MsgBox LeftUp & ":" & RightDown
などとして、指定範囲がどうなっているのかを確認してみてください。

この回答へのお礼

エラー箇所は仰る通りの箇所です。
VBAのコードを書く画面では、左側にシート名が縦に並んで表示されますよね。
sheet1、sheet2、、、と作成した順に番号が振られてます。
何度もシートを作り直した経緯があるので28が振られてます。
この番号で指定する方法はないんでしたっけ？
左からの順番指定では都合が悪いです。
シートを移動させてしまう人がいるかも知れないのでどこに居ようと特定したい。しかしシート名に使用者の氏名を入れるのでシート名では特定できないのです。

お礼日時：2024/03/13 01:59