ブック内の最終(右端)シートを取得する

あるブックの、最後のシートを取得する方法を解説します。

指定ブックの最終シートを一発で取得するWorkbooks("○○").LastSheetみたいなプロパティはありませんので、以下のコードで地道に取得することになります。

Dim 指定ブック As Workbook
Set 指定ブック = Workbooks("○○.xlsx")

Dim 最終シート As Worksheet
Set 最終シート = 指定ブック.Worksheets(指定ブック.Worksheets.Count)

理屈は簡単で、「ブックの中で、ワークシートの数番目のシート」が、末尾のシートとなります。

例えば「ブックの末尾にシートを追加する」場合は、

指定ブック.Worksheets.Add After:= 指定ブック.Worksheets(指定ブック.Worksheets.Count)

と書きます。

ActiveWorkbookの末尾にシートを追加する場合は、

Worksheets.Add After:= Worksheets(Worksheets.Count)

と、短い記載でいけます。

ただし、この手の「シートやブックを操作する」マクロは往々にしてアクティブブックがコロコロ変わりますので、なるべくブックはきっちり指定することをおすすめします。

同様に「他のブックの末尾へシートをコピーする」場合は、

ThisWorkbook.Worksheets("○○").Copy _
    After:= コピー先ブック.Worksheets(コピー先ブック.Worksheets.Count)

と記載します。

長くなりがちなコードですが、このように「_」を使って改行すると、多少は見やすくなりますね。

汎用関数にする

この「最終シートの取得」は、理屈は簡単なのですが、
コードが長ったらしくて面倒なんですよね。

ブックの指定を変数ではなくWorkbooks("△△△△.xlsx")という記載で行うと、

Set 最終シート = Workbooks("△△△△.xlsx").Worksheets(Workbooks("△△△△.xlsx").Worksheets.Count)

みたいになって腹が立つのですが、このためだけに「指定ブック」みたいな変数を用意するのも、それはそれで面倒です。

このように、同じようなコードが呪文みたいに続く冗長なイディオムは、
汎用関数にすると、とてもスッキリします。

Function Get最終シート(指定ブック As Workbook) As Worksheet
    Set Get最終シート = 指定ブック.Worksheets(指定ブック.Worksheets.Count)
End Function


Set 最終シート = Workbooks("△△△△.xlsx").Worksheets(Workbooks("△△△△.xlsx").Worksheets.Count)
' ↓関数を使って書き換え
Set 最終シート = Get最終シート(Workbooks("△△△△.xlsx"))


' 挿入やコピーもだいぶ楽に
Workbooks("△△△△.xlsx").Worksheets.Add _
    After:= Get最終シート(Workbooks("△△△△.xlsx"))

ThisWorkbook.Worksheets("○○").Copy _
    After:= Get最終シート(Workbooks("△△△△.xlsx"))

関数の中身は超単純で、さっきのイディオムを書いているだけですが、
実際のコードがかなり読みやすくなっているのが分かると思います。

関数というと難しく感じるかもしれませんが、こういった単純な関数ならすごく簡単に作れます。

関数づくりの練習にもなりますので、こういった冗長なコードを見つけたら、関数化にチャレンジしてみましょう。

↑の楽になった挿入とコピーのコードを見ると、コピーはいいですが挿入はまだしつこさが残っているので、

Function 末尾にシートを追加する(指定ブック As Workbook) As Worksheet
    Set 末尾にシートを追加する _
        = 指定ブック.Worksheets.Add(After:= Get最終シート(指定ブック))
End Function

' 使用例
Dim 作成シート As Worksheet
Set 作成シート = 末尾にシートを追加する(Workbooks("△△△△.xlsx"))

こんな関数を一緒に作ってもいいですね。

おまけ：Withステートメントで短縮する

同じ記述が繰り返される冗長な部分を省略する機能としては、
Withステートメントも便利です。
　

Workbooks("△△△△.xlsx").Worksheets(Workbooks("△△△△.xlsx").Worksheets.Count)

このコードには「Workbooks("△△△△.xlsx").Worksheets」が2回出てくるので、
これをWithステートメントでくくりましょう。

と思いきや、一個目の「Workbooks("△△△△.xlsx").Worksheets」は、
直後のコードが「.」ではなく「(」です。

これでは「.」に短縮するWithは使えないように思えます。

しかし、このコードはWithステートメントを使用して、
以下のように書くことができます。

With Workbooks("△△△△.xlsx").Worksheets
    Set 最終シート = .Item(.Count)
End With

Worksheets()は、Worksheets.Item()の省略表現です。
(厳密にはちょっと違うけど)

普段は書く必要はありませんが、Withを使うために「.」が欲しいというときには、
.Itemと省略せずに書くことで対応できます。

まあめったに使わないですし、今回の最終シート取得でも↑の汎用関数を作った今となっては不要ですけどね。

豆知識程度に覚えておくと、いつか何かの役に立つかもしれません。