【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 オブジェクトのRangeプロパティを使い範囲を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でセルに入れたブック名&パス一覧から別ブックを開き、値のみコピーして貼り付ける方法について説明をします。

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

タカヒロ
タカヒロ
一部拡張子が異なるファイルも含めています。

次に「コピー先ファイル一覧」シートを元シート.xlsmへ追加し、以下のようにA2セルにファイルが格納されているパスを入力、
B列にファイル名、C列に拡張子を入力していきます。

VBAコード

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

Sub シートの表をコピー先ファイル一覧にあるブックへコピーする()

    Dim objWorkbook As Workbook
    Dim i As Integer
    Dim strBookList As Variant
    Dim intLastRowNum As Integer
    
    
    '元シートのセル範囲をコピーします。
    Workbooks("元シート.xlsm").Sheets("ひな形").Range("A1:D8").Copy


    With Workbooks("元シート.xlsm").Sheets("コピー先ファイル一覧")
        'B列を対象に最終行数を取得します。
        intLastRowNum = .Cells(Rows.Count, 2).End(xlUp).Row

        For i = 2 To intLastRowNum
            
            '文字結合しファイルパスを作成します。
            strBookList = .Cells(2, 1).Value & .Cells(i, 2).Value & .Cells(i, 3).Value
            
            'コピー先となる別ブックを開きます。
            Set objWorkbook = Workbooks.Open(strBookList)
    
            '別ブックへ値のみ貼り付けします。
            objWorkbook.Sheets("Sheet1").Range("A1:H9").PasteSpecial Paste:=xlPasteValues
            
        Next i
        
    End With


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

VBAを設定する

元シートのコピーしたいセル範囲を指定します。
サンプルではひな形シートのセル範囲”A1:D8″を指定しています。

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

VBAを実行する

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

セルで指定した別ブックへ値のみコピーされましたね!
拡張子が異なっていても問題ありません!

VBAの説明

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

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

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

“元シート.xlsm”の”コピー先ファイル一覧”をWithにセットします。

With Workbooks("元シート.xlsm").Sheets("コピー先ファイル一覧")

B列を対象に最終行数を取得します。

intLastRowNum = .Cells(Rows.Count, 2).End(xlUp).Row

最終行数分ファイル名取得処理を繰り返します。

For i = 2 To intLastRowNum

文字結合しファイルパスを作成します。

strBookList = .Cells(2, 1).Value & .Cells(i, 2).Value & .Cells(i, 3).Value

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

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でシートを複数の別ブックへ値のみコピーする方法

についてまとめました。

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



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

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








10 件のコメント

  • ド素人でも解りやすく感謝感謝です。
    でも今回は解りません。66歳ではムズイです。
    別ブックへ値のみコピーする_行指定のWorkbooks(“Book2.xlsm”).Sheets(“sheet1”).Rows(“24:17”).Copy でインデックスが有効範囲にありません。と出て止まります。ここでのインデックスが解りません。教えて頂ければ幸いです。

    • いつもご利用ありがとうございます。

      別ブックへ値のみコピーする行指定でインデックスが有効範囲でないエラーが出てしまう件ですが、
      シート名かブック名が実際のものと異なっている値を指定している場合に表示されますので、今一度指定の内容が
      合っているかご確認いただけますでしょうか。
      具体的に以下の場合ですと、
      Workbooks(“Book2.xlsm”).Sheets(“sheet1”)
      ブック名「Book2.xlsm」にマクロを実装し、「sheet1」シートにコピーしたい値を入れる形になります。
      マクロは「Book2.xlsm」を開いた上で実行するようにお願いします。

  • お世話になります。
    文章が分かりにくくすみません。

    通常ですと、”コピー元のブック内セルに、コピー先のブック名を入力参照して操作する”のですが、希望としては”コピー先のブック内セルに、コピー元のブック名を入力参照して操作したい”が希望です(コピー先のブック内で操作したい)

    理由はコピー先は常に同じブック名なのですが、コピー元ブック末尾の日付が変わる為、そちらをコピー先のセル入力の日付変更により、操作したいということでした。

    何か良い方法がありましたら、ご教授よろしくお願いします。

    • ご連絡ありがとうございます。

      そうしますと、VBA実行用ブックとコピー元ブックはわけたほうが良いでしょうか。
      コピー先ブック内のJ1セルにコピー元ブックのパスが記載されていることを前提とすると、
      以下のように変更をすれば可能となります。

      ■変更
      ‘元シートのセル範囲をコピーします。⇒こちらは無効
      ‘Workbooks(“元シート.xlsm”).Sheets(“ひな形”).Range(“A1:D8”).Copy

      With Workbooks(“元シート.xlsm”).Sheets(“コピー先ファイル一覧”)
      ‘B列を対象に最終行数を取得します。
      intLastRowNum = .Cells(Rows.Count, 2).End(xlUp).Row

      For i = 2 To intLastRowNum

      ‘文字結合しファイルパスを作成します。
      strBookList = .Cells(2, 1).Value & .Cells(i, 2).Value & .Cells(i, 3).Value

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

      ‘コピー先J1セルにあるコピー元情報を取得し、別ブックを開きます。⇒追加
      Set objWorkbookMoto = Workbooks.Open(objWorkbook.Sheets(“Sheet1”).Range(“J1”).Value)

      ‘元シートのセル範囲をコピーします。⇒追加
      objWorkbookMoto.Sheets(“ひな形”).Range(“A1:D8”).Copy

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

      Next i

      End With

  • お世話様になっております。
    早速の回答ありがとうございました。流れが分かりやすく、実践して無事に実行できました。

    ちなみに逆の場合、別ブックに→別ブックからの場合、ブック元をセル参照にするにはどのようになるでしょうか?

    重ね重ね申し訳ありませんが、宜しくお願いします。

    • できたようでよかったです。

      追加のご質問につきまして、
      元のブック(元シート.xlsm)のブック名をセルへ記載し、コピー先の対象に含めることでよいでしょうか?
      そうでしたら元ブック「元シート」「.xlsm」をコピー先ファイル一覧のB列、C列に入力いただき、
      コピー先のシート「Sheet1」を追加した上実行いただければできるかと存じます。

  • お世話になっております。
    別ブックからのコピー&ペーストですが、ブック名をセルに入力した文字列を参照して開くことは可能でしょうか。

    例えば220928というブックがあり、セルに220928と入力しておいて、それを参照してブックを開く。

    文章稚拙で申し訳ありませんが、よろしくお願いします。

  • コメントを残す

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