大体でIT

-ちょっと使えるネタを紹介-

大体でIT

Excel VBAで、形式を選択して貼り付ける方法について、ご紹介します。形式を選択して貼り付けるには、「PasteSpecial」を使います。値だけでなく、書式や列幅も貼り付けることができるので、便利です。具体的なVBAコードを使って、解説していきます。

はじめに

この記事では、形式を選択して貼り付ける方法について、ご紹介します。

形式を選択して貼り付けるには、「PasteSpecial」を使います。

「PasteSpecial」の形式を変更して、「すべて」や「書式のみ」を貼り付けることができます。

値だけでなく、書式や列幅も貼り付けたい場合に、便利です。

では、形式を選択して貼り付ける方法について、解説していきます。

この記事で紹介すること

  • 形式を選択して貼り付ける方法

目次から見たい項目へ移動すると便利ですよ。

目次

VBAで形式を選択して貼り付ける

VBAで形式を選択して、貼り付けるには、「PasteSpecail」を使います。

形式を選択して貼り付けは、Excelでいうと次の画面で設定するものです。

形式を選択して貼り付けの画面

この形式を選択して貼り付けを、VBAでやってみます。

PasteSpecialを使う

形式を選択して貼り付ける、PasteSpecialは、次のように入力して使います。

「セル.PasteSpecial Paste:=形式」

形式には、次のような形式があります。

  • すべて(xlPasteAll)
  • 数式(xlPasteFormulas)
  • 値(xlPasteValues)
  • 書式(xlPasteFormats)
  • コメントとメモ(xlPasteComments)
  • 入力規則(xlPasteValidation)
  • コピー元のテーマを使用してすべて貼り付け(xlPasteAllUsingSourceTheme)
  • 罫線を除くすべて(xlPasteAllExceptBorders)
  • 列幅(xlPasteColumnWidths)
  • 数式と数値の書式(xlPasteFormulasAndNumberFormats)
  • 値と数値の書式(xlPasteValuesAndNumberFormats)
  • すべての結合されている条件付き書式(xlPasteAllMergingConditionalFormats)

また、次のように「Transpose」を使うことで、貼り付ける値を「転置」することができます。

「セル.PasteSpecial Paste:=形式, Transpose:=True」

では、それぞれの形式で貼り付けてみましょう。

VBAでPasteSpecialを使って貼り付け

PasteSpecialのそれぞれの形式で、貼り付けてみます。

すべて

形式を「すべて」で貼り付けてみます。

引数には、「Paste:=xlPasteAll」を使います。

Sub TEST1()
    
    'コピー
    ActiveSheet.Range("A1").CurrentRegion.Copy
    
    'すべて
    ActiveSheet.Range("D1").PasteSpecial Paste:=xlPasteAll
    
End Sub

セルに値を入力しておきました。

セルに値や書式を入力

セルに値を入力

B1には数式を入力しています。

数式を入力

B2には入力規則を設定しています。

入力規則を設定

では、VBAコードを実行してみます。

「すべて」で貼り付け

値や書式が貼り付けられます。

値が貼り付けられる

数式も貼り付けられています。

数式が貼り付けられる

入力規則もそのまま貼り付けられています。

入力規則もそのまま貼り付けられる

形式を「すべて」として貼付けができました。

数式

次は、形式を「数式」で貼り付けてみます。

引数には、「Paste:=xlPasteFormulas」を使います。

Sub TEST2()
    
    'コピー
    ActiveSheet.Range("A1").CurrentRegion.Copy
    
    '数式
    ActiveSheet.Range("D1").PasteSpecial Paste:=xlPasteFormulas
    
End Sub

セルに値を入力しておきました。

セルに数式を入力

数式を入力

B1には数式を入力しています。

では、VBAコードを実行してみます。

「数式」で貼り付け

値と数式が貼り付けられます。

値が貼り付けられる

数式も貼り付けられています。

数式が貼り付けられる

形式を「数式」として貼付けができました。

書式は貼り付けられていません。

形式を「値」で貼り付けてみます。

引数には「Paste:=xlPasteValues」を使います。

