VBAでよくある処理として、何らかの情報を収集してシートに一覧を書き出すというパターンがあります。
このとき、単にシートを新規作成してリストテキストを書き出すだけでは、無味乾燥な外観で使いにくいものになってしまいます。かといって、外観の各種調整をするマクロを用意するのは結構な手間ですし、場合によっては処理本体より多くのコードが必要になりかねません。
そんな場合によく使う手段が、一覧出力のテンプレートとなるシートを仕込んでおくことです。テンプレートには、列や書式が調整済みのテーブル、必要ならシートの印刷設定などもきちんと整えておきます。シートをそのまま置いておくとユーザーが操作してしまう恐れがあるので、非表示にしておきます。
マクロの実行時には、このシートのコピーを作成して非表示を解除し、そこにデータを入力していく、という使い方をします。
Sub 本処理()
Dim oWS As Worksheet
'複製元の隠しシートの名前を指定して、複製シートを取得する
Set oWS = f_getTemplateSheetCopy(aNAME:="リスト")
'--------------------------
'本処理を実行し、結果をoWSに入力する
'--------------------------
Set oWS = Nothing
End Sub'非表示ワークシートを複製、表示、アクティブ化して、ワークシートオブジェクトを返す関数
Function f_getTemplateSheetCopy(aNAME As String) As Worksheet
Dim aWS As Worksheet
With ActiveWorkbook.Worksheets(aNAME)
If .Visible = xlSheetHidden Then
'非表示のままシートを複製
.Copy After:=Worksheets(Worksheets.Count)
Else
GoTo EXTFUNC
End If
End With
For Each aWS In ActiveWorkbook.Worksheets
If aWS.Visible = xlSheetHidden Then
'複製シートを「コピー元名 (n)」の名前で判定
If InStr(aWS.Name, aNAME & " (") = 1 Then
'複製シートの非表示を解除しアクティブ化
aWS.Visible = xlSheetVisible
aWS.Activate
Exit For
End If
End If
Next aWS
Set f_getTemplateSheetCopy = aWS
EXTFUNC:
Set aWS = Nothing
End Function
シートのコピーのポイント
新規作成されたシートはオブジェクト変数に直接代入できますが、コピーで作成されたシートは直接代入できないという点に注意してください。
'OK : 新規作成では代入可能
Set oWS = Worksheets.Add
'NG : コピーは実行されるが、代入不可でエラーになる
Set oWS = ActiveSheet.Copyこのため通常は、コピーシートは直後に必ずアクティブになることを利用してActiveSheetを変数に代入します。
上記の場合、非表示シートのコピーではアクティブにならないので、コピーシートの命名規則を利用して対象を特定しています。