前回利用シートをコピー流用するついでに日付とか前回残を設定する方法【VBA備忘録】

素人VBA備忘録第3回です。

例によって個人的な備忘録ですが、もしかしたら役に立つかもしれないので公開しています。

前回の記事では、毎月決まったフォーマットのシートを自動的にコピーして日付のシート名を設定する方法を書きました。

Excel VBAで前回分のシートをコピーして日付で自動的にシート名をつける方法。【VBA備忘録】シートをコピーしてシート名を各種日付にするマクロの書き方です。前月資料を毎月流用する仕事を少し楽にしましょう！...

今回はほぼその続きです。

Contents

シートコピーのついでに最初の作業もマクロにやってほしい。
VBAで新シート(今回請求書)の日付を自動で入力する手順。
- 決まった日付ならVBAを使わずセルに関数を入れておく方法もある。
- 日付入力をマクロで行う場合。
  - 請求日付の更新
  - 決済日付は締日が決まればVBA不要。
ついでに手入力する欄をクリアする。
前月分の請求書から残高を引き継ぐ。
- 前月分のシートを指定するやり方は2通り。
一旦売掛が0になって前月分がない場合の対処法。
VBAでの請求書シートコピー、日付設定、引き継ぎのやり方まとめ。

シートコピーのついでに最初の作業もマクロにやってほしい。

例えば手打ちの請求書など、前回のシートをそのままコピーして次月分として流用する場合、コピー作業の他にも決まりきった作業が付随すると思います。
せっかくシートコピーのマクロを組んだんですから、決まった作業があるのならそれもマクロにやってもらえば楽になりますし、転記ミスや日付修正のミスも減らせますよね。

三本松

実際手作業のときは日付の変え忘れとかよくやらかしてました。

前提としては、

請求書作成時期は当月末から翌月初め辺り。
1種類のシートを毎月流用。
シート名は請求日ごとに「YYMM」の形式でつける。

といった感じで進めていきます。

↓前回分のコードは下の通り。

Sub 月次更新()
　　'バックグラウンドで作動
　　Application.ScreenUpdating = False 

　　'シート名用の変数定義
    Dim D As String　

　　'シート名を5日後の前月分の数字4桁にする
    D = Format(DateSerial(Year(Date + 5), Month(Date + 5) - 1, 0), "YYMM")

    '右端のシートをコピーして新しいシートを一番右に配置
    Worksheets(Worksheets.Count).Copy after:=Worksheets(Worksheets.Count)

　　'新シートにさっき決めた名前をつける
    ActiveSheet.Name = D
     
End Sub

※2019年12月追記
日付を進めるコード「Year(Date)」を「Year(Date + 5)」に修正しました。
私はこれを忘れて1年前の書類を作ってしまいましたのでご注意を。

今回は、前回の記事でやったこのコードにつけ足す感じでやっていきます。

前月分のを流用する資料(今回だと請求書)は上のような感じで、

請求日付と決済日を更新。
手入力する欄をクリア。
前月分の請求残高を今月の前回残高の欄に引っ張ってくる。

の3点をやります。

VBAで新シート(今回請求書)の日付を自動で入力する手順。

請求書などであれば請求締日を記載する必要があります。
せっかくなのでコピーしたときに自動で締日を入力してもらいましょう。

決まった日付ならVBAを使わずセルに関数を入れておく方法もある。

締日などはVBAを使わなくてもあらかじめセルに、

=EOMONTH(TODAY()-5,0)

というふうに入力しておけば5日前の所属する月の月末日が自動で表示されます。
月末当日でも月が明けてからでも対応できていますので、作成時にこれで困ることはないでしょう。

表示のしかたは「セルの書式設定」でお好みの表示形式を選べばOKです。

日付入力をマクロで行う場合。

基本的にはわざわざマクロを走らせなくても関数で簡単に対応が可能なのですが、この方法だと過去の資料の日付が時間の経過とともに進んでいってしまいます。

なので、時間が経ってから取引先の人などに、

取引先の人

