VBAで仕訳データのシート間転記を簡単にする

2020年9月20日

私が書いた本「会計ソフトのすき間を埋める経理の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行目～最終行を選択して「削除」（＝行の削除）をしてください。

これで、準備完了です。

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データを作成できるようになります。