Excelマクロで請求書作成→PDF保存。複数データ対応完全版。

Excelマクロで請求書をつくっています。
実際に私がつかっているのは、複数のデータがあっても転記できるバージョンです。
そのバージョンを解説します。

Excelマクロで請求書をつくる流れ

Excelファイルの構成

Excelマクロで請求書をつくるには、3枚のシートを使います。
まず、請求書データ（data）

請求書のひな型（master）

顧客データ（住所、担当者名等）

このうち、請求書データには、VLOOKUP関数を使って、シート「顧客データ」から名称を連動させています。

また、会計データも作成しているので、取り込みにも使えます。

マクロで転記する部分

マクロでやるのは、請求書データから、請求書のひな型への転記です。
人間がやるならコピーして貼り付けてコピーして貼り付けてを繰り返さなければいけません。

転記しているのは、
・請求書ナンバー
・顧客コード
・日付
・支払期限

・請求データ（項目、内容、金額）

です。

すべてのデータをマクロで転記することもできますが、Excel側でやったほうが、つくりやすくメンテナンスしやすい部分もあるため、マクロとExcelを組み合わせています。

日付は、ひな型に書式を設定しておき（Ctrl＋1）、「2018年5月1日」と表示されるようにしておきましょう。

Excelで計算する部分

小計、消費税の計算、合計は、Excelで計算しています。

ExcelのVLOOKUP関数で連動する部分

 

顧客コードは、請求書の印刷範囲外に転記し、そのコードをVLOOKUPで読み取っています。

こういった数式です。

コードが1なら、「株式会社タイムコンサルティング」のデータを連動して表示します。

こういうしくみをつくっておき、マクロ部分をつくっていく流れです。

請求書作成マクロ（データが1行）

まず、請求書データが1行の場合を考えてみます。

マクロはこういったものです。

 

Sub invoice1()

 

'選択した部分で繰り返す
Dim i As Long
For i = Selection(1).Row To Selection(Selection.count).Row

'選択したデータのうち最初の行なら新規ブックへひな形をコピー
　If i = Selection(1).Row Then
　　ThisWorkbook.Worksheets("master").Copy
　Else
'そうでなかったら同じブックでシートをコピー
　　ActiveSheet.Copy after:=Worksheets(Worksheets.count)
　End If


'データの転記
　Range("e1").Value = ThisWorkbook.Worksheets("data").Range("a" & i).Value
　Range("f1").Value = ThisWorkbook.Worksheets("data").Range("b" & i).Value 'コード
　Range("e5").Value = ThisWorkbook.Worksheets("data").Range("d" & i).Value '発行日
　Range("e8").Value = ThisWorkbook.Worksheets("data").Range("e" & i).Value '支払期限
　Range("b20").Value = ThisWorkbook.Worksheets("data").Range("f" & i).Value '項目
　Range("c20").Value = ThisWorkbook.Worksheets("data").Range("g" & i).Value '内容
　Range("e20").Value = ThisWorkbook.Worksheets("data").Range("h" & i).Value '金額
　ActiveSheet.name = ThisWorkbook.Worksheets("data").Range("a" & i).Value & ThisWorkbook.Worksheets("data").Range("c" & i).Value  'シート名


Next

'■請求書PDFの作成

Dim w

'それぞれのシートをPDFとして保存
For Each w In Worksheets
　w.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\" & w.name & "様 請求書" & ".pdf"
Next

'作成したExcelを閉じる

ActiveWorkbook.Close savechanges:=False


End Sub

順を追ってみていきます。

forは、繰り返しを指示する構文です。
forからNextまでを繰り返します。

Dim i
For i = 1 to 10

Next
と通常書きます。
iというのは箱のようなもので、1to10なら、1から10までをiに入れて繰り返すという意味です。
（変数といいます）

今回の場合、少し工夫しています。

請求書をつくるときは、〇月という指定もできますし、シートにあるすべてのデータという指定もできますが、「選択したデータ」で請求書をつくるというしくみにしておいたほうが、便利です。

請求書をつくるのは原則として月に1回なのですが、そうでない場合もありえます。
つくりたい請求書を選択して、マクロを実行したほうが使いやすくなるわけです。

