シート一覧の取得と絞り込み、並び替えができると便利
以前知り合いにExcelVBAで動作するツールを作って渡した際に、使用するシートの数が多く、シート一覧の情報を簡単に取得して並べ替えたり、絞り込んだりしたいと要望をもらったことがあります。
シートの絞り込みや並べ替えについてはExcel標準の機能では存在しないはずなので、きちんとした形で用意できれば色々便利に使えると思って作成してみました。
シート一覧の取得と絞り込み、並べ替えで想定している機能について
今回用意するExcelマクロファイルに搭載予定の機能は下記の4つです。
今回はこのうち、①シート名一覧を取得する機能、②ハイパーリンクでシートを移動する機能の2つを行うExcelVBAコードを紹介します。
スポンサーリンク
今回用意したExcelのワークシートについて
今回私が作成したマクロファイルのシート一覧取得、絞り込み、並べ替え用のシートは下記のようなものを想定しています。
「シートの一覧取得」ボタンをクリックするとA4セルから下に向かってシート名の一覧が表示されます。
各シート名のセルにはそのシートへのリンクが貼られており、すぐに目的のシートを表示することが可能です。
その他表示するシートの絞り込み、シートの並べ替えについてはそれぞれリンク先をご参照ください。
Excelのシート名一覧を取得し、ハイパーリンクを設定するExcelVBAコード
シート名一覧を取得して、ハイパーリンクを設定するExcelVBAコードは下記の通りです。
Dim i As Long, x As Long, y As Long
’シート名一覧の開始位置をxとする
For i = 1 To Cells(1000000, 1).End(xlUp).Row
If Cells(i, 1) = “シート名” Then
x = i
Exit For
End If
Next i
’シート名一覧の開始位置が取得できなければ終了
If x = 0 Then Exit Sub
’すでにオートフィルターが設定されていれば解除しておく
If ActiveSheet.AutoFilterMode = True Then
Cells(x, 1).AutoFilter
End If
’抽出用シートのシート番号をyとする
For i = 1 To Worksheets.Count
If Sheets(i).Name = ActiveSheet.Name Then
y = i
Exit For
End If
Next i
’検索用シートの次のシートからを抽出対象としてハイパーリンクを設定する
For i = y + 1 To Worksheets.Count
With ActiveSheet.Hyperlinks
’対象となるセルの行は「i – y + x 」となる
’この行をVBEにコピーする際はアポストロフィが全角になるので修正してください。
.Add Anchor:=Cells(i － y + x, 1), Address:=””, SubAddress:=”’” & Sheets(i).Name & “’!A1”, TextToDisplay:=Sheets(i).Name
End With
Next
End Sub
シート名の一覧表を作る開始位置や一覧表取得用のシートの位置を可変にするために少しサンプルコードが複雑ですが、固定にして変数「ｘ」「ｙ」を数字にしてしまうともっと楽にコードが書けると思います。
「Cells(i – y + x, 1)」の意味を解説すると下記の通りです。
シート番号は「y + 1」から開始、セルの行は「x + 1」から開始されるため、シートのインデックス番号が「i」の時、セルの行は「i – (y + 1) + (x + 1)」となり、()を外して
「i – y – x」で表すことができます。
この辺りは算数とか数学の考え方です。
久しぶりすぎて私も理解するのに少し時間がかかります。
また移動先の各シートから元の一覧表取得用のシートに戻るためのコードを用意しておいた方がより便利だと思います。
ショートカットまたはボタンなどで下記のExcelVBAを実行できるようにしておくとより使いやすくなります。
Dim x As Long
For i = 1 To Worksheets.Count
If InStr(Sheets(i).Name, “検索”) <> 0 Then
x = i
Exit For
End If
Next i
Sheets(x).Select
End Sub
こちらは説明不要かもしれませんが、シートを左から順に確認し、「検索」という文字がシート名に含まれている最初のシートに移動するExcelVBAコードになります。
コメント