雪ん子パースペクティヴ

読むとちょっとタメになるエントリー。コメントあると嬉しいです。

【VBA】先月のシートをコピーし、シート名と特定のセルに当月の年月を挿入する。シート間の累計も算出する。【Excel】

vba excel

エッセンスを参考にしてください。

 

はろー、yukiです。

 

今回は、ExcelのマクロとVBAを用いて、下記の作業を行います。

ーーーーーーーーーーーーーーーーーーーー

  1. 先月分のシートをコピー
  2. コピーしたシート名を当月の年月にする
  3. 特定のセルにも同じ値を挿入する
  4. 手入力に使用するセルのデータを削除する
  5. シート間での累計値も算出する

ーーーーーーーーーーーーーーーーーーーー

 

例として、ノルマ表を作成します。

それでは順にみていきましょう。

※今回はマクロとVBAに理解のある方を対象としています。

 

 

 

1. 先月分のシートをコピー

まず、前提としてこちらの図をご覧ください。

f:id:yuki_sasano:20170616170248j:plain

2017年6月現在、作成済みのものは2017年5月の分。

新たに作成する必要のあるものは、2017年6月（当月）の分です。

※翌月分（2017年7月）を作成する方法は最後に書きます。

 

よって、VBAでは下記のコードとなります。

ActiveSheet.Select
ActiveSheet.Copy After:=ActiveSheet

現在（「201705」）のシートを選択し、同じ内容のものをコピー・作成する。

 

すると、作成されたシート名は「201705(2)」になりますので、シート名の変更をします。

 

2. コピーしたシート名を当月の年月にする

VBAで下記のコードを書きます。

Dim Sname As String
Sname = Now
ActiveSheet.Name = Format(Sname, "yyyymm")

シート名には、2017年6月の日付けが値として必要になります。

まず、値を代入する変数を「Sname」とし、型をStringとして作成します。

作成する日にちは、2017年6月中としていますので、NOW関数により、当日の日にちを取得。

取得後、表示形式を「201706」としたいので、Format関数で指定します。

 

次に、同様の日にちをセルにも使用したいので、VBAでコードを書きます。

 

3. 任意のセルにも同じ値を挿入する

Dim Nengetu As Date
Nengetu = Now
Range("B2").Select
ActiveCell.FormulaR1C1 = Format(Nengetu, "yyyy年m月")

上と同じく、セル挿入に使用する変数「Nengetu」をData型で作成。

Now関数により、現在の日にちを取得し、今回は「B2」のセルに「2017年6月」という表記で挿入します。

 

4. 手入力に使用するセルのデータを削除する

例では、「今月のノルマ」と「ノルマ達成」は手入力する箇所となります。

（「今月のノルマ」は毎月変わり、「ノルマ達成」も変動します）

よって、VBAで削除します。

Range("B3:B4").ClearContents

 

最後は、VBAではなく、Excel関数を用います。


5. シート間での累計値も算出する

ノルマは毎月リセットされず、達成されなかった分は累計されることとします。

よって、次のような関数で算出できます。

=IF(MONTH(B2-1)<10, INDIRECT(20170&MONTH(B2-1)&"!B5"),INDIRECT(2017&MONTH(B2-1)&"!B5"))+B3-B4

肝はINDIRECT関数になります。

INDIRECT関数では、引数にある値をシート名として判断します。

例では、「20170」と「B2のセルにある年月から1を引いた値」を結合し、そのシート名の「B5」の値を取得しています。

（つまり、「20170」に「6」から1を引いて「5」となる。「201705」のセル「B5」の値を取得している）

そこに、当月のノルマ数を足し、ノルマ達成数を引いているわけです。

 

ところで、上ではIF関数で分岐させています。

それはシート名が「201701」から「201709」であれば、INDIRECT関数で0を付さなければならず、逆に「201710」から「201712」は0が不必要だからです。

よって、セル「B2」（「年月」）から1を引いた値（つまり先月）が、1月から9月の間であれば、INDIRECT関数で「20170」を使用し、それ以外であれば「2017」を使用するということです。

 

さて、最後の最後は2つおまけがあります。

 

 

 

おまけ1. 来月分を作りたい

当月分はすでにあり、早めに来月分を作っておきたいこともあります。

その時は、これまでのコードを活かし、VBAでIF文を使用します。

If ActiveSheet.Name <> Format(Now, "yyyymm") Then

現在のシート名（「201706）となっている）が、現在の日にち（年月）と同じでない場合、当月分を作成します。

それ以外（つまりシート名と年月が同じ）の場合、来月分を作成します。

コードは単純です。当月分を作成した時のものを活かします。

Else
 Sname = DateAdd("m", 1, Now)
 Nengetu = DateAdd("m", 1, Now) 
End If

 DateAdd関数は日にちの足し算をするもので、月である"m"を指定し、Now関数（当月）の翌月を各変数に挿入しています。

 

残りのシートコピーからセル挿入の処理は全く同じなので、IF文をここで終了させます。

 

おまけ2. 丁寧なマクロにする

簡単な話で、メッセージボックスで使用者に対し、作業の一つ一つに確認をしましょう。

Dim AskNew As VbMsgBoxResult
 
 AskNew = MsgBox("新規作成しますか？", vbYesNo, "新規作成")
 
 Select Case AskNew
 Case vbYes

 <<<ここに新規作成するコード>>>

 Case vbNo
 MsgBox ("新規作成を中断しました。")
 
 End Select

 定数vbYesNoにより、「はい」か「いいえ」を選べるメッセージボックスが表示されるようになります。

マクロを開始後、まずは新規作成するかどうかを使用者に尋ねます。

Select文により、「はい」の場合の処理と「いいえ」の場合の処理で分岐しています。

 

また、おまけ1で書いた当月と来月の分岐の部分で、各コードにメッセージボックスで「いつの分を作成するのか」を教えてあげるのも親切ですね。

 

それから、マクロは【Excelの表示タブ>マクロ>マクロの表示>実行】といった手順でも行うことができますが、ショートカットを作成したり、開発タブからフォームコントロールのボタンを作成してあげるのも親切ですね。

 

もう少し余裕のある方は、同じ名前のシートがある場合にその旨を知らせるというのも良いと思います。

 

 

 

以上。

 

 

Googleアドセンス