For i = Selection(1).Row To Selection(Selection.count).Row
なので、

「Selection(1).Row」（選択した1行目）から、「 Selection(Selection.count).Row」（選択した最終行）まで繰り返します。

Selection(Selection.count).RowのSelection.countで何行選択したか、数えてくれるのです。
この場合は、3行なので、Selection.countは3。2行目から3つ、つまり4行までのデータを指定していることになります。

 

次にやるのは、ひな型のコピーです。
もし、Selection(1).Row（選択した1行目）のときは、新規ブックへひな形をコピーしています。

ThisWorkbook.Worksheets(“master”).Copy

は、このブック（マクロが入っているブック）のシート「master」を新規ブックにコピーするという意味です。
通常はコピー先を指定するのですが、省略すると、新規ブックへコピーできます。

選択した最初の行じゃなかったら、先ほどコピーしたブックで、ひな形をコピーします。

IFは、
If　〇〇　Then

△△
Else
■■
End　If

で、もし〇〇だったら、△△、そうでなかったら■■という指示です。

このように新しいブックにシートが増えていくわけです。
このブックは最終的には保存せずに閉じます。
PDFで保存しておけば十分なので、Excelのこのブックは必要ありません。

 

ひな形をコピーしたら、データをそれぞれ転記します。

たとえば、
Range(“e1”).Value = ThisWorkbook.Worksheets(“data”).Range(“a” & i).Value
では、2行目のデータ（i＝2）なら、ひな形をコピーしたシートのセルE1に、このブックのシート「data」のセルA2の値（79）を入れるというしくみです。

なお、こういった部分は、もっと短い文にすることもできるのですが、かえって混乱することもあるので（特にはじめたばかりの方は）、あえて長めにするようにしています。

 

転記が終わったら、PDFで保存しましょう。

2行目から4行目を指定した場合、このようなファイルになっていますので、それぞれのシートをPDFに保存していきます。

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

すべてのシート、それぞれで処理をするなら、
Dim w
For Each w　in Worksheets
〇〇
Next

を使います。
wはなんでも大丈夫です。

 

 

 

 

 

PDFにし、ファイル名を指定して保存し、このブックを保存しているフォルダに保存されます）最後に、請求書のExcelファイル（新しいブック）は閉じます。

 

このように、請求書PDFが完成しました。
データが何万行あっても同じ手間です。
人間が作業することはありません。

 

私は最初に勤めた1997年から、こういうしくみを使っていました。
もちろん、職場にはこっそり。
こんなに早く楽に終わっているとばれたら、また仕事が降ってきますし。
ただ、その分の時間は、さらなる効率化の勉強をしていたので、職場には還元してきたつもりです。

 

請求書作成マクロ（データが複数行）

データが1行ずつならさきほどのマクロで大丈夫なのですが、このように、同じ取引先で複数項目がある場合は、対応できません。
そこで、プログラムを少し変えていきます。

Sub invoice()

'データが複数行ある場合の貼り付け位置
Dim n
n = 21

'選択した部分で繰り返す
Dim i As Long
For i = Selection(1).Row To Selection(Selection.count).Row

'もし、上のデータと請求書番号が違う場合
　If ThisWorkbook.Worksheets("data").Range("a" & i).Value <> ThisWorkbook.Worksheets("data").Range("a" & i - 1).Value Then

'選択したデータのうち最初の行なら新規ブックへひな形をコピー
　　If i = Selection(1).Row Then
　　　ThisWorkbook.Worksheets("master").Copy
　　Else
'そうでなかったら同じブックでシートをコピー
　　　ActiveSheet.Copy after:=Worksheets(Worksheets.count)
　　　Range("b20", "e37").ClearContents

'貼り付け位置リセット
　　　n = 21

　　End If


