一覧作成マクロ用の入力テンプレートに : 非表示シートをコピーして使う

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 oWS = Nothing
End Function
条件付き書式をテンプレートに仕込んでおき、要注意箇所などを検出できるようにしておくと、出力した一覧の可用性がぐっと高まります。
実際にシート化しておくと、仕様変更にも対応しやすくなります。

シートのコピーのポイント

新規作成されたシートはオブジェクト変数に直接代入できますが、コピーで作成されたシートは直接代入できないという点に注意してください。

'OK : 新規作成では代入可能
Set oWS = Worksheets.Add
'NG : コピーは実行されるが、代入不可でエラーになる
Set oWS = ActiveSheet.Copy

このため通常は、コピーシートは直後に必ずアクティブになることを利用してActiveSheetを変数に代入します。

上記の場合、非表示シートのコピーではアクティブにならないので、コピーシートの命名規則を利用して対象を特定しています。

タイトルとURLをコピーしました