エクセルマクロVBAで別ブックから転記｜vlookup関数の代用で元ブックに出力

マスタデータから情報を取得したいとき、エクセルのVlookup関数を使うと便利です。

実際、元ブックにvlookup関数を設定しておくことで、IDをキーにしてマスタブックの情報を参照させることが可能です。

しかしエクセル関数を使った事例はいくつか紹介されていますが、VBAではどのようにプログラムを作成すればいいのか分からないことも少なくありません。

そこでVBAを使って、別ブックに保存されたマスタブックから情報を取得する方法を紹介します。

・別ブック(参照ブック)の情報を取得して、元ブックに転記する

それでは以下で詳しく紹介していきます。

1 VBAで別ブックから転記(マスタファイルを参照して元ブックに出力)
2 VBA入りのエクセルファイルをダウンロード
3 VBA作成前の準備
4 VBAのプログラムソース解説
5 類似のVBAプログラムの解説
- 5.1 エクセル関数をVBAで作ってみる
- 5.2 転記マクロ
6 Excel VBAについて詳しく理解したいなら
- 6.1 VBAの勉強方法

VBAで別ブックから転記(マスタファイルを参照して元ブックに出力)

このページで紹介するのは、製品コードの情報をもとに、製品名や単価を取得するプログラムです。

今回は、以下のようなデータ一覧を入力された元ブック(Data.xlsm)を事例にします。

このとき、参照先となるブック(Master.xlsx)をあらかじめ準備しておきます。

元ブックの製品コードをキーにして、参照ブックの情報を取得していきます。

VBA入りのエクセルファイルをダウンロード

以下で紹介しているVBAプログラムをそのまま使いたい人は、以下のフォームからダウンロードできます。

登録したメールアドレスへ「VBAプログラムが含まれたエクセル」を送信します。

本プログラムの内容をそのまま使用可能です。ぜひお仕事にお役立てください。

読み込んでいます…

それでは以下で、具体的なプログラムの解説をしていきます。

VBA作成前の準備

ExcelでVBAを使うためにはいくつかの準備が必要です。

具体的には以下です。

1. Excelファイルを「.xlsm」として保存

2. 開発タブを追加

上記に関しては、以下のページで解説をしています。

エクセルマクロ使い方｜編集方法をVBA作成から保存のやり方まで解説

これからエクセルマクロを勉強しようとする人にとって、マクロの編集は一つのカベです。マクロというこれまで触ったことのないものを操作できるようになるのは決して簡単なことではありません。しかし順序良く学んでいけば、難しいも […]

もしVBAを使うための準備段階に不安がある人は上記をご覧ください。

なお、以下の動画でも紹介しています。

入門エクセルマクロの使い方｜マクロ作成から実行までを徹底解説

詳細を知りたい人は動画をご覧ください。

VBAのプログラムソース解説

今回紹介するプログラムの概要は以下です。

'プログラム0｜変数宣言の指定
Option Explicit

'プログラム1｜プログラム開始
Sub GetDataFromOtherBook()

    'プログラム2｜対象ブック(Dataブック)のシートを設定
    Dim datasheet As Worksheet
    Set datasheet = ThisWorkbook.Worksheets("Sheet1")
    
    'プログラム3｜datasheetの最終行を取得
    Dim cmax1 As Long
    cmax1 = datasheet.Range("A65536").End(xlUp).Row
    
    'プログラム4｜マスタブックを開く
    Dim masterfile As String
    masterfile = ThisWorkbook.Path & "\Master.xlsx"
    
    Dim master As Workbook
    Set master = Workbooks.Open(Filename:=masterfile)
    
    Dim mastersheet As Worksheet
    Set mastersheet = master.Worksheets("Sheet1")
        
    'プログラム5｜マスタファイルの最終行の取得
    Dim cmax2 As Long
    cmax2 = mastersheet.Range("A65536").End(xlUp).Row
    
    'プログラム6｜変数設定
    Dim product_code As String, master_code As String, product_name As String
    Dim i As Long, j As Long, product_price As Long
    
    'プログラム7｜DataブックのB列を取得
    For i = 2 To cmax1
        product_code = datasheet.Range("B" & i).Value
                   
        'プログラム8｜マスタブックのA列を取得
        For j = 2 To cmax2
            master_code = mastersheet.Range("A" & j).Value
            
            'プログラム9｜DataブックのB列とマスタブックのA列をマッチング
            If product_code = master_code Then
                product_name = mastersheet.Range("B" & j).Value
                product_price = mastersheet.Range("C" & j).Value
                Exit For
            End If
        Next
        
        'プログラム10｜DataブックのE列とF列に出力
        datasheet.Range("E" & i).Value = product_name
        datasheet.Range("F" & i).Value = product_price
    Next
    
    'プログラム11｜マスタファイルを閉じる
    master.Close