すいません○月分の請求書なんですけど
再送してもらっていいですかね？

と言われて再印刷するときには請求日付が変わっている可能性もあるわけです。
手直しはさして面倒ではありませんが、直し忘れて出してしまっては先方さんも大混乱です。

その場合はマクロで日付を入れ込んでしまえばその後変わることはありませんので無用な混乱を避けられます。

毒猫

だいぶ経ってから請求書再送しろってのも大概だけどな。

請求日付の更新

前月分資料の流用ですので、日付などの記入位置は変わらないと思います。
ですので、特段の事情がない限りセル位置に関しては固定で問題ありません。

請求日の位置は上の例だと「G2」ですので、このセルに請求日付を代入するコードを書きます。
日付の取り方は前回記事と一緒でDateserialを使ってみます。

sheets(D).range("G2") = dateserial(year(date + 5),month(date +5),0)

これで5日後の属する月の前月末の日付が入ります。
当月末に作る場合も月明けに作る場合も請求月末の日付が入りますのでこれで完了です。

決済日付は締日が決まればVBA不要。

決済日付に関しては通常、求日付からの支払いサイトが決まっています。
上のマクロで請求日付を入れた場合、時間の経過によって請求日が動くことはありませんので、決済日付のセルに請求日を基準にした関数を入力しておけば問題ありません。

具体的には、

翌月末払いの場合 → 「=EOMONTH(G2,1)」
翌々月末払いの場合 → 「=EOMONTH(G2,2)」
翌月20日払いの場合 → 「=EOMONTH(G2,0)+20」

といったように請求日付が入っているG2セルが属する月の月末を基準にして関数を組みます。
このとき、「EOMONTH」関数を組み込んでおけば、請求締日が月末以外の取引先でもわかりやすく決済日を算出することができます。

尚、故あってどうしてもVBAで組みたい場合は、

'翌月末支払いの場合
sheets(D).Range("J6") = Dateserial(year(Date + 5),month(Date + 5) + 1,0) 

'翌月20日払いの場合
sheets(D).Range("J6") = Dateserial(year(Date + 5),month(Date + 5) ,20)

のように、請求日同様にDateserialを使って日付をいじくればOKです。

ついでに手入力する欄をクリアする。

入金額や今回の売上など新たに手入力が必要になる欄は、コピー前の数字がそのまま乗っています。
ミスを防ぐためにこちらは消してしまいましょう。

今回の例だと

入金額(C6セル)
調整額(D6セル)

がそれにあたります。
差引額、売上金額、消費税額は通常計算式が入っているはずですのでこちらは消しません。

'入金額をクリア
Sheets(D).Range("C6").ClearContents
'調整額をクリア
Sheets(D).Range("D6").ClearContents

これで各欄が空欄になります。
「0」という数字を入れたい場合は「.ClearContents」の部分を「=0」に置き換えればOKです。

「.ClearContents」は結合したセルでやろうとすると怒られます。
この場合、

Sheets(D).Range("C6").MergeArea.ClearContents

のように、間に「.MergeArea」を挟むことでそのセルを含む領域として指定する必要があります。

前月分の請求書から残高を引き継ぐ。

前月分のシートを指定するやり方は2通り。

日付の設定が終わったら、次は前回分の請求残高を今回の請求書の「前回残高」欄に転記する作業です。

前回分の請求書の日付を指定する方法は2通り。

シート番号の後ろから2つ目を指定
前回の請求日付を名前としたシートを指定

のどちらでも構いません。
ここにある請求残高(H6セル)を今回作った分の請求書にある前回残(B6セル)に転記します。

前者の場合は

Sheets(D).Range("B6") = Sheets(Worksheets.Count - 1).range("H6")

でいけます。

後者の場合は前回分の請求書を変数として定義して作業を行います。

Dim DP As String
'(5日後の前月末の場合)前回請求書シート名
DP = Format(Dateserial(year(Date + 5),month(Date + 5) - 1,0),"YYMM")
'前回請求書の残高を今回請求書の前回残に転記
Sheets(D).Range("B6") = Sheets(DP).Range("H6")

