Excel VBA 逆引き集 | シートコピー時の重複名回避

Excel VBA
スポンサーリンク

シートコピー時の重複名回避

コピー後の命名で「同名エラー」を防ぐための安全テンプレートをまとめました。禁止文字・31文字上限・連番付与・警告非表示まで、初心者向けにコピペで使える形で解説します。

基本：コピーして安全に命名（重複なら連番）

Sub CopyTemplate_SafeRename()
    Dim src As Worksheet, dst As Worksheet, baseName As String, safeName As String
    Set src = ThisWorkbook.Worksheets("Template") 'コピー元
    baseName = "レポート"                           '希望の名前

    '末尾にコピー
    src.Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
    Set dst = ActiveSheet                          'コピー直後は新シートがアクティブ

    '安全名へ整形＋重複回避
    safeName = UniqueSheetName(baseName, ThisWorkbook)

    '命名（警告を抑止して確実に実行）
    Dim wasAlerts As Boolean: wasAlerts = Application.DisplayAlerts
    Application.DisplayAlerts = False
    dst.Name = safeName
    Application.DisplayAlerts = wasAlerts

    MsgBox "コピー完了: " & dst.Name
End Sub
VB
  • ポイント:
    • ActiveSheet: Copy直後の新シートはアクティブ。そこで命名するのが最短。
    • DisplayAlerts: 命名時のダイアログを抑止。処理後は元に戻す。
    • UniqueSheetName: 禁止文字・長さ・重複に強い安全命名関数を使う。

安全命名テンプレート：禁止文字・31文字上限・連番付与

