エッセンスを参考にしてください。
はろー、yukiです。
今回は、ExcelのマクロとVBAを用いて、下記の作業を行います。
ーーーーーーーーーーーーーーーーーーーー
- 先月分のシートをコピー
- コピーしたシート名を当月の年月にする
- 特定のセルにも同じ値を挿入する
- 手入力に使用するセルのデータを削除する
- シート間での累計値も算出する
ーーーーーーーーーーーーーーーーーーーー
例として、ノルマ表を作成します。
それでは順にみていきましょう。
※今回はマクロとVBAに理解のある方を対象としています。
1. 先月分のシートをコピー
まず、前提としてこちらの図をご覧ください。
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
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の表示タブ>マクロ>マクロの表示>実行】といった手順でも行うことができますが、ショートカットを作成したり、開発タブからフォームコントロールのボタンを作成してあげるのも親切ですね。
もう少し余裕のある方は、同じ名前のシートがある場合にその旨を知らせるというのも良いと思います。
以上。
