VBA 非表示シートの取り扱い

質問

Worksheets(1)を使う時、左から一番目のシートが非表示の場合、シートの指定を誤る可能性があります。非表示シートを無視して表示シートの左から1番目を指定することは可能でしょうか？

VBAなので非表示シートを調べる事はかのうですが、ファイル数が多い上に順番が同じとは限らないので、出来れば避けたいです。また、表示シートの名前もバラバラなので、番号指定を希望します。

回答No.4

mar00 · ベストアンサー

No.2です。
>Controls(1)の数字を変えると番号指定可能なのでしょうか？
Sub Macro1()
Dim Ws As String, stNo As Byte
Ws = Application.CommandBars("Workbook tabs").Controls(2).Caption
stNo = Sheets(Ws).Index
End Sub

のようにすればいいと思います。
stNoに表示されているシートの２番目のシート番号が返ります。

お礼

ありがとうございます。
実質１行ですね。

回答No.3

eden3616

配列myStに表示されているシートの左から指定番号のシート名を格納します。
St_noに取得したい番号（表示されている左からn番目のシート番号）を指定してください。

最下のコードでは1,2,4番目の表示されているシートを取得してmyStに格納します。
Sheet1～Sheet10のうち、奇数シートが非表示設定の場合、以下が格納されます。
　　mySt(0) = Sheet2
　　mySt(1) = Sheet4
　　mySt(2) = Sheet8

Sheets(mySt).Selectなどでシート選択できるように文字列配列に格納していますが、
取り扱いやすいようにオブジェクト変数として扱うには以下を変更してください。
①　Dim mySt() As String
　　　　　　↓
　　　Dim mySt() As Worksheet
②　mySt(nxSt) = Worksheets(i).Name
　　　　　　↓
　　　Set mySt(nxSt) = Worksheets(i)

上記例で、非表示5シート、表示5シートであるにもかかわらずSt_noに
1,2,4,6を指定した場合の6は無視されて1,2,4番目の表示シートが格納されます。

■VBAコード

Sub sample()
Dim mySt() As String, St_no() As Variant
Dim i As Integer, cnt As Integer, nxSt As Integer

'配列へ格納するシートの番号を指定
St_no = Array(1, 2, 4)

For i = 1 To Worksheets.Count
　　If Worksheets(i).Visible Then
　　　　cnt = cnt + 1
　　　　If St_no(nxSt) = cnt Then
　　　　　　ReDim Preserve mySt(nxSt + 1)
　　　　　　mySt(nxSt) = Worksheets(i).Name
　　　　　　nxSt = nxSt + 1
　　　　End If
　　End If
　　If UBound(St_no) < nxSt Then Exit For
Next i
ReDim Preserve mySt(nxSt - 1)

'格納された配列を表示
For i = 0 To UBound(mySt)
　　Debug.Print mySt(i)
Next i
End Sub

お礼

ありがとうございます。

回答No.2

mar00

番号指定ではないですが
Sub Macro1()
Ws = Application.CommandBars("Workbook tabs").Controls(1).Caption
Sheets(Ws).Select
End Sub
でも可能だと思います。

お礼

ありがとうございます。
Controls(1)の数字を変えると番号指定可能なのでしょうか？
検索してもなかなか見つかりません。

回答No.1

tom04

こんばんは！

Sub Sample1()
Dim k As Long, wS As Worksheet
For k = 1 To Worksheets.Count
If Worksheets(k).Visible = True Then
Set wS = Worksheets(k)
Exit For
End If
Next k
End Sub

※　これで k　番目のSheetが　wS　という変数に格納されます。

こんな感じをご希望だったのでしょうか？m(_ _)m

お礼

ありがとうございます。
欲しいワークシート名は複数だったので、wSを配列にして取り込むことにします。