一旦売掛が0になって前月分がない場合の対処法。

普通にやるとシートが存在せずエラーが出る。

毎月取引のある会社ではない場合、二月以上前に売掛金が清算されているため前月分の請求書が存在しない場合もあるでしょう。

三本松

請求書を作るのが3ヶ月ぶりとかいう場合ですね。

別に間違った数字を引っ張ってきても手直しすれば済むのですが、上述した前回残引き継ぎのマクロを、「前月分シートを変数で指定している場合」はエラーが出てしまいます。

飛んでいて存在しないシートから数字を引っ張ろうとしているわけですから当然ですよね。

ですので、

前月分のシートがあるかを確認
前月分の請求書がある場合は前回残に転記
前月分の請求書がない場合は前回残は「０」

となるマクロを組みたいところです。

いろいろ方法はあるがいっそエラーを利用する。

シート番号をループで全部洗う方法も考えましたが、処理が多くなるのと、前回分の請求書より右に現シートがあるため０が上書きされるなーと思い断念。

「いっそエラーが出たら前回残に0を入れる」というマクロを組んでしまったほうが楽かと思いやってみることにしました。

「On Error」と「GoTo」でエラーが出た時の処理を指定する。

エラーが出た時の処理を指定する「On Error」を使ったときはいくつかやり方がありますが、今回は「GoTo」を使ってエラー時の処理を決めることにします。

手順としては、

エラーがない場合の処理を普通に書いて「Exit Sub」で終わらせる。
「On Error GoTo」を挟み、行き先(ラベル)を指定。
ラベルを宣言してエラー時の処理を書く。

と言った感じです。

行き先であるラベルは、Exit Subのあとに書いて、ラベル名にコロン「：」をつけると機能します。

今回の例で実際に書いてみますよ。

　　'エラーが出たらErr1へ
　　On Error GoTo Err1
　　'エラーがなければ前回分(DP)の残高を今回分(D)の前回残へ
　　Sheets(D).Range("B6") = Sheets(DP).Range("H6")
'終了
Exit Sub
'前回分のシートがない場合
Err1:
　　'前回残の数字をクリア
    Sheets(D).Range("B6").ClearContents

これを書いておくことで、引っ張ってくる数字を持つシートがある場合とない場合で処理を分岐してくれます。

VBAでの請求書シートコピー、日付設定、引き継ぎのやり方まとめ。

今回のマクロ挙動を通しで書くとこちらになります。

Sub 月次更新()
    'バックグラウンドで作動
    Application.ScreenUpdating = False

    'シート名用の変数定義
    Dim D As String　'前月分のシート名
    Dim DP As String　'今回分のシート名

    '今回分のシート名を5日後の前月分の数字4桁にする
    D = Format(DateSerial(Year(Date), Month(Date + 5), 0), "YYMM")

    '右端(前回分)のシートをコピーして新しいシートを一番右に配置
    Worksheets(Worksheets.Count).Copy after:=Worksheets(Worksheets.Count)

    'シートにさっき決めた名前をつける
    ActiveSheet.Name = D
    '前回分のシート名
    DP = Format(DateSerial(Year(Date + 5), Month(Date + 5) - 1, 0), "YYMM")
    
　　'請求締日を5日後の前月末に
    Sheets(D).Range("G2") = DateSerial(Year(Date + 5), Month(Date + 5), 0)
　　'入金額をクリア
    Sheets(D).Range("C6").ClearContents
　　'調整額をクリア
    Sheets(D).Range("D6").ClearContents
    
　　'前月請求書がない場合はラベル「Err1」へ
    On Error GoTo Err1
    '前月分の請求残高を今月分の前回残に転記
    Sheets(D).Range("B6") = Sheets(DP).Range("H6")
    'できたら終了
    Exit Sub

Err1:
　　'前月分がないので今回分の前回残はクリア
    Sheets(D).Range("B6").ClearContents

End Sub

これを走らせることで、