記録することによって見えてくるもの

ExcelマクロのWorksheets.Copyメソッドを使って、月次出納帳シートの自動繰越機能を作成。

かわべ
WRITER
 
この記事を書いている人 - WRITER -
かわべ
生誕:1969年 東京 生まれ 趣味:楽器演奏(St.Bass、E.Bass)     読書(ミステリー)     映画鑑賞(学生時代、映画館でアルバイト経験あり)
<スポンサーリンク>  
 

Excelマクロ(VBA)のWorksheets.Copyメソッドを使って、月次出納帳シートの自動繰越機能を作成してみます。シートごとに1ヶ月間の出納帳を管理している場合、この自動繰越機能はかなり便利です。

シートの追加は、Worksheets.Addメソッドを使用する方法もありますが、私はWorksheets.Copyメソッドを使いました。

(Microsoft;Worksheets.Copy メソッド(Excel)より)

worksheets.copy_13

(使用Excel)2013


<スポンサーリンク>    
 

ExcelマクロのWorksheets.Copyメソッドで同じシートを追加する

Excelマクロで(使用している)シートと同じシートを追加するには、Worksheets.Copyメソッドを使用します。

 

Worksheets.Copyメソッドの基本的な記述方法

次のようなシートを用意しました。

worksheets.copy_12

 

引数Before、引数Afterを省略して記述した場合

このシートで次のようなマクロを記述してみました。

worksheets.copy_14

実行すると次のとおり。下の画像の赤色の丸囲み部分のとおり、新しいブック(Book1)が作成されました。

worksheets.copy_15

Worksheets.Copyメソッドの引数(Before)と引数(After)を省略すると、上記のようにシートをコピーして新しいブックを作成します。

(Microsoft;Worksheets.Copy メソッド(Excel)より)

注釈

引数 Before と引数 After の両方を省略した場合は、新規ブックが自動的に作成され、シートはそのブック内にコピーされます。

 

引数Beforeの記載

Worksheets.Copyメソッドの引数(Before)を記述してみました。シート「1604」の前にシートをコピー(して追加)するという記述になっています。

worksheets.copy_16

実行すると次のとおりです。シート「1604」の前に「1604(2)」が追加されます。

worksheets.copy_17

さらにこのこの状態で再度、同じマクロを実行してみます。シート「1604」の前に「1604(3)」と「1604(4)」の2枚のシートが追加されます。

(この記述方法の場合、ブック内のシートがすべてコピーされることになります。)

worksheets.copy_18

このWorksheets.Copyを使って、月次出納帳の自動繰越機能を作成してみます。

 

ExcelマクロのWorksheets.Copyメソッドで月次出納帳シートの自動繰越機能を作成

月次出納帳シートの自動繰越機能でやりたいこと

月次出納帳シートの自動繰越機能では次の機能を設定します。

・ Worksheets.Copyメソッドを使ってシートをコピー

・ 年月等、シート名称の変更

・ 月初残高の記載

 

月次出納帳シートの自動繰越機能の作成

追加情報の記載

自動繰越機能で処理する年月やシート名称を変更するため、シート内に次のような情報を追加で記載しました。

worksheets.copy_21

 

変数の定義

マクロ(VBA)の記述で次のように変数(ワークシートを2つ、文字列を7つ)を定義しました。

worksheets.copy_22

 

変数への代入

最初に、変数ws1に開いているワークシートを代入し、その開いているワークシートの西暦、和暦、処理月、期首月をそれぞれ文字列の変数に代入しました。

worksheets.copy_23

この文字列を翌月に変更します。(文字列ですが、数値として「1」を加算することができます。)

 

処理月の変更等

次に、処理月の数値を変更しました。(1を加えて翌月にします。)記述は次のとおりです。

翌月にするため、基本は「処理月(strCa)+1」という記述になりますが、12月の場合、「+1」をすると13になってしまうため、If式によって「12月の場合は処理月を『1』とし、西暦と和暦に『+1』をしたものをそれぞれ文字列変数に代入する」という記述をしました。

和暦は「平成」と「年」にはさまれた数値に1を加算するため、下の画像のとおり、Left及びRightメソッドを使っています。

worksheets.copy_24

 

シート名称の設定

今回シート名称は「西暦下2桁+処理月」としてあります。(1604)

よって、Rightメソッドを使って西暦(strAa)の下2桁を抜き出したものと上記の処理月の変更等によって「1」を加算した処理月(strCa)を「&」で結んで、文字列変数(strEa)に代入しました。

worksheets.copy_25

 

月初残高を入力するための式の準備

月初の残高は、最初は、残高の数値データをそのままコピーして張り付ける方法を考えていましたが、その方法の場合、過去の処理月に変更があった場合に、その後の処理月を手入力で修正しなければいけなくなってしまいます。

そこで、開いているシート(ws1)の名称を「=’」と「’!G20」で囲ってセルに記述する「式」を作成し、文字列に代入しました。

この式をシートをコピー(して追加)した後に、新しいシートのセルに入力するようにします。

worksheets.copy_26

 

シートのコピーと諸設定

次にワークシートをコピー(して追加)しました。「ws1.Copy」で開いているワークシートをコピーし、「after:=ws1」で開いているワークシートの後に追加するという記述になっています。

(この記事のメインの式なのですが、変数ws1を使ったので、シンプルな式になりました。)

worksheets.copy_27

そして「Set wsN = ActiveSheet」によって、新しいシートを変数wsNに代入し、この新しいシートに次の設定をしました。(シートの名称作成、セル内容の削除、月次残高の入力、処理年月データの変更)

worksheets.copy_28

※ 期首の場合は、「期首残高」になるという式を記述していますが、ここはあまりこだわる部分ではないかと思います。

 

マクロの記述は以上で、最後に下の画像のようにシート内にボタンを作成してマクロを登録しました。

worksheets.copy_29

実行すると次のとおりです。

worksheets.copy_30

表題の年月とシート名称が一致し、月初残高の式(E6セル)は、前月のG20セルを参照するように入力されています。ボタン1つで繰越(新しいシートの作成)ができるのでとても便利です。


■□◆◇ 編集後記 ◇◆□■

今日は、ほとんど外出していないのですが、関東は酷暑です。税理士試験の初日ですが、冷房が効いている会場であってもこの暑さはきついだろうな。試験中は気にならないと思いますが、試験後にどっと疲れが。

Pocket
このエントリーを Google ブックマーク に追加
LINEで送る

この記事を書いている人 - WRITER -
かわべ
生誕:1969年 東京 生まれ 趣味:楽器演奏(St.Bass、E.Bass)     読書(ミステリー)     映画鑑賞(学生時代、映画館でアルバイト経験あり)

報酬のお見積り

法人の顧問報酬と決算報酬を、前期の売上実績当期の売上予測でお見積もりすることができます。(※)

LINK 報酬お見積もり【法人用】 web

お名前やメールアドレスの入力は必要ありません。ウェブ上で簡単にお見積もりができます。

※ オプション料金は別途となります。

Copyright© J-musu-no-blog , 2016 All Rights Reserved.