シートを配列にいれることはできますか？

１つのブックの中に約40ぐらいシートがあり、そのうちの約30ぐらいのシートの最終行と最終列を知りたいのです。

シート名は統一されていないのでいろいろな名前になっています。

今は
Set ws1 = Sheets("田中")
Set ws2 = Sheets("本田")
というように各シートをws1, ws2...としてVBAで処理しています。

これを以下のようにすることはできないのでしょうか？
MyArray = Array("田中","本田","南","上田",...)
For i = 1 to 30
Set ws & i = Sheets(MyArray(i))
Next i

ネットで検索しましたが探しきれませんでした。識者の方、アドバイスお願いいたします。m(__)m

No.1 ベストアンサー 回答者： 初心者ですが何か。 回答日時： 2023/06/04 19:23

dim ws(1 to 30) as worksheets

MyArray = Array("田中","本田","南","上田",...)
For i = 0 to 29
Set ws(i + 1) = Sheets(MyArray(i + 1))
Next i

未検証ですがこうとかかな？

この回答へのお礼

忙しい中、回答ありがとうございます。

配列は０からでしたね。失念しておりました。

お礼日時：2023/06/04 22:49

No.3 回答者： 初心者ですが何か。 回答日時： 2023/06/05 08:52

ちょっとミスでした。

＞Set ws(i + 1) = Sheets(MyArray(i + 1))

は

Set ws(i + 1) = Sheets(MyArray(i))

でしたね。

ところで、

＞１つのブックの中に約40ぐらいシートがあり、そのうちの約30ぐらいのシート

この【約３０シート】とは連続したシートなのでしょうか？
仮に個人データ管理と捉えるなら後ろの方にあると考えた時、例えばシートインデックス5~34位？とも思えます。

だとしたら簡易検証ですが、

Sub try() '検証でシート数は１０個用意
Dim ws() As Worksheet
Dim i As Integer

ReDim ws(1 To 1)

For i = 6 To Worksheets.Count '6はデータシートの開始インデックス番号

ReDim Preserve ws(1 To i - 5) '5は除外する最終インデックス番号

Set ws(i - 5) = Worksheets(i)

Next


For i = 1 To UBound(ws)

Debug.Print ws(i).Name

Next

End Sub

結果：
Sheet6
Sheet7
Sheet8
Sheet9
Sheet10

とインデックス番号を用いてシートを指定するのが楽かもです。

若しくは途中をくり抜くならインデックス番号をSelect CaseやIf文で分岐させると言う方法も取れそうです。

あとは、

＞最終行と最終列

がどの程度の範囲なのか（開始セルはどこ？判断する基準行列番号は？）、データがないように見えて実は数式が埋まっているとか？

でしょうか。

しかし実際に必要なのはシートの配列なのか、シート毎のセル範囲情報なのかによっては、連想配列とか上記に構造体を組み合わせと言うのもあるかと。
配列の配列でも良いかもですが。

この回答へのお礼

忙しい中、回答ありがとうございます。

＞この【約３０シート】とは連続したシートなのでしょうか？
何回もシートを削除したりしているので連続したシートではないのです。

VBEの画面でシートのインデックス番号(オブジェクト名)を書き換えることはできるようなのですが、書き換えても問題はないのでしょうか？もし書き換え可能なら、それが一番簡単な方法かもしれません。

＞しかし実際に必要なのはシートの配列なのか、シート毎のセル範囲情報なのかによっては、連想配列とか上記に構造体を組み合わせと言うのもあるかと。
質問がわかりにくくてすみません。今回はシートの配列です。

ただ別の質問でシートごとの特定の範囲を格納した部分を配列のように扱うことができるのかという質問をしています。もし可能でしたら、そちらの質問にも回答していただけると助かります。よろしくお願いいたします。

お礼日時：2023/06/05 09:16

No.2 回答者： tatsumaru77 回答日時： 2023/06/04 20:11

以下のようにしてください。

各シートのA1セルにシート名を設定しています。

Public Sub シート試験()
Dim ws() As Worksheet
Dim arr As Variant
Dim i As Long
arr = Array("田中", "本田", "南", "上田")
ReDim ws(UBound(arr))
For i = 0 To UBound(arr)
Set ws(i) = Worksheets(arr(i))
Next
'各シートのA1セルにシート名を設定する
For i = 0 To UBound(ws)
ws(i).Cells(1, 1).Value = ws(i).Name
Next
End Sub

この回答へのお礼

忙しい中、回答ありがとうございます。

UBound(arr)というような使い方ができるんですね。勉強になりました。

お礼日時：2023/06/04 22:49