Sheetのcopyで新たなBookを作る場合の新たなBookとSheetを扱う方法について

Question

前提・実現したいこと

Sheetのcopyで新たなBookを作る場合、copyメソッドには戻り値がないため
新たなBookやSheetを取得するための良い手法を知りたいです。

こんな手法があるとか
試したことの例示の内容の認識が間違っているとか
これで十分です等の情報があれば教えてください。
よろしくお願いします。

試したこと

tSheetがコピーする対象のシートだとした場合…

（１）

    tSheet.Copy
    Dim tToBook As Workbook
    Dim tToSheet As Worksheet
    Set tToBook = ActiveWorkbook
    Set tToSheet =tToBook.Sheets.Item(1)

等のようにActive何とかを使ってBookとSheetを取得する方法が
最もシンプルだと思いますが「ActiveWorkbook」とか
対象が不明瞭なコードは如何なものか。と思っています。
※私は大抵…これで実装してますが。

（２）

    Dim tCol As Collection
    Set tCol = New Collection
       
    Dim tToBook As Workbook
    Dim tToSheet As Worksheet
    
    Set tToBook = Workbooks.Add
    For Each tToSheet In tToBook.Sheets
        tCol.Add tToSheet
    Next
    Set tToSheet = tToBook.Sheets.Item(1)
    tSheet.Copy after:=tToSheet
    For Each tToSheet In tCol
        tToSheet.Delete
    Next
    Set tToSheet = tToBook.Sheets.Item(1)
    tToSheet.Name = tSheet.Name

Bookを予め作っておいて、そこにシートをコピーし
余計なのを後で削除するという手順です。
「tToBook.Sheets.Item(1)」という所が不明瞭な感じがしますが
１シートのみ残っている状態であれば確定されるので許容かと思います。
そういう意味では、tToBook.ActiveSheetでもいいと思います。
ただ大層です。こんなのならしない方が良いのではと思ったりします。

（３）

    tSheet.Copy
    Dim tToBook As Workbook
    Dim tToSheet As Worksheet
    Set tToBook = Workbooks(Workbooks.Count)
    Set tToSheet = tToBook.Sheets.Item(1)

最後に追加されたBookだからWorkbooks(Workbooks.Count)で取れる
らしいですが、なんだか不安です。
※似たような実装の仕方をしたこともありますが。

補足情報（FW/ツールのバージョンなど）

Windows10
Excel2016,365

Answer 1

（１）か（３）でいいのではないかな。
下記のページでもおなじことをしてますね。

Worksheet.Copyで新規に作成されたブック名の取得:エクセルマクロ・Excel VBAの使い方/Workbookオブジェクト

Comments

[xail2222]

回答ありがとうございます
やはり、1か3ですか。私は2も良いかなとか思ったりするときもあったのですが迷走しているだけかもしれないですね。
実装もしそうな勢いでしたが、やめることにします。理念次第で、2もありかなと思ったりしたのとネットに書いてるひともいた。と思って今再度見直したら、悪い例？のような感じで書かれてました。
http://officetanaka.net/excel/vba/tips/tips170.htm
まあ人それぞれ理念があるから色々な実装があると思うので2も否定はすべきじゃないと思ったりもするのですが、処理時間はかかるし可読性もないので良くない実装という所までいくでしょうか。

Answer 2

私も同じことで悩んでいましたが、実用上問題ないので(1)の方法を使っています。

ほかに思い当たる方法としては
1.新規シートを作成
2.数式貼り付け
3.書式貼り付け
でいけるかもしれません。狭い範囲のコピーを手作業でやる時はこの方法でよくやります。
これを全セルに適用すればいいと思いますが処理に時間がかかるかもしれません。

Comments

[xail2222]

回答ありがとうございます。
やはり、悩みますよね。同じ事で悩んだ経験のある人がいて安心しました。1で正常動作しなくなるのはデバッグ時に処理を止めてactiveなのを変えた時位でしょうか。なので実用上は問題無いといえますよね

張り付けの例の提示もありがとうございます。クリップボードを使ってと言う事だと思いますが、クリップボードも処理時にパソコン操作次第で正常動作しなくなるので可能なら避けるべきかなと思ったりしますが、どうしても使う場合があるのでその使用量次第で運用制限をかけてもらうようにしています