'プログラム12｜プログラム終了
End Sub

'プログラム0｜変数宣言の指定

Option Explicit

'プログラム1｜プログラム開始

Sub GetDataFromOtherBook()

'プログラム2｜対象ブック(Dataブック)のシートを設定

Dim datasheet As Worksheet

Set datasheet = ThisWorkbook.Worksheets("Sheet1")

'プログラム3｜datasheetの最終行を取得

Dim cmax1 As Long

cmax1 = datasheet.Range("A65536").End(xlUp).Row

'プログラム4｜マスタブックを開く

Dim masterfile As String

masterfile = ThisWorkbook.Path & "\Master.xlsx"

Dim master As Workbook

Set master = Workbooks.Open(Filename:=masterfile)

Dim mastersheet As Worksheet

Set mastersheet = master.Worksheets("Sheet1")

'プログラム5｜マスタファイルの最終行の取得

Dim cmax2 As Long

cmax2 = mastersheet.Range("A65536").End(xlUp).Row

'プログラム6｜変数設定

Dim product_code As String, master_code As String, product_name As String

Dim i As Long, j As Long, product_price As Long

'プログラム7｜DataブックのB列を取得

For i = 2 To cmax1

product_code = datasheet.Range("B" & i).Value

'プログラム8｜マスタブックのA列を取得

For j = 2 To cmax2

master_code = mastersheet.Range("A" & j).Value

'プログラム9｜DataブックのB列とマスタブックのA列をマッチング

If product_code = master_code Then

product_name = mastersheet.Range("B" & j).Value

product_price = mastersheet.Range("C" & j).Value

Exit For

End If

'プログラム10｜DataブックのE列とF列に出力

datasheet.Range("E" & i).Value = product_name

datasheet.Range("F" & i).Value = product_price

'プログラム11｜マスタファイルを閉じる

master.Close

'プログラム12｜プログラム終了

End Sub

以下で詳しく説明しています。

プログラム0｜変数宣言の指定

Option Explicit

1	Option Explicit

「Option Explicit」とは、変数宣言を強制するためのものです。

予期しないエラーを防止できるため「Option Explicit」を入力することを習慣化することを推奨しています。

詳しい説明は以下のページで紹介しています。

エクセルマクロVBAのOption Explicitとは？設定方法と宣言する理由

エクセルマクロVBAではOption Explicitを設定すると変数宣言を強制させることができます。 Option Explicitは入力しておくと、変数宣言が必須になります。そのせいで変数宣言していないとエラー表示が […]

プログラム1｜プログラム開始

Sub GetDataFromOtherBook()

1	Sub GetDataFromOtherBook()

VBAではプロシージャという単位でプログラムを呼び出します。

プロシージャの構文は下記となっています。

Sub プロシージャ名()
    '実行プログラム
End Sub

Sub プロシージャ名()

'実行プログラム

End Sub

「Sub」で始まり「End Sub」で終わります。

プロシージャに関連するmoduleの話については以下で説明しています。

エクセルマクロVBAの標準モジュールを解説｜名前変更/削除/呼び出し/実行/コピー/複数作成/表示など

