Excel VBA 逆引き集 | 値と書式を同時コピー

Excel VBA
スポンサーリンク

値と書式を同時コピー

「値だけ」「書式だけ」ではなく、“値と書式をまとめて”移したい場面に使える定番コードを、初心者向けにわかりやすく整理します。基本は PasteSpecial の種類を正しく選ぶか、値貼り＋書式貼りを組み合わせます。

基本：同じサイズの範囲へ値＋数値書式を一度に貼り付け

Sub CopyValuesAndFormats_Basic()
    Dim src As Range, dst As Range
    Set src = Range("B3:E12")
    Set dst = Range("H3").Resize(src.Rows.Count, src.Columns.Count)

    src.Copy
    dst.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    Application.CutCopyMode = False
End Sub
VB
  • ポイント:
    • 値＋数値書式: xlPasteValuesAndNumberFormats は「値と数値の表示形式」を一度に貼ります。色や太字などの書式も含めたい場合は下の組み合わせを使います。
    • サイズ一致: 貼り付け先は src と同じ行数・列数に Resize します。

値＋すべての書式を移す（2回貼り付けで確実）

Sub CopyValuesAndAllFormats()
    Dim src As Range, dst As Range
    Set src = Range("B3:E12")
    Set dst = Range("H3").Resize(src.Rows.Count, src.Columns.Count)

    src.Copy
    dst.PasteSpecial Paste:=xlPasteValues          '値
    dst.PasteSpecial Paste:=xlPasteFormats         '書式（色、太字、罫線、表示形式など）
    Application.CutCopyMode = False
End Sub
VB
  • ポイント:
    • 分割貼り付け: 値→書式の順に2回貼ると、見た目（塗り、罫線、フォント、表示形式）も含めてほぼ完全に移せます。
    • 罫線だけ除外したい: xlPasteAllExceptBorders を使うと罫線以外を一括で移せます。

列幅も含めて“見た目そのまま”にする

Sub CopyValuesFormatsAndColumnWidths()
    Dim src As Range, dst As Range
    Set src = Range("B3:E12")
    Set dst = Range("H3").Resize(src.Rows.Count, src.Columns.Count)

    src.Copy
    dst.PasteSpecial Paste:=xlPasteValues
    dst.PasteSpecial Paste:=xlPasteFormats
    dst.PasteSpecial Paste:=xlPasteColumnWidths    '列幅も合わせる
    Application.CutCopyMode = False
End Sub
VB
  • ポイント:
    • 列幅同期: 表の見た目を揃えたい提出用などに便利。
    • 行の高さは別途設定: 行高さは自動では移らないので、必要なら手動で調整します。

クリップボードを使わない軽量版（値は直接、書式だけPaste）

Sub CopyValuesDirect_FormatsPaste()
    Dim src As Range, dst As Range
    Set src = Range("B3:E12")
    Set dst = Range("H3").Resize(src.Rows.Count, src.Columns.Count)

    '値は直接代入（高速・安全）
    dst.Value = src.Value

    '書式だけPasteSpecial
    src.Copy
    dst.PasteSpecial Paste:=xlPasteFormats
    Application.CutCopyMode = False
End Sub
VB
  • ポイント:
    • 高速化: 値は .Value で直接コピーすると速く、余計な再計算やペーストの不安定さを避けられます。
    • 安定: 書式だけ PasteSpecial で重ねれば、見た目も揃います。

条件付き書式をマージして持っていく

Sub CopyWithConditionalFormatsMerge()
    Dim src As Range, dst As Range
    Set src = Range("B3:E12")
    Set dst = Range("H3").Resize(src.Rows.Count, src.Columns.Count)

    src.Copy
    dst.PasteSpecial Paste:=xlPasteValues
    dst.PasteSpecial Paste:=xlPasteAllMergingConditionalFormats '条件付き書式をマージ
    Application.CutCopyMode = False
End Sub
VB
  • ポイント:
    • マージ: 既存の条件付き書式に重ねる形で持って行きたいときに便利。
    • 完全置換したい: 条件付き書式を一旦クリアしてから貼る手順に変更。

シートやブックをまたいでコピー

