ひな形から作る詳細シートが増減するブックでは、一覧の行を作る処理と、各シートの合計を反映する処理を分けると保守しやすくなります。Excel VBAは一覧の構成を作り、金額の変更はExcelの数式で追従させます。
合計セルにシート単位の名前を付ける
詳細シートの合計セルにDetailTotalのような名前付き範囲を設定します。行挿入で合計セルが動いても、名前の参照先が追従するため、一覧側のセル番地を直す作業を減らせます。
ひな形を複製する場合は、名前のスコープがブック全体ではなく各シートになっていることを確認します。同名のローカル名とブック名が混在するブックでは、どちらを参照しているかも点検します。
値ではなく参照式を書き込む
Dim DetailSheet As Worksheet
Dim ReferenceFormula As String
Set DetailSheet = ThisWorkbook.Worksheets("Detail A")
ReferenceFormula = "='" & Replace(DetailSheet.Name, "'", "''") & "'!DetailTotal"
ThisWorkbook.Worksheets("Summary").Range("B2").Formula = ReferenceFormula
シート名は単一引用符で囲み、名前自体に含まれる単一引用符は二重にします。合計値をコピーする場合と違い、詳細シートの入力を直すと、一覧の数式も再計算されます。計算方法が手動の場合は、更新タイミングを別途管理します。
複数行は二次元配列へ式を組み立て、対象範囲へまとめて書けます。FormulaはA1形式、FormulaR1C1はR1C1形式です。行合計を=SUM(RC2:RC4)で作るなら後者を使い、式の形式を揃えます。項目名など外部入力の文字列は、数式として評価されないよう別列の文字列として扱います。
一覧自体の参照範囲も更新する
別シートが一覧を参照する場合は、一覧の現在行数に合わせて名前の範囲も更新します。表、項目列、金額列にそれぞれ名前を付けると、SUMIFSや検索式が行数の変化に追従できます。
=IFERROR(VLOOKUP(A2,SummaryTable,COLUMNS(SummaryTable),FALSE),0)
この式は検索列が表の左端、返したい列が右端にある前提です。項目名が重複する場合は先頭だけを返すため、分類との複合キーや重複検査を用意します。配布先が古いExcelなら、利用できる関数を確認してVLOOKUPやINDEX/MATCHを選びます。
作り直すタイミングと書式の扱いを決める
詳細シートを作成・削除した直後に一覧を作り直すと、シートを開くたびに処理するより更新箇所が明確です。手作業でシートを変更する運用なら、一覧の更新ボタンも用意します。
件数が減った場合は以前の出力を残さず、出力担当範囲だけClearContentsで消します。利用者が整えた罫線や列幅まで消さないようにします。合計名がないシートは黙って0と扱わず、不足している名前を知らせます。
確認するケース
詳細の金額変更、行挿入、詳細シートの追加・削除、引用符を含むシート名、一覧の0件・減少を確認します。数式が正しいだけでなく、名前のスコープと実際の参照先を名前の管理画面でも確認すると、参照違いを見つけやすくなります。