エクセルマクロVBAでは、標準モジュールを使ってプログラムを作成します。しかしマクロを使い始めたばかりだと、標準モジュールの使い方が分からず困ってしまうことがあります。そこで、エクセルマクロVBA初心者が標準モジュー […]

プログラム2｜対象ブック(Dataブック)のシートを設定

    Dim datasheet As Worksheet
    Set datasheet = ThisWorkbook.Worksheets("Sheet1")

1 2	Dim datasheet As Worksheet Set datasheet = ThisWorkbook.Worksheets("Sheet1")

開いているブック(Data.xlsm)のSheet1をdatasheetとして扱います。

プログラム3｜datasheetの最終行を取得

    Dim cmax1 As Long
    cmax1 = datasheet.Range("A65536").End(xlUp).Row

1 2	Dim cmax1 As Long cmax1 = datasheet.Range("A65536").End(xlUp).Row

セルA65536から数えて、セルA21が値が入っている最初のセルなので、「cmax1=21」となります。

Debug.Printでの検証結果

Dim cmax1 As Long
cmax1 = datasheet.Range("A65536").End(xlUp).Row

>>>21

Dim cmax1 As Long

cmax1 = datasheet.Range("A65536").End(xlUp).Row

>>>21

cmax1はプログラム7で使用します。

プログラム4｜マスタブックを開く

    Dim masterfile As String
    masterfile = ThisWorkbook.Path & "\Master.xlsx"
    
    Dim master As Workbook
    Set master = Workbooks.Open(Filename:=masterfile)
    
    Dim mastersheet As Worksheet
    Set mastersheet = master.Worksheets("Sheet1")

Dim masterfile As String

masterfile = ThisWorkbook.Path & "\Master.xlsx"

Dim master As Workbook

Set master = Workbooks.Open(Filename:=masterfile)

Dim mastersheet As Worksheet

Set mastersheet = master.Worksheets("Sheet1")

マスタブックである「Master.xlsx」を開き、そのブック内の「Sheet1」をmastersheetとして扱います。

以下で詳細を解説します。

マスタファイルのパスを取得

Dim masterfile As String
masterfile = ThisWorkbook.Path & “\Master.xlsx”

マスタファイルのパスをmasterfileとして取得します。

ここでは、マクロが保存されているブック(Data.xlsm)と同じフォルダに存在する「Master.xlsx」のパスをmasterfileとして取得します。

マスタファイルを開く

Dim master As Workbook
Set master = Workbooks.Open(Filename:=masterfile)

Openメソッドを使って、「Master.xlsx」を開きます。

開いた「Master.xlsx」をmasterという変数(Workbook型)として扱います。

マスタファイルのSheet1をmastersheetとして扱う

Dim mastersheet As Worksheet
Set mastersheet = master.Worksheets(“Sheet1”)

「Master.xlsx」の「Sheet1」をmastersheetとして扱います。

プログラム5｜マスタファイルの最終行の取得

    Dim cmax2 As Long
    cmax2 = mastersheet.Range("A65536").End(xlUp).Row

1 2	Dim cmax2 As Long cmax2 = mastersheet.Range("A65536").End(xlUp).Row

mastersheetのA列の最終行をcmax2として取得します。

セルA65536から数えて、セルA8が値が入っている最初のセルなので、「cmax2=8」となります。

Debug.Printでの検証結果

Dim cmax2 As Long
cmax2 = mastersheet.Range("A65536").End(xlUp).Row

>>>8

Dim cmax2 As Long

cmax2 = mastersheet.Range("A65536").End(xlUp).Row

>>>8

cmax2はプログラム8で使用します。

プログラム6｜変数設定

    Dim product_code As String, master_code As String, product_name As String
    Dim i As Long, j As Long, product_price As Long

1 2	Dim product_code As String, master_code As String, product_name As String Dim i As Long, j As Long, product_price As Long

プログラム7以降で使用する変数を設定します。

変数設定に関しては、以下のページで詳しく紹介しています。

