【Excel VBA】別ブックに値のみコピーする方法!複数ブック対応可!

VBAでシートを別ブックへ値のみコピーしたいときはないでしょうか。

けど、そんな中で悩むことは、

・VBAでシートを別ブックへ値のみコピーしたいが方法がよくわからない
・VBAのシートを複数の別ブックへ値のみコピーしたいが方法がわからない

ですよね。

今回はそんなお悩みを解決する

・VBAでシートを別ブックへ値のみコピーする方法
・VBAでシートを複数の別ブックへ値のみコピーする方法

についてまとめます!

VBAでシートを別ブックへ値のみコピーするイメージ

VBAでシートを別ブックへ値のみコピーするイメージについて説明をします。

前回の「別シートへ値のみ値のみコピーして貼り付ける方法」の続編となります。

以下のようにコピー元となるひな形シートと

コピー先の別ブックを用意します。

通常Copyメソッドは書式は数式が入る形でコピーされますが、今回は値のみにした上で別ブックへコピーするようにします。

さらに複数の別ブックへ値のみコピーしていきます。

それでは早速試してみましょう。

VBAでシートを別ブックへ値のみコピーして貼り付ける方法

VBAでシートを別ブックへ値のみコピーして貼り付ける方法について説明をします。

サンプルデータを準備する

サンプルデータを元シートとなるひな形シートへ入力しましょう。

タカヒロ
タカヒロ
値のみ貼り付けられているか確認のため、数式や書式設定をいれるとよいでしょう。

コピー先のブックを新規作成します。

VBAコード

サンプルのVBAコードは以下の通りです。

Sub 別ブックへ値のみコピーする()
    
    Dim objWorkbook As Workbook
    
    'コピー先となる別ブックを開きます。
    Set objWorkbook = Workbooks.Open("F:\test\別ブック.xlsx")
    
    '元シートのセル範囲をコピーします。
    Workbooks("元シート.xlsm").Sheets("ひな形").Range("A1:H9").Copy
    
    '別ブックへ値のみ貼り付けします。
    objWorkbook.Sheets("Sheet1").Range("A1:H9").PasteSpecial Paste:=xlPasteValues
    
    '範囲選択を解除します。
    Application.CutCopyMode = False
    
    'オブジェクトを解放します。
    Set objWorkbook = Nothing

End Sub

VBAの実装

VBAの実装方法については
VBAの実装手順
をご参照ください。

VBAを設定する

VBAを設定していきましょう。

コピー先となる別ブックのパスを指定します。

Set objWorkbook = Workbooks.Open("F:\test\別ブック.xlsx")

元シートのセル範囲を指定します。

元シートのファイル名を入れ、シートは”ひな形”シート、セル範囲は”A1:H9″とします。

Workbooks("元シート.xlsm").Sheets("ひな形").Rows("1:9").Copy

別シートを指定しします。

シートは”Sheet1″シート、セル範囲は”A1:H9″とします。

objWorkbook.Sheets("Sheet1").Range("A1:H9").PasteSpecial Paste:=xlPasteValues

タカヒロ
タカヒロ
コピー元のセル範囲とコピー先のセル範囲は同じにするようにしましょう。

VBAを実行する

VBAを実行してみましょう。

別ブックに値のみコピーされましたね!

VBAの説明

VBAについて説明をします。

コピー先となる別ブックを開き、オブジェクトにセットします。

Set objWorkbook = Workbooks.Open("F:\test\別ブック.xlsx")

元シートのセル範囲をCopyメソッドでコピーします。

Workbooks("元シート.xlsm").Sheets("ひな形").Range("A1:H9").Copy

シートを別ブックへ値のみ貼り付けをします。

値のみ貼り付けの場合は、PasteSpecialメソッドのPasetプロパティにxlPasteValuesを指定します。

objWorkbook.Sheets("Sheet1").Range("A1:H9").PasteSpecial Paste:=xlPasteValues

PasteSpecialメソッド

PasteSpecialメソッドの構文は以下の通りです。

構文 Rangeオブジェクト.PasteSpecial(Format, Link, DisplayAsIcon, IconFileName, IconIndex, IconLabel, NoHTMLFormatting)
説明 指定された形式で、クリップボードの内容をシートに貼り付けます。 パラメータ「Format」によりデータを貼り付ける形式を指定できます。
Format:クリップボードのデータの形式を文字列で指定します。
Link(省略可能):リンクを設定するには Trueを指定します。既定値は False です。
DisplayAsIcon(省略可能):True を指定すると、貼り付けデータがアイコンとして表示されます。 既定値は False です。
IconFileName(省略可能):DisplayAsIcon が True の場合に使用するアイコンを含 むファイルの名前 を指定します。
IconIndex(省略可能):アイコン ファイルのどのアイコンを使うかを示すインデックス番号を指定します。
IconLabel(省略可能):アイコンのラベルを文字列で指定します。
NoHTMLFormatting(省略可能):True を設定すると、HTML から書式設定、ハイパーリンク、およびイメージを削除します。 False を設定すると、HTML をそのまま貼り付けます。 既定値は False です。