Sub CopyAcrossSheetsAndWorkbooks()
    Dim src As Range, dst As Range
    Set src = ThisWorkbook.Worksheets("元").Range("B3:E12")
    Set dst = Workbooks("提出.xlsm").Worksheets("先").Range("H3"). _
              Resize(src.Rows.Count, src.Columns.Count)

    src.Copy
    dst.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    dst.PasteSpecial Paste:=xlPasteFormats          '必要に応じて追加
    dst.PasteSpecial Paste:=xlPasteColumnWidths     '列幅も
    Application.CutCopyMode = False
End Sub
VB
  • ポイント:
    • 対象を明示: Workbooks/Worksheets を必ず明示して取り違えを防止。
    • 先ブックは開いている前提: 未オープンなら開いてから実行します。

選択範囲を“そのまま見た目”で値化コピー（作業グループ対応）

Sub CopySelectionValuesFormats()
    If TypeName(Selection) = "Range" Then
        Dim src As Range, dst As Range
        Set src = Selection
        Set dst = Application.InputBox("貼り付け先の左上セルを指定", Type:=8)
        If dst Is Nothing Then Exit Sub

        Set dst = dst.Resize(src.Rows.Count, src.Columns.Count)
        src.Copy
        dst.PasteSpecial Paste:=xlPasteValues
        dst.PasteSpecial Paste:=xlPasteFormats
        dst.PasteSpecial Paste:=xlPasteColumnWidths
        Application.CutCopyMode = False
    End If
End Sub
VB
  • ポイント:
    • 対話的: 左上セルを指定するだけで選択範囲を“見た目ごと”値にコピー。
    • 安全: サイズを一致させて事故を防ぎます。

例題で練習

例題1：売上表を“値＋見た目”で提出用シートへコピー

Sub Example_CopyTableForSubmission()
    Dim src As Range, dst As Range
    Set src = ThisWorkbook.Worksheets("集計").Range("B2:E20")
    Set dst = ThisWorkbook.Worksheets("提出").Range("B2").Resize(src.Rows.Count, src.Columns.Count)

    src.Copy
    dst.PasteSpecial Paste:=xlPasteValues
    dst.PasteSpecial Paste:=xlPasteFormats
    dst.PasteSpecial Paste:=xlPasteColumnWidths
    Application.CutCopyMode = False
End Sub
VB

例題2：値＋数値書式だけで軽量コピー（見た目最低限）

Sub Example_LightCopyValuesAndNumberFormats()
    Dim src As Range, dst As Range
    Set src = Range("C3:F200")
    Set dst = Range("J3").Resize(src.Rows.Count, src.Columns.Count)

    src.Copy
    dst.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    Application.CutCopyMode = False
End Sub
VB

例題3：値は直接、書式だけ複数ブロックでまとめ貼り（非連続対応）

Sub Example_CopyFormatsMultipleAreas()
    Dim src As Range, dst As Range, area As Range

    '非連続の書式元を作成（例）
    Set src = Union(Range("B2:E2"), Range("B5:E5"))

    '値は直接（例として同じ形の先頭ブロックへ）
    Range("H2:K2").Value = Range("B2:E2").Value
    Range("H5:K5").Value = Range("B5:E5").Value

    '書式は各エリアでPaste（サイズは一致させる）
    For Each area In src.Areas
        Set dst = Range("H" & area.Row).Resize(area.Rows.Count, area.Columns.Count)
        area.Copy
        dst.PasteSpecial Paste:=xlPasteFormats
    Next
    Application.CutCopyMode = False
End Sub
VB

実務の落とし穴と対策

  • 貼り付け先サイズが違う:
    • 対策: 常に Resize で行数・列数を一致させる。ズレると一部しか貼れない／エラーの原因。
  • 列幅が合わず見た目が崩れる:
    • 対策: xlPasteColumnWidths を追加。行高さは必要に応じて Rows(…).RowHeight を設定。
  • 条件付き書式の上書き／衝突:
    • 対策: マージしたいなら xlPasteAllMergingConditionalFormats。完全置換なら先に ClearFormats。
  • クリップボード状態が残る:
    • 対策: 最後に Application.CutCopyMode = False を必ず実行。
  • 速度が遅い／ちらつく:
    • 対策: 値は直接代入＋書式だけPaste、必要に応じて Application.ScreenUpdating = False を併用。
スポンサーリンク
タイトルとURLをコピーしました