Sub TEST3()
    
    'コピー
    ActiveSheet.Range("A1").CurrentRegion.Copy
    
    '値
    ActiveSheet.Range("D1").PasteSpecial Paste:=xlPasteValues
    
End Sub

セルに値を入力しておきました。

セルに数式を入力

セルに値を入力

B1には数式を入力しています。

では、VBAコードを実行してみます。

「値」で貼り付け

値が貼り付けられます。書式は貼り付けられません。

値が貼り付けられる

数式ではなく、値となっています。

数式が貼り付けられない

形式を「値」として貼付けができました。

書式

形式を「書式」で貼り付けてみます。

引数には、「Paste:=xlPasteFormats」を使います。

Sub TEST4()
    
    'コピー
    ActiveSheet.Range("A1").CurrentRegion.Copy
    
    '書式
    ActiveSheet.Range("D1").PasteSpecial Paste:=xlPasteFormats
    
End Sub

セルに値を入力しておきました。

セルに値や書式を入力

セルに値を入力

では、VBAコードを実行してみます。

「書式」で貼り付け

書式のみが貼り付けられます。

値が貼り付けられる

値などは貼り付けられていません。

値などは貼り付けられない

形式を「書式」で貼付けができました。

コメントとメモ

形式を「コメントとメモ」で貼り付けてみます。

引数には、「Paste:=xlPasteComments」を使います。

Sub TEST5()
    
    'コピー
    ActiveSheet.Range("A1").CurrentRegion.Copy
    
    'コメントとメモ
    ActiveSheet.Range("D1").PasteSpecial Paste:=xlPasteComments
    
End Sub

セルに値を入力しておきました。

セルにメモを入力

セルに値を入力

B1にはメモが入力されています。

では、VBAコードを実行してみます。

「コメントとメモ」で貼り付け

メモのみが貼り付けられます。

メモのみが貼り付けられる

形式を「コメントとメモ」として貼付けができました。

入力規則

形式を「入力規則」で貼り付けてみます。

引数には、「Paste:=xlPasteValidation」を使います。

Sub TEST6()
    
    'コピー
    ActiveSheet.Range("A1").CurrentRegion.Copy
    
    '入力規則
    ActiveSheet.Range("D1").PasteSpecial Paste:=xlPasteValidation
    
End Sub

セルに値を入力しておきました。

セルに入力規則を入力

入力規則を設定

B2に入力規則を設定しています。

では、VBAコードを実行してみます。

「入力規則」で貼り付け

入力規則が貼り付けられます。

入力規則が貼り付けられる

入力規則が貼り付けられているか確認してみます。

入力規則が貼り付けられる

形式を「入力規則」として貼付けができました。

コピー元のテーマを使用してすべて貼り付け

形式を「コピー元のテーマを使用してすべて貼り付け」で貼り付けてみます。

引数には、「Paste:=xlPasteAllUsingSourceTheme」を使います。

Sub TEST7()
    
    'コピー
    ActiveSheet.Range("A1").CurrentRegion.Copy
    
    'コピー元のテーマを使用してすべて貼り付け
    ActiveSheet.Range("D1").PasteSpecial Paste:=xlPasteAllUsingSourceTheme
        
End Sub

セルに値を入力しておきました。

セルに値や書式を入力

セルに値を入力

B1には数式を入力しています。

数式を入力

B2には入力規則を設定しています。

入力規則を設定

では、VBAコードを実行してみます。

「コピー元のテーマを使用してすべて貼り付け」で貼り付け

値や書式が貼り付けられます。

値が貼り付けられる

数式も貼り付けられています。

数式が貼り付けられる

入力規則もそのまま貼り付けられています。

入力規則もそのまま貼り付けられる

形式を「コピー元のテーマを使用してすべて貼り付け」として貼付けができました。

形式が「すべて」と違いがわかりません。

この「コピー元のテーマを使用してすべて貼り付け」は使わなくても良さそうです。

罫線を除くすべて

形式を「罫線を除くすべて」で貼り付けてみます。

引数には、「Paste:=xlPasteAllExceptBorders」を使います。

