VBAで効率化！Excelで他ブックからデータをコピペするテクニック

当ページのリンクには広告が含まれています。

エクセルのVBAを使った

他のブックからデータをコピペするテクニック を解説します

毎日の データ貼り付け作業 を自動化するのに役立ちます

具体例を２つ紹介して解説します

シート上で読出すブックのパスを指定 し、ボタンを押して他のブックの中身を読出すシンプルなパターン
「ブック名_20230101.xlsx」のような、名前の末尾に日付があるような複数のブックを、位置をずらしながら貼付ける応用パターン（シート上で読出すブックのパスを動的に変更する）

下に２つのイメージ動画があります

動画を見て求めている情報かを確認してみてください

クリックしてジャンプ

イメージ動画（音声はありません）

上記で紹介した２パターンのイメージ動画です

１つの他ブックからデータを簡単コピペ

日付が変わる複数ブックをずらしてコピペ

サンプルダウンロード

説明に使用しているエクセルファイルです

vba – 他のブックからコピペ.zip

下図のような表示が出た場合は、以下の手順でマクロを有効にできます。

一度、エクセルを閉じる
ダウンロードしたエクセルファイルを「右クリック」
「プロパティ」を選択
「全般」タブのセキュリティの「許可する」に ✓
「適用」ボタンをクリック
「OK」ボタンをクリック

他のブックからデータをコピペ

具体例１の「 １つの他ブックからデータを簡単コピペ 」を解説します

VBAコード

Sub 他ブックから貼付()
    
    '他ブックのパスはセルから取得
    Dim 読出パス As String
        読出パス = ThisWorkbook.Worksheets("他ブックから貼付").Range("D1").Value

    '他ブックが存在するかチェック、あれば処理を実行
    If Dir(読出パス) <> "" Then
        'チラついて五月蝿いのを防止
        Application.ScreenUpdating = False

        'コピーしたいエクセルブックを開き、シートをセット（アクティブにする）、コピーする
        Set 他ブック = Workbooks.Open(読出パス)
            他ブック.Worksheets("Sheet1").Activate
            他ブック.Worksheets("Sheet1").Range("B2:L19").Copy
    
        'このブックをアクティブにして、貼り付ける（値の貼り付け）
        ThisWorkbook.Worksheets("他ブックから貼付").Activate
        ThisWorkbook.Worksheets("他ブックから貼付").Range("B2").PasteSpecial Paste:=xlValues
        ThisWorkbook.Worksheets("他ブックから貼付").Range("B2").Select
    
        '保存するか否かのダイアログを表示させないで閉じる
        Application.DisplayAlerts = False
        他ブック.Close

    '他ブックが存在しない場合 メッセージを出す
    ElseIf Dir(読出パス) = "" Then
        MsgBox "ファイルが存在しません"
    End If
    
End Sub

動作説明

「このブック.xlsm」に「他ブック.xlsx」のデータを自動でコピペしたい。と想定します。

動作の流れは以下です

ボタンクリックで「他ブック.xlsx」を開き「Sheet1」シートの「B2:L19」をコピー
「このブック.xlsm」をアクティブにして「他ブックから貼付」シートの「 B2 」に値の貼付け
「他ブック.xlsx」を閉じる

コピーしたいブックのパスはセル（動画では D1 セル）に入力してあります

Ｄ１セルの中身はこんな感じ

C:\excel-memo\vba\他ブックから貼付け\他ブック.xlsx

コードの解説

「他ブック.xlsx」のパスを「D1」セルから取得

取得した「他ブック.xlsx」のパスが存在するかチェック

存在する場合のみ処理を実行

ブックを開いたり閉じたりすると画面がチラつくので、それを防ぐ

「他ブック.xlsx」を開く

「Sheet1」シートをアクティブにする

「B2:L19」をコピーする。

「このブック.xlsm」をアクティブにする

「他ブックから貼付」シートの「B2」に貼付ける（値の貼付け）

カーソルの位置を「B2」にする

「ブックを保存しますか？」のダイアログが出ないように設定する

「他ブック.xlsx」を閉じる

他ブックからコピーして特定日付にずらして貼付け

具体例② の「日付が変わる複数ブックをずらしてコピぺ」を解説します

日付に基づいてデータを整理する必要がある場合に特に有用です

例えば、毎日の売上データを日付ごとにまとめて記録する、月次の報告書を作成する際に前月のデータを参照する、といった用途に適しています