'データの転記
　　Range("e1").Value = ThisWorkbook.Worksheets("data").Range("a" & i).Value
　　Range("f1").Value = ThisWorkbook.Worksheets("data").Range("b" & i).Value 'コード
　　Range("e5").Value = ThisWorkbook.Worksheets("data").Range("d" & i).Value '発行日
　　Range("e8").Value = ThisWorkbook.Worksheets("data").Range("e" & i).Value '支払期限
　　Range("b20").Value = ThisWorkbook.Worksheets("data").Range("f" & i).Value '項目
　　Range("c20").Value = ThisWorkbook.Worksheets("data").Range("g" & i).Value '内容
　　Range("e20").Value = ThisWorkbook.Worksheets("data").Range("h" & i).Value '金額
　　ActiveSheet.name = ThisWorkbook.Worksheets("data").Range("a" & i).Value & ThisWorkbook.Worksheets("data").Range("c" & i).Value  'シート名


'上のデータと請求書番号が同じ場合
　Else
　　Range("b" & n).Value = ThisWorkbook.Worksheets("data").Range("f" & i).Value '項目
　　Range("c" & n).Value = ThisWorkbook.Worksheets("data").Range("g" & i).Value '内容
　　Range("e" & n).Value = ThisWorkbook.Worksheets("data").Range("h" & i).Value '金額
　　n = n + 1
　End If



Next

'■請求書PDFの作成

Dim w

'それぞれのシートをPDFとして保存
For Each w In Worksheets
　w.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\" & w.name & "様 請求書" & ".pdf"
Next

'作成したExcelを閉じる

ActiveWorkbook.Close savechanges:=False


End Sub

複数項目がある場合は、このように同じシートに転記してほしいものです。

そこで、IFを追加しました。

 

最初に、「A列の1つ上のセルと違うときは」という条件にしています。
If ThisWorkbook.Worksheets(“data”).Range(“a” & i).Value <> ThisWorkbook.Worksheets(“data”).Range(“a” & i – 1).Value Then

で、

ThisWorkbook.Worksheets(“data”).Range(“a” & i).Value

と
ThisWorkbook.Worksheets(“data”).Range(“a” & i – 1).Value
を比較しているわけです。

iが5のとき、
IThisWorkbook.Worksheets(“data”).Range(“a” & i).Value は、セルA5、

ThisWorkbook.Worksheets(“data”).Range(“a” & i – 1).Value は、セルA4のことになり、A4とA5が一緒かどうかを判定します。

違っていれば、最初のブログラムのように、20行目に転記し、

そうでなければ、21行目以降に転記していきます。
ここで、21と固定してしまうと、3つ以上の項目に対応できません。
そのため、nという変数をつかっています。

プログラムの最初のほうで、nという変数を21と設定します。
（設定しないと、0になります）
複数の項目がある場合、21行目に貼り付けるからです。

 

 

 

 

 

 

 

項目、内容、金額を転記した後、

n=n+1
で、21に1加えて22にします。
さらに項目がある場合は、22行目、23行目‥‥とずらして転記できるのです。

 

 

 

ただ、次の取引先になったときは、いったん、データをクリアし、貼り付け位置も21にリセットします。
F8キーで1行ずつ実行してみていただければ。

 

 

 

 

 

 

 

 

 

項目が2つならこうなりますし、

 

3つならこうなります。

マクロが入っていないテンプレートExcelファイルはこちらです。

EX-ITサンプル　請求書マクロテンプレート.xlsx

 

---

【編集後記】

昨日は、午前中に『十人十色の「ひとり税理士」という生き方（井ノ上版）』セミナーを開催。
参加者の方とランチし、午後は、値付けセミナーを開催し、懇親会。
終日話す、楽しい日でした。

『十人十色の「ひとり税理士」という生き方（井ノ上版）』セミナー音声は、メルマガ「税理士進化論」にて行っているキャンペーン特典です。
（キャンペーン終了後は販売予定）

値付けセミナーは、販売開始しました。
音声 値付け 8つのポイント – 株式会社タイムコンサルティング

【昨日の1日1新】
※詳細は→「1日1新」

『十人十色の「ひとり税理士」という生き方』 セミナー開催
値付けセミナー開催
ボナンザ 肉バル

【昨日の娘日記】
昨日、出かけようとするとギャン泣き。
よくよく考えると毎日一緒に出かけているので、「あれ？」と思われたのでしょう。
6月は出張が多いので、心苦しいです。