Function SanitizeSheetName(rawName As String) As String
    Dim s As String: s = Trim$(rawName)
    s = Replace(s, ":", "_")
    s = Replace(s, "/", "_")
    s = Replace(s, "\", "_")
    s = Replace(s, "?", "_")
    s = Replace(s, "*", "_")
    s = Replace(s, "[", "_")
    s = Replace(s, "]", "_")
    If Len(s) = 0 Then s = "Sheet"
    If Len(s) > 31 Then s = Left$(s, 31)
    SanitizeSheetName = s
End Function

Function SheetExists(name As String, wb As Workbook) As Boolean
    Dim t As Worksheet
    On Error Resume Next
    Set t = wb.Worksheets(name)
    SheetExists = Not t Is Nothing
    On Error GoTo 0
End Function

Function UniqueSheetName(baseName As String, wb As Workbook) As String
    Dim name As String: name = SanitizeSheetName(baseName)
    Dim i As Long: i = 1
    Do While SheetExists(name, wb)
        i = i + 1
        name = SanitizeSheetName(baseName & " (" & CStr(i) & ")") '「(2)」形式
    Loop
    UniqueSheetName = name
End Function
VB
  • ポイント:
    • 禁止文字対応: : / \ ? * [ ]は使えないため_に置換。
    • 31文字制限: 超過は切り詰め。空文字は「Sheet」に。
    • 連番形式: Excelの慣例「(2)」スタイルで読みやすく。

他ブックへコピーする場合（既存ブック末尾へ）

Sub CopyToOtherWorkbook_Safe()
    Dim src As Worksheet, dstWb As Workbook, newName As String
    Set src = ThisWorkbook.Worksheets("Template")
    Set dstWb = Workbooks("月次集計.xlsm") '既に開いているブック

    '相手ブックの末尾にコピー
    src.Copy After:=dstWb.Worksheets(dstWb.Worksheets.Count)

    '新しくできたシートは「相手ブック側で」アクティブになる
    newName = UniqueSheetName("月次レポート", dstWb)

    Dim wasAlerts As Boolean: wasAlerts = Application.DisplayAlerts
    Application.DisplayAlerts = False
    dstWb.ActiveSheet.Name = newName
    Application.DisplayAlerts = wasAlerts

    MsgBox "他ブックへコピー完了: " & newName
End Sub
VB
  • ポイント:
    • 対象の明確化: コピー先のブックでActiveSheetが新シートになる。命名はそのブック側で行う。
    • UniqueSheetName: 連番付与は「コピー先のブック」を基準に判定。

連続コピーの重複回避（まとめて量産）

Sub BulkCopyTemplate_Safe()
    Dim i As Long, nm As String
    For i = 1 To 5
        nm = "週報_" & Format(i, "00")
        Call CopyTemplateWithName(nm)
    Next
    MsgBox "週報を5枚作成しました。"
End Sub

Sub CopyTemplateWithName(desiredName As String)
    Dim src As Worksheet, dst As Worksheet, safe As String
    Set src = ThisWorkbook.Worksheets("Template")

    src.Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
    Set dst = ActiveSheet

    safe = UniqueSheetName(desiredName, ThisWorkbook)

    Dim wasAlerts As Boolean: wasAlerts = Application.DisplayAlerts
    Application.DisplayAlerts = False
    dst.Name = safe
    Application.DisplayAlerts = wasAlerts
End Sub
VB
  • ポイント:
    • 使い回し: 1枚コピー＋命名をサブルーチン化して量産。
    • 重複対策: 同名があっても「週報_01 (2)」のように自動で逃がす。

On Error を使った最短回避（簡易版）

Sub CopyWithSimpleFallback()
    Worksheets("Template").Copy After:=Worksheets(Worksheets.Count)
    On Error Resume Next
    ActiveSheet.Name = "レポート"
    If ActiveSheet.Name <> "レポート" Then
        ActiveSheet.Name = "レポート (2)"
    End If
    On Error GoTo 0
End Sub
VB
  • ポイント:
    • 手軽: まず希望名で命名、失敗したら「(2)」で再試行するだけの簡易策。
    • 限界: 既に「(2)」まである場合などは足りないため、実務はUnique関数がおすすめ。

例題で練習

例題1：テンプレートを「年月レポート」でコピーし、重複なら連番

Sub Example_MonthlyReport()
    Dim nm As String
    nm = Format(Date, "yyyy-mm") & "_レポート"
    CopyTemplateWithName nm
End Sub
VB
  • ポイント:
    • 安定命名: 年月を含める規則＋重複回避で運用が楽。

例題2：一覧（Control!A2:A）に書いた名前で順次コピー・命名

Sub Example_CopyFromList()
    Dim ctrl As Worksheet, last As Long, r As Long, nm As String
    Set ctrl = ThisWorkbook.Worksheets("Control")
    last = ctrl.Cells(ctrl.Rows.Count, "A").End(xlUp).Row

    For r = 2 To last
        nm = CStr(ctrl.Cells(r, "A").Value)
        CopyTemplateWithName nm
    Next
End Sub
VB
  • ポイント:
    • 運用連動: 管理用シートからの一括作成に相性が良い。

例題3：他ブックへ「売上」「在庫」「顧客」を安全コピー

Sub Example_CopyToOtherBook()
    Dim dstWb As Workbook, arr As Variant, i As Long, nm As String
    Set dstWb = Workbooks("集計.xlsm")
    arr = Array("売上", "在庫", "顧客")
    For i = LBound(arr) To UBound(arr)
        Worksheets("Template").Copy After:=dstWb.Worksheets(dstWb.Worksheets.Count)
        nm = UniqueSheetName(CStr(arr(i)), dstWb)
        Dim wasAlerts As Boolean: wasAlerts = Application.DisplayAlerts
        Application.DisplayAlerts = False
        dstWb.ActiveSheet.Name = nm
        Application.DisplayAlerts = wasAlerts
    Next
    MsgBox "他ブックへ安全コピーしました。"
End Sub
VB
  • ポイント:
    • 先方基準: 重複判定は「コピー先ブック」単位で行う。

実務の落とし穴と対策

  • 同名エラー:
    • 対策: 命名前に必ずUnique生成。簡易なら「希望名→(2)」再試行でも最低限回避。
  • 禁止文字・長さ上限:
    • 対策: Sanitizeで置換＋31文字切り詰め。入力由来の名前は必ず整形。
  • DisplayAlertsの戻し忘れ:
    • 対策: 現設定を退避して必ず復帰。例外時でも戻す構造が安全。
  • コピー先のブック混同:
    • 対策: 他ブックへコピー時は「どのブックのActiveSheetか」を意識して命名。
  • VeryHidden/Hiddenのコピー:
    • 注意: 非表示でもコピーは可能。表示操作が絡むなら可視化が必要な場面あり。
スポンサーリンク
タイトルとURLをコピーしました