この記事には広告を含む場合があります。
記事内で紹介する商品を購入することで、当サイトに売り上げの一部が還元されることがあります。
Excelマクロ（VBA）のWorksheets.Copyメソッドを使って、月次出納帳シートの自動繰越機能を作成してみます。シートごとに1ヶ月間の出納帳を管理している場合、この自動繰越機能はかなり便利です。
シートの追加は、Worksheets.Addメソッドを使用する方法もありますが、私はWorksheets.Copyメソッドを使いました。
（Microsoft；Worksheets.Copy メソッド（Excel）より）
（使用Excel）2013
ExcelマクロのWorksheets.Copyメソッドで同じシートを追加する
Excelマクロで（使用している）シートと同じシートを追加するには、Worksheets.Copyメソッドを使用します。
Worksheets.Copyメソッドの基本的な記述方法
次のようなシートを用意しました。
引数Before、引数Afterを省略して記述した場合
このシートで次のようなマクロを記述してみました。
実行すると次のとおり。下の画像の赤色の丸囲み部分のとおり、新しいブック（Book1）が作成されました。
Worksheets.Copyメソッドの引数（Before)と引数（After）を省略すると、上記のようにシートをコピーして新しいブックを作成します。
（Microsoft；Worksheets.Copy メソッド（Excel）より）
注釈
引数 Before と引数 After の両方を省略した場合は、新規ブックが自動的に作成され、シートはそのブック内にコピーされます。
引数Beforeの記載
Worksheets.Copyメソッドの引数（Before）を記述してみました。シート「1604」の前にシートをコピー（して追加）するという記述になっています。
実行すると次のとおりです。シート「1604」の前に「1604（2）」が追加されます。
さらにこのこの状態で再度、同じマクロを実行してみます。シート「1604」の前に「1604（３）」と「1604（４）」の2枚のシートが追加されます。
（この記述方法の場合、ブック内のシートがすべてコピーされることになります。）
このWorksheets.Copyを使って、月次出納帳の自動繰越機能を作成してみます。
ExcelマクロのWorksheets.Copyメソッドで月次出納帳シートの自動繰越機能を作成
月次出納帳シートの自動繰越機能でやりたいこと
月次出納帳シートの自動繰越機能では次の機能を設定します。
[aside type=”boader”]・ Worksheets.Copyメソッドを使ってシートをコピー
・ 年月等、シート名称の変更
・ 月初残高の記載[/aside]
月次出納帳シートの自動繰越機能の作成
追加情報の記載
自動繰越機能で処理する年月やシート名称を変更するため、シート内に次のような情報を追加で記載しました。
変数の定義
マクロ（VBA）の記述で次のように変数（ワークシートを2つ、文字列を7つ）を定義しました。
変数への代入
最初に、変数ws1に開いているワークシートを代入し、その開いているワークシートの西暦、和暦、処理月、期首月をそれぞれ文字列の変数に代入しました。
この文字列を翌月に変更します。（文字列ですが、数値として「1」を加算することができます。）
処理月の変更等
次に、処理月の数値を変更しました。（1を加えて翌月にします。）記述は次のとおりです。
翌月にするため、基本は「処理月（strCa）＋1」という記述になりますが、12月の場合、「＋1」をすると13になってしまうため、If式によって「12月の場合は処理月を『1』とし、西暦と和暦に『＋1』をしたものをそれぞれ文字列変数に代入する」という記述をしました。
和暦は「平成」と「年」にはさまれた数値に1を加算するため、下の画像のとおり、Left及びRightメソッドを使っています。
シート名称の設定
今回シート名称は「西暦下2桁+処理月」としてあります。（1604）
よって、Rightメソッドを使って西暦（strAa）の下2桁を抜き出したものと上記の処理月の変更等によって「1」を加算した処理月（strCa）を「＆」で結んで、文字列変数（strEa)に代入しました。
月初残高を入力するための式の準備
月初の残高は、最初は、残高の数値データをそのままコピーして張り付ける方法を考えていましたが、その方法の場合、過去の処理月に変更があった場合に、その後の処理月を手入力で修正しなければいけなくなってしまいます。
そこで、開いているシート（ws1）の名称を「=’」と「’!G20」で囲ってセルに記述する「式」を作成し、文字列に代入しました。
この式をシートをコピー（して追加）した後に、新しいシートのセルに入力するようにします。
シートのコピーと諸設定
次にワークシートをコピー（して追加）しました。「ws1.Copy」で開いているワークシートをコピーし、「after:=ws1」で開いているワークシートの後に追加するという記述になっています。
（この記事のメインの式なのですが、変数ws1を使ったので、シンプルな式になりました。）
そして「Set wsN = ActiveSheet」によって、新しいシートを変数wsNに代入し、この新しいシートに次の設定をしました。（シートの名称作成、セル内容の削除、月次残高の入力、処理年月データの変更）
※ 期首の場合は、「期首残高」になるという式を記述していますが、ここはあまりこだわる部分ではないかと思います。
マクロの記述は以上で、最後に下の画像のようにシート内にボタンを作成してマクロを登録しました。
実行すると次のとおりです。
表題の年月とシート名称が一致し、月初残高の式（E6セル）は、前月のG20セルを参照するように入力されています。ボタン1つで繰越（新しいシートの作成）ができるのでとても便利です。
■□◆◇ 編集後記 ◇◆□■
今日は、ほとんど外出していないのですが、関東は酷暑です。税理士試験の初日ですが、冷房が効いている会場であってもこの暑さはきついだろうな。試験中は気にならないと思いますが、試験後にどっと疲れが。
