エクセルVBAでテーブル上の請求データを新規ブックに転記するマクロの作り方

photo credit: marcoverch Nahaufnahme der Spiralbindung eines Notizbuchs via photopin (license)

みなさん、こんにちは！
タカハシ(@ntakahashi0505)です。

エクセルVBAでテーブル機能を活用した請求書作成マクロの作成方法についてお伝えします。

前回の記事はコチラ。

エクセルVBAでテーブルを走査して取引先ごとのブックを作るマクロ

請求書の作成マクロを題材に、エクセルVBAでテーブルを操作する方法をお伝えしています。今回は、テーブルを走査して取引先ごとにブックを作成して、請求書のひな形シートをコピーするマクロを紹介します。

テーブルを走査して取引先ごとのブックを作るところまでマクロを作成しました。

今回は、その続きですね。

作成したブックのひな形テーブルに追加した新規データ行に請求データを転記する処理を作成していきます。

エクセルVBAでテーブル上の請求データを新規ブックに転記するマクロの作り方、行ってみましょう！

前回のおさらい
テーブルのデータ行を走査して値を転記する
1. テーブルの各行を順番どおりにループする
2. テーブルの値を別のテーブルの新規データ行に転記する
取引先ごとのブックに請求データを転記するマクロ
まとめ
連載目次：エクセルVBAでテーブルを活用した請求書マクロを作る

前回のおさらい

まずは、これまでのおさらいから進めていきます。

「請求データ.xlsm」ブックには、「請求データ」および「取引先マスタ」という２つのテーブルから構成されている「データ」シート（オブジェクト名は「wsData」）と

取引先ごとの請求書のひな形となる「ひな形」シート（オブジェクト名は「wsTemplate」）で構成されています。

それで、前回作成したマクロはコチラです。

Sub 請求書作成()

Dim tbData As ListObject: Set tbData = wsData.ListObjects("請求データ")
Dim tbClient As ListObject: Set tbClient = wsData.ListObjects("取引先マスタ")
Dim rowClient As ListRow

For Each rowClient In tbClient.ListRows
    Dim client As String: client = rowClient.Range(1).Value
    wsTemplate.Copy
    With ActiveSheet
        .Name = client
        Dim tbTemplate As ListObject: Set tbTemplate = .ListObjects("ひな形データ")
        '2. 「請求データ」テーブルの行を走査して転記処理などをする
        '3. 請求金額、請求先の社名や住所などの情報を入力する
    End With
    '4. ファイル名を決めて保存する
Next rowClient

End Sub

「取引先マスタ」テーブルの取引先ごとに、「ひな形」シートをコピーした新規ブックを作成するものです。

あとは以下の部分を作っていけば、請求書作成マクロが完成します。

2.それぞれの新規ブックに、「請求データ」のうち該当の取引先の分のデータを転記する
3.請求書として必要な請求金額、請求先の情報
4.ファイル名を決めて保存する

今回は「2」つまり、ブックに該当のデータを転記していく処理を作成していきます。

テーブルのデータ行を走査して値を転記する

上記のコードの「’2. 「請求データ」テーブルの行を走査して転記処理などをする」の部分ですが、以下の処理を挿入します。

Dim i As Long
For i = 1 To tbData.ListRows.Count
    Dim rowData As ListRow: Set rowData = tbData.ListRows(i)
    If rowData.Range(2).Value = client Then
        With tbTemplate.ListRows.Add
            .Range(1).Value = rowData.Range(3).Value
            .Range(2).Value = rowData.Range(4).Value
            .Range(3).Value = rowData.Range(5).Value
        End With
    End If
Next i

解説していきましょう。

テーブルの各行を順番どおりにループする

まず、2~11行目のFor文ですが、tbDataつまり「請求データ」テーブルのListRowsつまり各行についてのループです。

これは、「取引先マスタ」テーブルのループには、順番を考慮しなくて良いのでFor Each文を使用していましたが、「請求データ」テーブルには一応順番が大事かも知れないので、For文にしました。

そのあたりは、以下の記事を再度復習していただければと思います。

エクセルVBAでテーブルのデータ行についてループ処理を行う方法

請求書マクロを題材にエクセルVBAでテーブル操作する方法をお伝えしています。今回は、ListRowsオブジェクトがポイント。テーブルのデータ行についてループ処理を行う方法についてお伝えしていきます。

テーブルの値を別のテーブルの新規データ行に転記する

現在のデータ行つまりrowDataの取引先が、該当の取引先つまりclientのものであれば、「ひな形」テーブルに新規データ行を追加し、データを転記していきます。

新規データ行を追加しているのが5行目。ListRowsコレクションのAddメソッドです。

また、品目、単価、数量といった各データを転記をしているのが6~8行目です。ListRowのセルの位置を取得するにはRangeプロパティでしたね。

このあたりは、以下の記事で詳しく解説をしています。

エクセルVBAでテーブルのデータ行を追加して値を入力をする方法

請求書作成マクロを題材としてエクセルVBAでテーブルを操作する方法についてお伝えするシリーズです。今回は、テーブルのデータ行を追加して値を入力をする、Addメソッドの使い方をお伝えしていきますね。

取引先ごとのブックに請求データを転記するマクロ

以上を組み合わせた、まとめのコードはコチラです。

Sub 請求書作成()

Dim tbData As ListObject: Set tbData = wsData.ListObjects("請求データ")
Dim tbClient As ListObject: Set tbClient = wsData.ListObjects("取引先マスタ")
Dim rowClient As ListRow

For Each rowClient In tbClient.ListRows
    Dim client As String: client = rowClient.Range(1).Value
    wsTemplate.Copy
    
    Dim wb As Workbook: Set wb = ActiveWorkbook
    With ActiveSheet
        .Name = client
        Dim tbTemplate As ListObject: Set tbTemplate = .ListObjects("ひな形データ")
        
        Dim i As Long
        For i = 1 To tbData.ListRows.Count
            Dim rowData As ListRow: Set rowData = tbData.ListRows(i)
            If rowData.Range(2).Value = client Then
                With tbTemplate.ListRows.Add
                    .Range(1).Value = rowData.Range(3).Value
                    .Range(2).Value = rowData.Range(4).Value
                    .Range(3).Value = rowData.Range(5).Value
                End With
            End If
        Next i

        '3. 請求金額、請求先の社名や住所などの情報を入力する
    End With
    '4. ファイル名を決めて保存する

End Sub

実行をすると、以下のように各取引先ごとのブックの「ひな形」テーブルに、それぞれ対象となるデータ行の値が転記されていることを確認できます。