VBAコード

Sub 他ブックから貼付_選択した日付の位置に()

    '他ブックのパスはセルから取得
    Dim 読出パス As String
        読出パス = ThisWorkbook.Worksheets("選択日付に貼付").Range("D1").Value

    '他ブックが存在するかチェック、あれば処理を実行
    If Dir(読出パス) <> "" Then

        'チラついて五月蝿いのを防止
        Application.ScreenUpdating = False

        'コピーしたいエクセルブックを開き、シートをセット（アクティブにする）、コピーする
        Set 他ブック = Workbooks.Open(読出パス)
            他ブック.Worksheets("Sheet1").Activate
            他ブック.Worksheets("Sheet1").Range("B2:L19").Copy
    
        'このブックをアクティブにして、貼り付ける（値の貼り付け）
        ThisWorkbook.Worksheets("選択日付に貼付").Activate

        'A列の最終行の取得（A列の最終行から上にジャンプしてその行数を取得する）
        MyRow1 = Range("A" & Rows.Count).End(xlUp).Row
        
        '日付け検索ループ
        'A列の最終行までのループ
        For Each Rng1 In Range("A2:A" & MyRow1)
            '日付が一致したら次の処理へ
            If Rng1 = Range("A1") Then
                '該当日付けに値の貼付け　Rng1.Offset(行,列）
                Rng1.Offset(0, 1).PasteSpecial Paste:=xlValues
                Rng1.Offset(0, 0).Select
                Exit For
            '違ったら次へ
            End If
        '範囲内であれば続けて処理
        Next Rng1
    
        '保存するか否かのダイアログを表示させないで閉じる
        Application.DisplayAlerts = False
        他ブック.Close

    '他ブックが存在しない場合 メッセージを出す
    ElseIf Dir(読出パス) = "" Then
        MsgBox "ファイルが存在しません"
    End If

End Sub

動作説明

外部のワークブックから特定の範囲をコピーし、現在作業しているワークブックの指定された日付の位置にそのデータを貼り付ける機能を持ちます

「このブック.xlsm」というブックと

「他ブック_YYYYMMDD.xlsx」という複数のブックが存在

各日付のブックのデータを該当日付の位置にずらしてコピペしたい

と想定しています

下の画像のように、各日付の位置にデータをずらして貼付けていきます

コード解説

STEP

他ブックのパスを取得

現在のワークブック内の特定のセルから他ブックのファイルパスを取得します

このパスは事前にユーザーが指定しておく必要があります

具体例②では、セル「D1」に入力してあります

セル「D1」の中身は以下です

="C:\excel-memo\vba\他ブックから貼付け\他ブック（日付）\他ブック_"&TEXT($A$1,"yyyymmdd")&".xlsx"

該当箇所のVBAコードは以下です

'他ブックのパスはセルから取得
Dim 読出パス As String
    読出パス = ThisWorkbook.Worksheets("選択日付に貼付").Range("D1").Value

STEP

他ブックの存在確認

指定されたパスにファイルが実際に存在するかを確認します

ファイルが見つからない場合はユーザーに警告するメッセージボックスが表示されます

該当箇所のVBAコードは以下です

'他ブックが存在するかチェック、あれば処理を実行
If Dir(読出パス) <> "" Then

  ' ～ 処理（省略） ～

'他ブックが存在しない場合 メッセージを出す
ElseIf Dir(読出パス) = "" Then
    MsgBox "ファイルが存在しません"
    
End If

STEP

画面のチラつき防止

処理中にExcelの画面が頻繁に更新されることで生じるチラつきを防ぐために ScreenUpdatingプロパティをFalseに設定します

処理の最中は画面が更新されなくなり、処理完了後に一度に変更が反映されます

該当箇所のVBAコードは以下です

'チラついて五月蝿いのを防止
Application.ScreenUpdating = False

STEP

データのコピー

指定されたパスからワークブックを開き指定されたシートとセル範囲からデータをコピーします

ワークブックを開いた状態で対象のシートをアクティブにし、指定された範囲を選択してコピーを行います

該当箇所のVBAコードは以下です

'コピーしたいエクセルブックを開き、シートをセット（アクティブにする）、コピーする
Set 他ブック = Workbooks.Open(読出パス)
    他ブック.Worksheets("Sheet1").Activate
    他ブック.Worksheets("Sheet1").Range("B2:L19").Copy

STEP