タカヒロ
タカヒロ
その他の貼り付けの形式のプロパティは以下ページをご参照ください。

【Excel VBA】Paste系メソッドで値貼り付けする方法!他の値貼り付け方法も!

コピーした場合、範囲選択の破線が点滅するコピーモードの状態になりますが、その範囲選択を解除します。

Application.CutCopyMode = False

オブジェクトを解放します。

Set objWorkbook = Nothing

VBAでシートを別ブックへ値のみコピーして貼り付ける方法 (列指定版)

これまでセル範囲を指定していましたが、列指定でシートを別ブックへ値のみコピーして貼り付ける方法について説明をします。

VBAコード

サンプルのVBAコードは以下の通りです。

Sub 別ブックへ値のみコピーする_列指定()
    
    Dim objWorkbook As Workbook
    
    'コピー先となる別ブックを開きます。
    Set objWorkbook = Workbooks.Open("F:\test\別ブック.xlsx")
    
    '元シートのセル範囲をコピーします。
    Workbooks("元シート.xlsm").Sheets("ひな形").Columns("A:H").Copy
    
    '別ブックへ値のみ貼り付けします。
    objWorkbook.Sheets("Sheet1").Columns("A:H").PasteSpecial Paste:=xlPasteValues
    
    '範囲選択を解除します。
    Application.CutCopyMode = False
    
    'オブジェクトを解放します。
    Set objWorkbook = Nothing

End Sub

VBAを設定する

VBAを設定していきましょう。

元シートのセル範囲を指定します。

シートは”ひな形”シート、範囲となる列は”A:H”とします。

Sheets("ひな形").Columns("A:H").Copy

別シートを指定しします。

シートは”Sheet1″シート、範囲となる列は”A:H”とします。

Workbooks("元シート.xlsm").Sheets("ひな形").Columns("A:H").Copy

VBAを実行する

VBAを実行してみましょう。

指定列の範囲が別ブックへ値のみコピーされましたね!

VBAの説明

VBAについて説明をします。

これまでRangeプロパティでセル範囲を指定していましたが、今回は Worksheet オブジェクトのColumns プロパティを使い列指定をし、その範囲をCopyメソッドでコピーします。

Workbooks("元シート.xlsm").Sheets("ひな形").Columns("A:H").Copy

VBAでシートを別ブックへ値のみコピーして貼り付ける方法 (行指定版)

次に、行指定でシートを別ブックへ値のみコピーして貼り付ける方法について説明をします。

VBAコード

サンプルのVBAコードは以下の通りです。

Sub 別ブックへ値のみコピーする_行指定()
    Dim objWorkbook As Workbook
    
    'コピー先となる別ブックを開きます。
    Set objWorkbook = Workbooks.Open("F:\test\別ブック.xlsx")
    
    '元シートの指定行数分コピーします。
    Workbooks("元シート.xlsm").Sheets("ひな形").Rows("1:9").Copy
    
    '別ブックへ値のみ貼り付けします。
    objWorkbook.Sheets("Sheet1").Rows("1:9").PasteSpecial Paste:=xlPasteValues
    
    '範囲選択を解除します。
    Application.CutCopyMode = False
    
    'オブジェクトを解放します。
    Set objWorkbook = Nothing

End Sub

VBAを設定する

VBAを設定していきましょう。

元シートのセル範囲を指定します。

シートは”ひな形”シート、範囲となる行は”1:9″とします。

Sheets("ひな形").Rows("1:9").Copy

別シートを指定しします。

シートは”Sheet1″シート、範囲となる行は”1:9″とします。

Workbooks("元シート.xlsm").Sheets("ひな形").Rows("1:9").Copy

VBAを実行する

VBAを実行してみましょう。

指定行の範囲が別ブックへ値のみコピーされましたね!

VBAの説明

VBAについて説明をします。

Worksheet オブジェクトのRowsプロパティを使い行を指定をし、その範囲をCopyメソッドでコピーします。

Workbooks("元シート.xlsm").Sheets("ひな形").Rows("1:9").Copy

VBAでシートを複数の別ブックへ値のみコピーして貼り付ける方法

