VBAで仕訳データのシート間転記を簡単にする
私が書いた本「会計ソフトのすき間を埋める経理のExcel仕事術」では、エクセルで仕訳データのインポートシートを作る方法を解説しています。
書籍では、エクセルの基本的な関数・機能だけを使ってシートを作っているのですが、少しVBAでマクロを作ることで、さらに便利に使うことができます。
今回は、VBAを使って、仕訳帳データの「入力」シートからCSVデータを出力する「CSV」シートにデータを転記する仕組みを作ってみようと思います。
この記事の目次
今回使うデータのダウンロード
今回使うデータは、技術評論社のホームページからダウンロードできます。
会計ソフトのすき間を埋める 経理のExcel仕事術のサポートページからファイルをダウンロードしてください。
今回は、その中に含まれている「¥Chapter04¥作成後¥4-5仕訳入力用フォーマット_final.xlsx」を使います。
関数で転記をする問題点
会計ソフトのすき間を埋める経理のExcel仕事術のChapter4では、では、「入力」シートの4行目~1003行目に入力したデータを、数式を使って「CSV」シートに転記をしました。
ただ、数式を使って転記をする場合、「CSV」シートの4行目~1003行目に数式を入力しているため、次のように面倒な点も出てきてしまいます。
- 1003行目までしか転記されない
-
たとえば、1004行目以降に仕訳データを入れても、「CSV」シートには1003行目までしか数式が入力されていないので、「CSV」シートに仕訳データが転記されません。
- 「入力」シートで行の挿入・削除をすると数式が壊れる
-
「CSV」シートに値を転記するために、「入力」シートを参照する数式を「CSV」シートに入れています。そのため、「入力」シートで、行の挿入・削除をすると「CSV」シートの数式が壊れてしまいます。
たとえば、「入力」シートの8行目を削除すると、「CSV」シートの8行目の数式が壊れてしまいます。
↓「入力」シートの8行目を削除
↓「CSV」シートの8行目の数式が壊れる
そこで、VBAを使って、この問題を解決してみましょう。
理想的には、転記ロジックをすべてVBAで書いてしまいたいところです。ただ、そうすると、VBAのプログラムが複雑になってしまいがちです。そこで、今回は、数式による転記とVBAを組み合わせて、VBAのプログラムを最低限で済ませる工夫をしてみようと思います。
事前準備:「CSV」シートの数式を修正する
「CSV」シートの4行目に入力されている数式をコピーして、1行目に貼り付けてください。 そして、4行目以下の数式はすべて削除してしまってください。
- 4行目の数式をコピーして1行目に貼り付け
-
- 4行目~最終行の内容をすべて削除する
-
4行目~最終行を選択して「削除」(=行の削除)をしてください。
これで、準備完了です。
4行目以下の数式をすべて削除したため、「入力」シートの4行目以下で、行の挿入・削除をしても、「CSV」シートへの影響がなくなりました。
VBAのマクロを使って「CSV」シートに仕訳データを転記する
それでは、VBAのマクロを組んで「入力」シートの仕訳データを「CSV」シートに転記してみましょう。
下記のプログラムを入力して、実行してみましょう。
Sub 仕訳転記()
Dim lastRow
lastRow = Worksheets("入力").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("CSV").Range("4:1048576").ClearContents
Worksheets("CSV").Range("1:1").Copy Worksheets("CSV").Range("4:" & lastRow)
End Sub
実行すると、「CSV」シートに必要な行数分、数式が入力され、仕訳データを作成することができます。

プログラムの解説
「入力」シートの最終行を変数lastRowに入れる
Dim lastRow
lastRow = Worksheets("入力").Cells(Rows.Count, 1).End(xlUp).Row
変数lastRowに、「入力」シートに入力されている最後の行(の行番号)を入れています。
たとえば「入力」シートに10行目まで入力されている場合には、変数lastRowの値は「10」になります。
この文が何をやっているかについては、下記ページで詳細を解説しています。合わせてお読みください。
上記ページでも解説していますが、フィルターがかかっている場合その他の場合には、この方法だと、最終行を誤検出してしまう可能性があります。特に、フィルターを使う可能性がある場合には、注意してください。
「CSV」シートの4行目以下を消去する
Worksheets("CSV").Range("4:1048576").ClearContents
CSVシートの4行目(から最終行である1048576行)までの値を削除します。
「CSV」シートの1行目をコピーして、4行目以下必要な行数分貼り付ける
Worksheets("CSV").Range("1:1").Copy Worksheets("CSV").Range("4:" & lastRow)
「CSV」シートの1行目をコピーして、4行目~lastRow行目までに貼り付けています。
一番最後の「Range("4:" & lastRow)」は、Rangeに「4:」と変数lastRowの値を結合したものを指定していることに注意してください。
たとえば、変数lastRowに10が入っている場合、次のような意味になります。
Range("4:" & lastRow) |
↓ |
Range("4:10") |
CSV出力マクロと組み合わせる
これで、「CSV」シートにCSVデータの元データを出力することができるようになりました。
さらに、別ページで紹介している「CSV」シートの内容をCSV出力するプログラムと組み合わせると、同時にCSVデータを出力できるようになります。
上記ページで作成したVBAのプログラムを入力した後に、次のようなプログラムを入力してみてください。
Sub インポートデータ作成()
Call 仕訳転記
Call CSVシートをCSV形式で保存
End Sub
このマクロを実行すると、今回作成したプログラムと、CSV保存プログラムを連続して実行することができ、一気にCSVデータを作成できるようになります。