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


invoice-calculate

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

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

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

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

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

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

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

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

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

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

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

請求書作成マクロの要件

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

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


※請求データ.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 '住所1
    ws.Range("A7").Value = wsClient.Cells(n, 4).Value '住所2
    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」にご請求金額、取引先情報、請求日、お支払い期限などを転記
    • 保存するファイル名を生成
    • ブックを別名で保存
    • ブックを閉じる

実行結果

これマクロ実行して、入力ダイアログに「2018/01」とすると、フォルダ内には以下のように取引先別のブックが作成されます。

エクセルVBAでフォルダ内に生成された取引先別のブック

例えば、ABC株式会社の請求書を開くと、以下のように該当のデータが転記された請求書は作成されてることがわかります。

エクセルVBAで取引先別に作られた請求書ブック

まとめ

以上、エクセルVBAで取引先別の請求書を別ブックとして作成するマクロの作り方についてお伝えしました。

今回紹介したコードは、コピペだけして使うよりも、一行一行の意味をちゃんと理解した上で、メンテナンスやカスタマイズができる状態が理想です。

もし、引っかかるところがあれば、これまでのシリーズの記事で復習してくださいね。

では、また別の便利なツールの作り方についてお伝えしたいと思いますので、どうぞ他の記事もご活用くださいね。

連載目次:データ一覧から請求書を自動で作る

お仕事において特定のデータ一覧から必要な情報を抽出するということは頻繁にありうると思います。ここではデータ一覧から請求書を作るということを目標に、実務で使えるスキルをまっすぐに身に着けることを目的としています。
  1. 【初心者向けエクセルVBA】データ一覧から請求書を自動で作る
  2. 【初心者向けエクセルVBA】ワークシートをオブジェクト名で取り扱う方法
  3. 【初心者向けエクセルVBA】For~Next文で簡潔にプログラムを書く
  4. 【初心者向けエクセルVBA】行の数をカウントする&不要な行を隠す
  5. 【初心者向けエクセルVBA】文字列の連結&Format関数での書式変更
  6. 【初心者向けエクセルVBA】セル範囲を一気にまとめてコピーする方法
  7. 【初心者向けエクセルVBA】ワークシートのデータのある範囲だけをピッタリ取得する方法
  8. 【初心者向けエクセルVBA】セル範囲の平行移動をする方法・リサイズをする方法
  9. 【初心者向けエクセルVBA】日付データから年・月・日を取り出す
  10. 【初心者向けエクセルVBA】If~Thenを使った条件分岐の超入門
  11. 【初心者向けエクセルVBA】For~Next文でセル範囲を一行ずつ移動させる
  12. 【初心者向けエクセルVBA】セル範囲のクリア~ClearContentsメソッドとClearメソッド
  13. 【初心者向けエクセルVBA】ワークシート・セルを選択する方法の色々について
  14. 【初心者向けエクセルVBA】入力ダイアログを表示するInputBoxメソッドの使い方
  15. 【初心者向けエクセルVBA】日付データから月末日と翌月末日を自動算出する
  16. 【初心者向けエクセルVBA】ワークシートをコピーする方法とそのシート名を変更する方法
  17. 【初心者向けエクセルVBA】オブジェクトを変数にセットして取り扱う方法
  18. 【初心者向けエクセルVBA】Openメソッドで新たなブックを開く方法
  19. 【初心者向けエクセルVBA】現在マクロを書いているブックのフォルダパスを取得する
  20. 【初心者向けエクセルVBA】開いたブックとそのワークシートをオブジェクト変数にセットする
  21. 【初心者向けエクセルVBA】ワークブックを別名で保存して閉じる方法
  22. 【初心者向けエクセルVBA】取引先別に請求書を作成するマクロを作る

  投稿者プロフィール

タカハシノリアキ株式会社プランノーツ 代表取締役
株式会社プランノーツ代表、コミュニティ「ノンプロ研」主宰。1976年こどもの日生まれ。東京板橋区在住。「ITで日本の『働く』の価値を上げる!」をテーマに、VBA&GASの開発、講師、執筆などをしております。→詳しいプロフィールはコチラ
★ご依頼・ご相談はお気軽にどうぞ!→お問い合わせはコチラ
★フォロー頂ければ嬉しいです。

タイトルとURLをコピーしました