【初心者向けエクセルVBA】取引先別に請求書を作成するマクロを作る

photo credit: MellieRene4 145 – Crunching the Numbers via photopin (license)

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

初心者向けのエクセルVBAのシリーズとして、請求書を作成するマクロの作り方をお伝えしています。

前回の記事はこちらです。

【初心者向けエクセルVBA】ワークブックを別名で保存して閉じる方法

初心者向けエクセル VBAのシリーズとして、請求書を作成するマクロの作り方についてお伝えしています。今回は、ワークブックをSaveAsメソッドで別名で保存し、Closeメソッドで閉じる方法をお伝えします。

ブックを別名で保存する方法と閉じる方法についてお伝えしました。

さて、しばらく続いてきたこのシリーズも、いよいよ今回で完成となります。

これまでお伝えしてきたテクニックを元に、取引先ごとに別のブックで請求書を作成するマクロを完成させていきます。

なお、以下のYouTube動画で各ステートメントの解説も加えていますので、合わせてご活用くださいませ。

では、行ってみましょう！

請求書作成マクロの要件
1. マクロを記述する請求データ.xlsm
2. 請求書ひな形.xlsx
請求書を作成するプログラム
1. 実行結果
まとめ
連載目次：データ一覧から請求書を自動で作る

請求書作成マクロの要件

ではまず、今回作成するマクロの全体像について説明をします。

実際のファイルは以下からダウンロードください。

※請求データ.xlsxにはマクロが含まれておりません。
※ファイル名がアルファベットになっていますので、それぞれ「請求データ.xlsm」「請求書ひな形.xlsx」にリネームしてお使いください。

マクロを記述する請求データ.xlsm

二つの関連するブックがありまして、一つはマクロを実際に記述する「請求データ.xlsm」です。

シートの構成としては、まず請求するデータをリストしている、「請求データ」シート
（wsData）があります。

このシートから、該当の年月のデータについて、取引先ごとの請求書に抽出して転記をしていきます。

次に、「取引先マスタ」シート（wsClient）です。

ここにリストされている取引先の分だけ、請求書のブックを作成します。

請求書ひな形.xlsx

次に、請求書のひな形になる「請求書ひな形.xlsx」です。

こちらにはマクロを仕込まずに、前述の「請求データ.xlsm」のマクロから取引先ごとに開き、処理をし、別名で保存することで、個別のブックを作成しています。

請求書を作成するプログラム

これらを使って、取引先別の請求書を作成するマクロを作りました。

プログラムはこちらです。

Sub 請求書作成()

Dim rowsData As Long, rowsClient As Long
rowsData = wsData.Cells(Rows.Count, 1).End(xlUp).Row '請求データの最終行数
rowsClient = wsClient.Cells(Rows.Count, 1).End(xlUp).Row '取引先マスタの最終行数

'年月を入力ダイアログで入力
Dim dayCutoff As Date
dayCutoff = Application.InputBox("年月を入力してください", "対象年月を入力", Format(Date, "yyyy/mm"))

Dim n As Long
For n = 2 To rowsClient
            
    Dim client As String
    client = wsClient.Cells(n, 1).Value
    
    'ひな形ブックを開きそのシートとともにセットする
    Dim wb As Workbook, ws As Worksheet
    Set wb = Workbooks.Open(ThisWorkbook.Path & "\請求書ひな形.xlsx")
    Set ws = wb.Worksheets(1)
    
    '該当の取引先かつ年月のデータを転記する
    Dim i As Long, k As Long
    k = 21
    For i = 2 To rowsData
        
        If wsData.Cells(i, 2).Value = client Then
            Dim deliDate As Date
            deliDate = wsData.Cells(i, 1).Value
        
            If Year(deliDate) = Year(dayCutoff) And Month(deliDate) = Month(dayCutoff) Then
                wsData.Range(wsData.Cells(i, 3), wsData.Cells(i, 5)).Copy ws.Cells(k, 1)
                k = k + 1
            End If
        End If
    Next i

    'その他の転記と行の非表示
    ws.Rows(k & ":50").Hidden = True  'データがない行を隠す
    
    ws.Range("A18").Value = "ご請求金額：" & Format(ws.Range("D54").Value, "#,##0") & " 円"
    ws.Range("A3").Value = client & "御中"  '取引先名
    ws.Range("A5").Value = "〒" & wsClient.Cells(n, 2).Value '郵便番号
    ws.Range("A6").Value = wsClient.Cells(n, 3).Value '住所１
    ws.Range("A7").Value = wsClient.Cells(n, 4).Value '住所２
    ws.Range("D15").Value = DateSerial(Year(dayCutoff), Month(dayCutoff) + 1, 0) '請求日
    ws.Range("D16").Value = DateSerial(Year(dayCutoff), Month(dayCutoff) + 2, 0) 'お支払期限
    
    'ファイル名を生成して保存して閉じる
    Dim fileName As String
    fileName = ThisWorkbook.Path & "\" & Format(dayCutoff, "yyyymm") & "請求書_" & client & ".xlsx"
    wb.SaveAs fileName
    wb.Close
       
Next n
End Sub

ちょっと長いですがこれまでお伝えしてきたテクニックを見直せば多分大丈夫です。

全体の流れとしては以下のようになっています。

「請求データ」シート、「取引先マスタ」シートの最終行数を取得する
請求書を作成する対象の年月を入力ダイアログを表示してユーザーに入力してもらう
「取引先マスタ」シートの最終行まで繰り返し
- 「請求書ひな形.xlsx」を開き、ブックとワークシートをオブジェクト変数にセット
- 「請求データ」シートの最終行まで繰り返し
- 現在のデータの取引先が、対象の取引先であれば
  - 現在のデータの納品日が対象の年月であれば
  - 現在のデータを「請求書ひな形.xlsx」に転記
- 「請求書ひな形.xlsx」のデータがない行を隠す
- 「請求書ひな形.xlsx」にご請求金額、取引先情報、請求日、お支払い期限などを転記
- 保存するファイル名を生成
- ブックを別名で保存
- ブックを閉じる