素人のVBA備忘録2記事目です。
個人的な備忘録ですが、もしかしたら何かの役に立つかもしれないので一応公開しています。
間違えてたらすみません。
第1回の「SQL内の条件をセル内の日付で指定してシートに貼り付ける方法」はこちらです。
今回の記事は前回のとはあまり関係はなく、より初歩的な備忘録です。
Contents
前回のをコピーして作るならマクロでやっちゃえば楽になる？
Excelで月ごとの資料を作るとき、
- 前回使ったものをシートコピー
- コピーしたシートの名前を日付でつける
という作業が発生します。
たまになら全然構わないんですが、毎月、しかも何箇所分もあるとなると結構手間です。
手間だけならまだしも、たまにコピーし忘れて作成を始めてしまい、最初からやり直しになったら結構ショックです。
最悪気づかないまま保存してしまい、前月分のデータとサヨウナラという可能性も否定できません。
ですので、そういった流用するような資料のコピーはVBAに任せてしまおうというのが今回の趣旨です。
スポンサーリンク
VBAでのシートコピーのやり方。
まずお約束で、マクロを走らせたときに動きが見えるのは嫌なのでそれを消します。
Application.ScreenUpdating = False VBAの最初にこれを書いておけばバックグラウンドで勝手にやってくれます。
右端のシートをコピーして一番右に配置する。
大体の場合、左側が古いシートで右側に新しいのが増えていくことが多いと思います。
ですので右端のシートをコピーして新しいシートを更にその右に配置することになります。
Excelにはマクロの記録なる便利なものがありますが、これをやって右端に配置すると、
Worksheets("〇〇").Copy after:=Worksheets("〇〇")
となってしまい、次にやったときも同じシートの後ろに挟まれてしまいます。
これを回避するためには、右端に一つ仮のシートを作っておいて
Worksheets("〇〇").Copy before:=Worksheets("仮シート")とすれば、常に仮シートの1個前に置かれるので順番は守られます。
でもちょっとかっこ悪いですね。
ですので、シート名で指定するのではなくシート番号で指定してやります。
そして便利なことに、シート数をカウントする機能もVBAにはついています。
親切設計ですね。
Worksheets(Worksheets.Count).Copy after:=Worksheets(Worksheets.Count)これで右端のシートを更に右端にコピーすることが可能になります。
複数種類の資料をコピーして右に配置するには？
2種類あってそれを両方コピーしたい場合は「右から2番目のシートを最後尾に配置する」を2回繰り返すことで可能になります。
Worksheets(Worksheets.Count - 1).Copy after:=Worksheets(Worksheets.Count)
Worksheets(Worksheets.Count - 1).Copy after:=Worksheets(Worksheets.Count)これだけです。
簡単に説明すると、
| 資料A1902 | 資料B1902 | 資料A1903 | 資料B1903 |
という並びで1904を作りたい場合、最初の1行で
| 資料A1902 | 資料B1902 | 資料A1903 | 資料B1903 |
から
| 資料A1902 | 資料B1902 | 資料A1903 | 資料B1903 | 資料A1903(2) |
になり、2行目で
| 資料A1902 | 資料B1902 | 資料A1903 | 資料B1903 | 資料A 1903(2) |
資料B 1903(2) |
となる理屈です。
種類が増えた分だけ回数と「-」の後の数字を増やしていけばいいということですね。
残念ながら私は知りません。
コピーしたシートに日付の名前をつける。
まずはシート名用の変数を定義
シート名は文字列なのでそれ用の変数を定義します。
Dim D As String
とりあえずこの記事では「D」を使って進めていきますが、変数は好きに決めて構いませんよ。
Format関数で日付を文字列化する
今月のシート名にしたい場合は今日の日付を求める「Date」を使います。
それを「Format」関数で好みの形に文字列化してシート名にする算段です。
'1904にしたい場合
D = Format(Date, "YYMM")
'190415にしたい場合
D = Format(Date, "YYMMDD")
'20190415にしたい場合
D = Format(Date, "YYYYMMDD")前月分など今月以外の月にする場合は、更に「Dateserial」を組み合わせます。
Dateserial関数は、3つの引数をそれぞれ年月日として指定する関数ですが、
- 第1引数→年
- 第2引数→月
- 第3引数→日
第2引数に0を入れると前年の12月になり、第3引数に0を入れると前月の末日になります。
'前月分にしたい場合
D = Format(DateSerial(Year(Date), Month(Date), 0), "YYMM")
'前月末にしたい場合
D = Format(DateSerial(Year(Date), Month(Date), 0), "YYMMDD")
'前月同日にしたい場合
D = Format(DateSerial(Year(Date), Month(Date) -1 , Day(Date)), "YYMMDD")Dateserialで日付の基準日をいじることができますので、
月が明けてから作ることもあるんだよなー
という場合は、
'明ける前の月のを作りたい場合
D = Format(DateSerial(Year(Date + 5), Month(Date + 5) -1 , Day(Date)), "YYMMDD")
'明ける前の月末分を作りたい場合
D = Format(DateSerial(Year(Date + 5), Month(Date + 5), 0) , "YYMM")のように勝手に日付を進めて前月分にすることも可能です。
※2019年12月追記
日付を進める際はMonthの方だけでなく、「Year(Date + 5)」のようにYearのほうも日付を進めるのを忘れないようにしましょう。
私は年末の書類を作るときにこれを忘れてしまい、1年前の書類が出来上がってしまいました。
コピーしたシートに名前をつける。
上記で決めた名前をコピーしたシートにつけるんですが、コピー直後であればコピー先のシートが必ずアクティブになっているのでシート名をわざわざ指定しなくてもOKです。
ですので、コピーしたコードのすぐ後に
ActiveSheet.Name = Dと入れるだけで日付の名前が入った新しいシートの出来上がりです。
スポンサーリンク
前回分のシートをコピーして日付を名前にするコード
全部通しで書くとこんな感じです。
Sub 月次更新()
'バックグラウンドで作動
Application.ScreenUpdating = False
'シート名用の変数定義
Dim D As String
'シート名を5日後の前月分の数字4桁にする
D = Format(DateSerial(Year(Date + 5), Month(Date + 5) , 0), "YYMM")
'右端のシートをコピーして新しいシートを一番右に配置
Worksheets(Worksheets.Count).Copy after:=Worksheets(Worksheets.Count)
'新シートにさっき決めた名前をつける
ActiveSheet.Name = D
End Sub
資料が1種類の場合は上記のコードでコピーは完了します。
一方、資料の種類が2種類あり、
| 請求1902 | 納品1902 | 請求1903 | 納品1903 |
という並びになっている場合は、
Sub 月次更新()
'バックグラウンドで作動
Application.ScreenUpdating = False
'シート名用の変数定義
Dim D As String
'シート名を5日後の前月分の数字4桁にする
D = Format(DateSerial(Year(Date + 5), Month(Date + 5) , 0), "YYMM")
'右から2番目のシート(請求1903)をコピーして新しいシートを一番右に配置
Worksheets(Worksheets.Count - 1).Copy after:=Worksheets(Worksheets.Count)
'シートにさっき決めた名前(請求1904)をつける
ActiveSheet.Name = "請求" & D
'右から2番目のシート(納品1903)をコピーして新しいシートを一番右に配置
Worksheets(Worksheets.Count - 1).Copy after:=Worksheets(Worksheets.Count)
'シートにさっき決めた名前(納品1904)をつける
ActiveSheet.Name = "納品" & D
End Sub
と書けば2つコピーして名前をつけてくれます。
ご利用の条件によって関数の中をいじっていけばある程度対応できると思います。
コピーした新しいシートにおまけでいくつかやりたいこともありますがそれはまた次の機会に書こうと思います。
以上です！