Sub TEST8()
    
    'コピー
    ActiveSheet.Range("A1").CurrentRegion.Copy
    
    '罫線を除くすべて
    ActiveSheet.Range("D1").PasteSpecial Paste:=xlPasteAllExceptBorders
    
End Sub

セルに値を入力しておきました。

セルに罫線や数式を入力

セルに値を入力

B1には数式を入力しています。

数式を入力

B2には入力規則を設定しています。

入力規則を設定

では、VBAコードを実行してみます。

「罫線を除くすべて」で貼り付け

罫線を除くすべてが貼り付けられます。

値が貼り付けられる

罫線は貼り付けられていません。

罫線は貼り付けられない

数式は貼り付けられています。

数式が貼り付けられる

入力規則もそのまま貼り付けられています。

入力規則もそのまま貼り付けられる

形式を「罫線を除くすべて」として貼付けができました。

列幅

形式を「列幅」で貼り付けてみます。

引数には、「Paste:=xlPasteColumnWidths」を使います。

Sub TEST9()
    
    'コピー
    ActiveSheet.Range("A1").CurrentRegion.Copy
    
    '列幅
    ActiveSheet.Range("D1").PasteSpecial Paste:=xlPasteColumnWidths
    
End Sub

セルに値を入力しておきました。

セルの列幅を変更

セルに値を入力

セルの列幅を変更しています。

では、VBAコードを実行してみます。

「列幅」で貼り付け

列幅だけ貼り付けられます。

列幅だけが貼り付けられる

形式を「列幅」として貼付けができました。

値や書式は貼り付けられていません。

数式と数値の書式

形式を「数式と数値の書式」で貼り付けてみます。

引数には、「Paste:=xlPasteFormulasAndNumberFormats」を使います。

Sub TEST10()
    
    'コピー
    ActiveSheet.Range("A1").CurrentRegion.Copy
    
    '数式と数値の書式
    ActiveSheet.Range("D1").PasteSpecial Paste:=xlPasteFormulasAndNumberFormats
    
End Sub

セルに値を入力しておきました。

セルに数式を入力

数式を入力

B1には数式を入力しています。

では、VBAコードを実行してみます。

「数式と数値の書式」で貼り付け

数式と数値の書式のみが貼り付けられます。

値が貼り付けられる

数値の書式が貼り付けられます。

数値の書式が貼り付けられる

数式も貼り付けられます。

数式も貼り付けられる

形式を「数式と数値の書式」として貼付けができました。

値と数値の書式

形式を「値と数値の書式」で貼り付けてみます。

引数には、「Paste:=xlPasteValuesAndNumberFormats」を使います。