VBAの変数とは？型や初期値からメリットや使う意義まで解説

VBAでは変数を使うとき、最初に変数を定義すると便利です。このページでは変数を定義する方法や、変数名の付け方などについて解説します。上記の動画で変数について解説していますので、イメージを掴みたい人はご覧ください。以 […]

プログラム7｜DataブックのB列を取得

    For i = 2 To cmax1
        product_code = datasheet.Range("B" & i).Value
        '(中略)
    Next

For i = 2 To cmax1

product_code = datasheet.Range("B" & i).Value

'(中略)

product_codeという変数に、datasheetのB列(B2~B21)の値を格納します。

ここではForNextを使って繰り返し処理を実行しています。

「For i = 2 to cmax1」は、「i =2,3,4,・・・, cmax1(21)」というようにiに1ずつ加算しながら、ForとNextの間の処理を繰り返し実行させることができます。

For Next文は以下で事例を交えながら解説しています。

マクロVBAのForNext文を12事例で解説｜Continue,抜ける,配列,ifなどの使い方

エクセルマクロVBAでは、ForNext文を頻繁に使います。実際、このFor next文を自在に使えると、マクロで出来ることの幅が広がります。このページではVBAでの自動化に必須であるForNext文について、事例を […]

さらに詳しく知りたい方は上記のリンクをご覧ください。

プログラム8｜マスタブックのA列を取得

        For j = 2 To cmax2
            master_code = mastersheet.Range("A" & j).Value
            
            'プログラム9｜DataブックのB列とマスタブックのA列をマッチング
            'If product_code = master_code Then
                'product_name = mastersheet.Range("B" & j).Value
                'product_price = mastersheet.Range("C" & j).Value
                'Exit For
            'End If
        Next

For j = 2 To cmax2

master_code = mastersheet.Range("A" & j).Value

'プログラム9｜DataブックのB列とマスタブックのA列をマッチング

'If product_code = master_code Then

'product_name = mastersheet.Range("B" & j).Value

'product_price = mastersheet.Range("C" & j).Value

'Exit For

'End If

マスタブック(mastersheet)のA列(A2~A8)の値をmaster_codeとして取得します。

ここではForNextのカウンターとして「j」を使用しています。プログラム7の「i」と混同しないように気を付けます。

もしここで「j」ではなく「i」を使ってしまうと、正しく処理されません。

プログラム9｜DataブックのB列とマスタブックのA列をマッチング

            If product_code = master_code Then
                product_name = mastersheet.Range("B" & j).Value
                product_price = mastersheet.Range("C" & j).Value
                Exit For
            End If

If product_code = master_code Then

product_name = mastersheet.Range("B" & j).Value

product_price = mastersheet.Range("C" & j).Value

Exit For

End If

product_code(プログラム7)とmaster_code(プログラム8)をマッチングしていきます。

product_code(プログラム7)とmaster_code(プログラム8)が一致していれば、master_codeの行に対応するproduct_name(B列)とproduct_price(C列)の値を取得します。

これでマスタ(Master.xlsx)の情報を取得し、データ(Data.xlsm)に製品情報を参照させます。

マッチングしたらExit ForでFor文を抜ける

Exit For

product_code(プログラム7)とmaster_code(プログラム8)をマッチングしていれば、B列とC列の値を取得後、Exit ForでFor文を抜ける処理を行います。

Exit Forによって、プログラム8で実行するFor文を途中で終了させることになります。

理由はMaster.xlsxのマスタ情報に重複がないから、一度マッチングすれば、その値が正しいからです。

余計な処理を増やさないようにするために、Exit ForでFor文を抜けます。

Exit Forで繰り返し処理を抜けるとプログラム10を実行します。

なおExit Forに関しては、以下で事例を交えながら解説しています。

エクセルVBAのForNext｜ExitForで繰り返し処理から抜ける(break)

For nextの繰り返し処理の途中で、繰り返し処理を終了したいときがあります。 VBA以外のプログラミングでは、breakという記述でループを途中終了できる場合があります。ここでは、fornextのような繰り返し処理 […]