最後に、VBAでシートを複数の別ブックへ値のみコピーして貼り付ける方法について説明をします。

まずはコピー先となる別ブックを複数用意しましょう。

VBAコード

サンプルのVBAコードは以下の通りです。

Sub シートを複数の別ブックへ複数コピーする1()

    Dim objWorkbook As Workbook
    Dim i As Integer
    Dim arrBookList As Variant
    Dim strBookList As Variant
    
    '別ブック格納先パスを入力します。
    arrBookList = Array("F:\test\別ブック.xlsx", "F:\test\別ブック1.xlsx", "F:\test\別ブック2.xlsx")
    
    '元シートのセル範囲をコピーします。
    Workbooks("元シート.xlsm").Sheets("ひな形").Range("A1:H9").Copy

    For Each strBookList In arrBookList

        'コピー先となる別ブックを開きます。
        Set objWorkbook = Workbooks.Open(strBookList)

        '別ブックへ値のみ貼り付けします。
        objWorkbook.Sheets("Sheet1").Range("A1:H9").PasteSpecial Paste:=xlPasteValues
    
    Next

    '範囲選択を解除します。
    Application.CutCopyMode = False
    
    'オブジェクトを解放します。
    Set objWorkbook = Nothing
    
  
End Sub

VBAを設定する

VBAを設定していきましょう。

別ブック格納先パスを入力します。

arrBookList = Array("F:\test\別ブック.xlsx", "F:\test\別ブック1.xlsx", "F:\test\別ブック2.xlsx")

元シートのセル範囲を指定します。

シートは”ひな形”シート、範囲は”A1:H9″とします。

Workbooks("元シート.xlsm").Sheets("ひな形").Range("A1:H9").Copy

別シートを指定しします。

シートは”Sheet1″シート、範囲は”A1:H9″とします。

objWorkbook.Sheets("Sheet1").Range("A1:H9").PasteSpecial Paste:=xlPasteValues

VBAを実行する

VBAを実行してみましょう。

複数の別ブックへ値のみコピーされましたね!

VBAの説明

VBAについて説明をします。

別ブックのパスを配列の各要素に入力します。ブックを増やす場合はカンマ(,)で区切り追加してください。

arrBookList = Array("F:\test\別ブック.xlsx", "F:\test\別ブック1.xlsx", "F:\test\別ブック2.xlsx")

Worksheet オブジェクトのRowsプロパティを使い行を指定をし、その範囲をCopyメソッドでコピーします。

Workbooks("元シート.xlsm").Sheets("ひな形").Range("A1:H9").Copy

パスがある件数分処理を繰り返します。

For Each strBookList In arrBookList

Next

コピー先となる別ブックを開きます。

Set objWorkbook = Workbooks.Open(strBookList)

別ブックへ値のみ貼り付けします。

objWorkbook.Sheets("Sheet1").Range("A1:H9").PasteSpecial Paste:=xlPasteValues

VBAの実装手順

実装手順は以下の通りです。

Excel側にVBAを実装していきます。

①Excelを新規に開き、「開発」タブをクリックし、「VisualBasic」をクリックします。
もしくはショートカットキー「Alt」+「F11」でもOKです。

②標準モジュールを追加します。
左ペインのVBAProjectを右クリックし、「挿入」、「標準モジュール」を選択します。

③右ペインのウインドウに上記のVBAを入力します。

こちらで完了です。

VBAを実行する

では早速VBAの実行をしてみましょう。

①「開発」タブの「VBA」をクリックし実行したいマクロを選択し、「実行」をクリックします。

②処理がされたことが確認できれば完了です。

さいごに

いかがでしょうか。

今回は、

・VBAでシートを別ブックへ値のみコピーする方法
・VBAでシートを複数の別ブックへ値のみコピーする方法

についてまとめました。

また、他にも便利な方法がありますので、よろしければご参照頂ければと思います。



この記事の関連キーワード

こちらの記事の関連キーワード一覧です。クリックするとキーワードに関連する記事一覧が閲覧できます。



 データ分析で四苦八苦している方にオススメ! 
Power BIをカンタン習得できるおすすめ本! 最短習得方法も!

Power BIをカンタン習得できるおすすめ本!裏技|最短習得方法も!

サクッとPowerBIでデータ分析できる人になりましょう!

タカヒロ

タカヒロ
実質無料で読めるExcelVBA本についてまとめました。
もしVBA本購入を検討されていたら、どれだけお得か確かめてみてください。

【¥0】実質無料のExcelVBAおすすめ本25選!初級~中級まで網羅!

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です