Sub TEST11()
    
    'コピー
    ActiveSheet.Range("A1").CurrentRegion.Copy
    
    '値と数値の書式
    ActiveSheet.Range("D1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    
End Sub

セルに値を入力しておきました。

セルに数式を入力

数式を入力

B1には数式を入力しています。

では、VBAコードを実行してみます。

「値と数値の書式」で貼り付け

値や数値の書式だけが貼り付けられます。

値が貼り付けられる

値の書式も貼り付けられています。

値の書式が貼り付けられる

数式は貼り付けられていなくて、値となります。

数式は値となって貼り付けられる

形式を「値と数値の書式」として貼付けができました。

すべての結合されている条件付き書式

形式を「すべての結合されている条件付き書式」で貼り付けてみます。

引数には、「Paste:=xlPasteAllMergingConditionalFormats」を使います。

Sub TEST12()
    
    'コピー
    ActiveSheet.Range("A1").CurrentRegion.Copy
    
    '全ての結合されている条件付き書式
    ActiveSheet.Range("D1").PasteSpecial Paste:=xlPasteAllMergingConditionalFormats
    
End Sub

セルに条件付き書式を設定しておきました。

セルに条件付き書式を設定

セルに値を入力

条件付き書式が設定されています。

では、VBAコードを実行してみます。

「すべての結合されている条件付き書式」で貼り付け

条件付き書式や値、書式が貼り付けられます。

条件付き書式や値、書式が貼り付けられる

条件付き書式が貼り付けられています。

条件付き書式が貼り付けられている

数式もそのまま貼り付けられています。

数式も貼り付けられる

入力規則もそのまま貼り付けられています。

入力規則も貼り付けられる

形式を「すべての結合されている条件付き書式」として貼付けができました。

ただ、この「すべての結合されている条件付き書式」も、「すべて」の形式と違いがわかりません。

「すべて」の形式を使えば良さそうです。

転置して貼り付け

値を「転置」して貼り付けてみます。

転置して貼付けは、「Traspose:=True」を入力するとできます。

Sub TEST13()
    
    'コピー
    ActiveSheet.Range("A1").CurrentRegion.Copy
    
    '転置して貼り付け
    ActiveSheet.Range("D1").PasteSpecial Paste:=xlPasteAll, Transpose:=True
    
End Sub

セルに値を入力しておきました。

セルに値や書式を入力

セルに値を入力

では、VBAコードを実行してみます。

「転置」して貼り付け

値が転置して貼り付けられます。

値が貼り付けられる

形式を「転置」して貼付けができました。

PasteSpecialの形式を組み合わせ

PasteSpecialは形式を組み合わせて、貼り付けすることもできます。

形式を「すべて」と「列幅」を組み合わせて、貼り付けてみます。

すべて+列幅を組み合わせる

形式を「すべて」と「列幅」を組み合わせて、貼り付けするには次のように2つ「PasteSpecial」を使います。

Sub TEST14()
    
    'コピー
    ActiveSheet.Range("A1").CurrentRegion.Copy
    
    'すべて
    ActiveSheet.Range("D1").PasteSpecial Paste:=xlPasteAll
    '列幅
    ActiveSheet.Range("D1").PasteSpecial Paste:=xlPasteColumnWidths
    
End Sub

「すべて」の「Paste:=xlPasteAll」と、「列幅」の「Paste:=xlPasteColumnWidths」を組み合わせています。

セルに値と書式を入力しておきました。

列幅を変更して値や書式を入力

セルに値と書式を入力

列幅を変更しています。

セルB1には、数式を入力しています。

セルB1に数式を入力している

セルB2には、入力規則を設定しています。

セルB2に入力規則を設定

では、VBAコードを実行してみます。

「すべて」+「列幅」を貼り付け

値と書式、そして列幅が貼り付けられる

値と書式、そして列幅が貼り付けられました。

数式も貼り付けられています。

数式が貼り付けられている

入力規則も貼り付けられています。

入力規則が貼り付けられる

こんな感じで、形式の「すべて」と「列幅」を組み合わせることもできます。

別シートをアクティブにせず貼り付け可能

「PasteSpecail」は、別シートに貼り付ける際に、別シートをアクティブにしなくても、貼り付けることができます。

やってみます。

PasteSpecailで別シートに貼り付け

PasteSpecialで別シートに「すべて」を貼り付けてみます。

Sub TEST15()
    
    'Sheet1をコピー
    Worksheets("Sheet1").Range("A1").CurrentRegion.Copy
    'Sheet2に貼り付け
    Worksheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteAll
    
End Sub

「Sheet1」のセルに値と書式を入力しておきました。

Sheet1に値と書式を入力

Sheet1のセルに値と書式を入力しておいた

では、実行してみます。

Sheet2に貼り付け

Sheet2のセルに値と書式のすべてを貼り付けられました

Sheet2のセルに値と書式のすべてを貼り付けられました。

Sheet2をアクティブにすることなく、セルを貼付けすることができます。

おわりに

この記事では、形式を選択して貼り付ける方法について、ご紹介しました。

形式を選択して貼り付けるには、「PasteSpecial」を使います。

「PasteSpecial」の形式を変更して、「すべて」や「書式のみ」を貼り付けることができます。

値だけでなく、書式や列幅も貼り付けたい場合に、便利です。

参考になればと思います。最後までご覧くださいまして、ありがとうございました。

関連する記事から探す

カテゴリから探す

カテゴリから見たい項目を探すと便利ですよ。

アーカイブから探す