WorksheetのCopyメソッドを改良した関数を作る

VBAをやっているとかなりの頻度で使用するWorksheet.Copyですが、
正直なところ仕様がイマイチで使いづらいです。

なんといっても返り値がWorksheet型になっていないため、

Set ws出力シート = Worksheet("○○").Copy

という記述を行うことができず、

Worksheet("○○").Copy
Set ws出力シート = ActiveSheet

というアクティブシートに頼った冗長なコードが必要になります。

その他の改善したい点として、

• 非表示シートをコピーすることができない
• After:=ブックの最後尾 という頻出の処理を書くのが面倒
  （wb.Worksheets(wb.Worksheets.Count)という記述が冗長）

あたりも挙げられますね。

ということで、この辺りを改善した汎用関数を作ってしまいましょう。

• 返り値がWorksheetのFunction
• BeforeにWorkbookを渡せる（先頭にコピー）
• AfterにWorkbookを渡せる（末尾にコピー）
• 非表示シートを渡すと表示してからコピーして非表示に戻す
• コピーシートの保護を解除する（設定でOFFも可）

この機能を持たせた汎用関数を作成します。

気に入ったら皆さんのライブラリに仲間入りさせてあげてください。

ソースコード

' シートのコピー改良関数
Function シートをコピーする(wsコピー元 As Worksheet _
    , Optional ByVal Before As Object, Optional ByVal After As Object _
    , Optional isシートの保護を解除する As Boolean = True) As Worksheet
    
    ' コピー元シートが非表示であれば状態を記憶してから再表示
    Dim コピー元シートVisible As Long
    コピー元シートVisible = wsコピー元.Visible
    
    If コピー元シートVisible <> xlSheetVisible Then
        wsコピー元.Visible = True
    End If
    
    ' BeforeにWorkbookを渡した場合はそのブックの先頭にコピー
    If TypeName(Before) = "Workbook" Then
        Set Before = Before.Worksheets(1)
    End If
    
    ' AfterにWorkbookを渡した場合はそのブックの末尾へコピー
    If TypeName(After) = "Workbook" Then
        Set After = After.Worksheets(After.Worksheets.Count)
    End If
    
    ' Copyメソッドを実行
    If Not Before Is Nothing Then
        wsコピー元.Copy Before:=Before
    ElseIf Not After Is Nothing Then
        wsコピー元.Copy After:=After
    Else
        wsコピー元.Copy
    End If
    
    ' 返り値のセット
    Set シートをコピーする = ActiveSheet
    
    ' 保護の解除
    If isシートの保護を解除する Then シートをコピーする.Unprotect
    
    ' 元シートの表示設定を復元
    If コピー元シートVisible <> xlSheetVisible Then
        wsコピー元.Visible = コピー元シートVisible
    End If
    
End Function

実行例

単純にシートを新規ブックにコピーするコードがこちら。

Set ws出力シート = シートをコピーする(ThisWorkbook.Worksheets("○○"))

　
この関数が最も真価を発揮する
「非表示でロックされたシートを対象ブックの最後尾にコピー」するコードがこちら。

Set ws出力シート = シートをコピーする(ThisWorkbook.Worksheets("○○"), After:=wb対象ブック)

　
コピー元が非表示でも全くコードが変わらないのもとても便利ですし、
「AfterにWorkbookを渡すだけで末尾にコピーしてくれる」のが、
この処理をよくやる自分としてはものすごくありがたい仕様です。

なかなか便利な関数だと思いますので、
気に入ったらライブラリの仲間に入れてあげてください。