プログラム10｜DataブックのE列とF列に出力

        datasheet.Range("E" & i).Value = product_name
        datasheet.Range("F" & i).Value = product_price

1 2	datasheet.Range("E" & i).Value = product_name datasheet.Range("F" & i).Value = product_price

プログラム9で取得したproduct_name(Master.xlsxのB列)とproduct_price(Master.xlsxのC列)をdatasheet(Data.xlsm)のE列とF列に書き出します。

これで、元ブックである「Data.xlsm」のシートに、参照ブックである「Master.xlsx」の値を出力することができました。

プログラム11｜マスタファイルを閉じる

    master.Close

1	master.Close

参照ファイルであるMaster.xlsxを閉じます。

プログラム4でmasterとしてブックを設定しているため、「master.Close」でファイルを閉じることが可能です。

なお上書き保存等は実施せずに閉じる設定にしています。

プログラム12｜プログラムの終了

End Sub

End Sub

プログラムを終了させます。

類似のVBAプログラムの解説

エクセル関数をVBAで作ってみる

エクセル関数をマクロで作る！10事例でVBAプログラムを解説

このページではエクセル関数をVBAを使ったら、「どのようなプログラムとなるのか？」について紹介していきます。実際に、以下のエクセル関数をVBAプログラムによって作成します。 VBAでエクセル関数を作ってみる・SUM関 […]

上記のページでは以下のエクセル関数について、VBAプログラムで作成した事例を紹介しています。

事例1｜SUM関数
事例2｜IF関数
事例3｜COUNTIF関数
事例4｜COUNTIFS関数
事例5｜SUMIF関数
事例6｜SUMIFS関数
事例7｜VLOOKUP関数
事例8｜SUMPRODUCT関数
事例9｜SUMIFS関数(日付の比較)
事例10｜COUNTIF関数(ワイルドカード)

興味がある人はご覧ください。

転記マクロ

エクセルマクロVBAのデータ転記15事例｜シート別、ブック別、蓄積の実例を紹介

エクセルマクロVBAの作業効率化の代表的なものとして、転記マクロがあります。マクロを上手く活用すれば、データ一覧をもとに、条件に合致する情報のみをシート別やブック別に転記していくことが可能です。またVBAプログラムを […]

上記のページでは、以下のマクロについてプログラム解説をしています。

事例1. エクセルマクロVBAでデータ項目別にシートコピーし自動転記

事例2. エクセルマクロVBAでデータを月別にシートを分ける

事例3. マクロVBAでエクセルの不要シート(複数)を一斉削除

事例4. VBAでエクセル内の複数シートのデータを一つにまとめる(集約)

事例5. VBAでエクセルデータを項目毎に別ファイルへ転記｜マクロテンプレート

事例6. VBAでフォルダ内の不要ファイルをまとめて削除｜マクロをダウンロード可

事例7. VBAでフォルダ内の全てのエクセルデータを一つにまとめる(集約)

事例8. VBAで別ブックから転記(マスタファイルを参照して元ブックに出力)

事例9. エクセルVBAで請求書PDF作成｜複数の取引先別に自動転記(マクロテンプレートあり)

事例10. マクロVBA｜エクセル一覧表で複数条件に合致したデータを抽出して表示

事例11. エクセルマクロVBA｜特定文字列(複数可)を含む行だけ別シート抽出

事例12. エクセルマクロVBA｜特定の文字列を含む列のみ別シート抽出

事例13. エクセルマクロVBA｜セルにデータ入力して別シートに自動で蓄積させる

事例14. VBAでWord議事録メモを作成して最前面表示！ファイルコピーと一覧化で整理整頓

事例15. VBAでエクセル申請フォームからデータ登録・蓄積の仕組みを作る

Excel VBAについて詳しく理解したいなら

VBAを活用すると、仕事を効率化できる幅を広げることができます。