貼り付け先の準備

コピーしたデータを貼り付けるために元のワークブックと特定のシートを再びアクティブにします

該当箇所のVBAコードは以下です

'このブックをアクティブにして、貼り付ける（値の貼り付け）
ThisWorkbook.Worksheets("選択日付に貼付").Activate

STEP

データの貼り付け

A列を検索して指定された日付が存在する行を探します

該当する日付が見つかった場合、その日付の隣のセルからデータの貼り付けを行います

PasteSpecialメソッドを使用して値のみを貼り付けることで、元のフォーマットや式は貼り付けられず、値のみが反映されます

該当箇所のVBAコードは以下です

'A列の最終行の取得（A列の最終行から上にジャンプしてその行数を取得する）
MyRow1 = Range("A" & Rows.Count).End(xlUp).Row
        
'日付け検索ループ
'A列の最終行までのループ
For Each Rng1 In Range("A2:A" & MyRow1)

    '日付が一致したら次の処理へ
    If Rng1 = Range("A1") Then

        '該当日付けに値の貼付け　Rng1.Offset(行,列）
        Rng1.Offset(0, 1).PasteSpecial Paste:=xlValues
        Rng1.Offset(0, 0).Select
        Exit For

    '違ったら次へ
    End If

'範囲内であれば続けて処理
Next Rng1

STEP

他ブックの閉じ方

開いた他ブックを保存せずに閉じます

DisplayAlertsプロパティをFalseに設定することで、保存の確認ダイアログを表示させずに閉じることができます

スクリプトの自動実行が中断されることなく、スムーズに処理を完了させることが可能です

該当箇所のVBAコードは以下です

'保存するか否かのダイアログを表示させないで閉じる
Application.DisplayAlerts = False
他ブック.Close

「このブック.xlsm」と「他ブック_YYYYMMDD.xlsx」という複数ブックがあり、各日付のデータをずらしてコピペしたい。と想定します

下の画像のように、各日付のブックをずらして貼付けていきます

コピー元のブックのパスはセル（動画では D1 セル）に入力してある

「D1」セルのパスは「A1」セルに入力した日付によってが動的に変化するようになっています

「Ｄ１」セルの中はこんな感じです

="C:\excel-memo\vba\他ブックから貼付け\他ブック（日付）\他ブック_"&TEXT($A$1,"yyyymmdd")&".xlsx"

`.PasteSpecial` `Paste` プロパティ一覧

この記事の事例では「値の貼り付け」を紹介しました

.PasteSpecial メソッドの Paste 引数の設定可能なプロパティ一覧を以下に示します

プロパティ一覧表

スクロールできます

プロパティ値	説明
`xlPasteAll`	全ての要素を貼り付け
`xlPasteAllExceptBorders`	枠線を除く全ての要素を貼り付け
`xlPasteAllMergingConditionalFormats`	条件付き書式を結合しながら全てを貼り付け
`xlPasteAllUsingSourceTheme`	ソースのテーマで全てを貼り付け
`xlPasteColumnWidths`	列の幅のみを貼り付け
`xlPasteComments`	コメントのみを貼り付け
`xlPasteFormats`	書式のみを貼り付け
`xlPasteFormulas`	数式のみを貼り付け
`xlPasteFormulasAndNumberFormats`	数式と数値の形式のみを貼り付け
`xlPasteValidation`	データ検証の設定のみを貼り付け
`xlPasteValues`	値のみを貼り付け
`xlPasteValuesAndNumberFormats`	値と数値の形式のみを貼り付け

補足事項

条件付き書式を結合しながらすべてを貼り付け (xlPasteAllMergingConditionalFormats)
貼り付け元の条件付き書式と貼り付け先の条件付き書式が統合され、両方の条件が新しい場所で有効に機能します。例えば、貼り付け元に特定の値以上でセルが赤くなる設定があり、貼り付け先に特定の値以下で青くなる設定がある場合、両方の設定が適用されます。
ソースのテーマを使用して全てを貼り付け (xlPasteAllUsingSourceTheme)
貼り付け元のドキュメントに設定されているテーマ（色、フォント、スタイル）を保持して内容を貼り付けます。これにより、異なるテーマ設定を持つドキュメント間でデザインの一貫性を維持することが可能です。

これらのプロパティを .PasteSpecial メソッドの Paste 引数に指定することで、特定の内容だけを対象の範囲に貼り付けることが可能です