たとえば私が実際にVBAを活用して効率化してきた作業は以下の記事で紹介しています。

興味がある人は以下の記事もご覧ください。

エクセルマクロVBA「できること」は何？100事例で自動化を解説

エクセルマクロVBAを使えば面倒な仕事を自動化して、作業をエクセルに処理させることができます。しかし、エクセルマクロと聞いてもそもそも何か分からない人もいたり、どのようなことができるのか知らなかったりする人は多いです。 […]

動画でも解説しています。

エクセルマクロVBAで出来ることを15の事例で紹介｜日常業務をラクにするヒントを見つけよう！

(音声が小さいので、ボリュームを上げてご覧いただければと思います)

VBAの勉強方法

私はプログラミング初心者からVBAを勉強を始めて少しずつレベルアップしていきました。

少しずつレベルアップしながら、難しい内容に挑戦していくと効率的に学ぶことができます。

エクセルマクロVBAの練習問題・動画90｜初心者や再学習者向けの演習をサポート

エクセルマクロ入門者・初心者の人向けにVBA学習用の練習問題を紹介します。このページの概要は以下です。・VBAの練習問題や動画を紹介・学習者のレベルに応じた勉強の方針を紹介この動画では以下のことを目指して動画を作 […]

上記のリンクでは、VBA勉強に役立つ内容を紹介しています。

興味がある人はご覧ください。

エクセルマクロVBAで別ブックから転記｜vlookup関数の代用で元ブックに出力

VBAで別ブックから転記(マスタファイルを参照して元ブックに出力)

VBA入りのエクセルファイルをダウンロード

VBA作成前の準備

VBAのプログラムソース解説

プログラム0｜変数宣言の指定

プログラム1｜プログラム開始

プログラム2｜対象ブック(Dataブック)のシートを設定

プログラム3｜datasheetの最終行を取得

Debug.Printでの検証結果

プログラム4｜マスタブックを開く

マスタファイルのパスを取得

マスタファイルを開く

マスタファイルのSheet1をmastersheetとして扱う

プログラム5｜マスタファイルの最終行の取得

Debug.Printでの検証結果

プログラム6｜変数設定

プログラム7｜DataブックのB列を取得

プログラム8｜マスタブックのA列を取得

プログラム9｜DataブックのB列とマスタブックのA列をマッチング

マッチングしたらExit ForでFor文を抜ける

プログラム10｜DataブックのE列とF列に出力

プログラム11｜マスタファイルを閉じる

プログラム12｜プログラムの終了

類似のVBAプログラムの解説

エクセル関数をVBAで作ってみる

転記マクロ

Excel VBAについて詳しく理解したいなら

VBAの勉強方法

人気記事

サイト運営情報

パソコンスキル

仕事術

VBAで別ブックから転記(マスタファイルを参照して元ブックに出力)

VBA入りのエクセルファイルをダウンロード

VBA作成前の準備

VBAのプログラムソース解説

プログラム0｜変数宣言の指定

プログラム1｜プログラム開始

プログラム2｜対象ブック(Dataブック)のシートを設定

プログラム3｜datasheetの最終行を取得

Debug.Printでの検証結果

プログラム4｜マスタブックを開く

マスタファイルのパスを取得

マスタファイルを開く

マスタファイルのSheet1をmastersheetとして扱う

プログラム5｜マスタファイルの最終行の取得

Debug.Printでの検証結果

プログラム6｜変数設定

プログラム7｜DataブックのB列を取得

プログラム8｜マスタブックのA列を取得

プログラム9｜DataブックのB列とマスタブックのA列をマッチング

マッチングしたらExit ForでFor文を抜ける

プログラム10｜DataブックのE列とF列に出力

プログラム11｜マスタファイルを閉じる

プログラム12｜プログラムの終了

類似のVBAプログラムの解説

エクセル関数をVBAで作ってみる

転記マクロ

Excel VBAについて詳しく理解したいなら

VBAの勉強方法

Footer

人気記事

サイト運営情報

パソコンスキル

仕事術