複数のシートのデータを1枚にまとめるマクロ実況中継。指示出しは自分の非をまず確認すべき

複数のシートのあるデータを1つのシートにまとめたいときは、マクロを使うと便利です。
どのように書くか、その過程を書いてみます。
※今回の記事はある程度マクロがわかっている方向けに書きましたが、指示の出し方という観点で読んでいただいてもおもしろいかと思います。

 

複数のシートのデータを1つのシートに

複数のシートにあるデータを1つのシートにまとめたい場合、通常はコピー・貼り付けを繰り返す必要があります。
たとえば、こういったファイルが事例に考えてみましょう。
1月から9月、3月賞与のシートがあり、赤枠の部分（給与の仕訳）を1枚のシートに集める事例です。
（最初から1枚のシートで給与データを入力していれば、その必要はありません）

1枚のシートに集めると、
・合計を出したり、分析したりすることが簡単
・加工して会計ソフトに取り込むこともできる
といったメリットがあります。

いざやろうとすると、シート「1月」の該当データをコピーして、

シート「all」に貼り付け（値のみ貼り付け）、

次に、シート「2月」のデータをコピーして

シート「all」に貼り付けます。
（このときには、1月のデータの下に貼り付けるようになります）

今回は、シートの枚数が10枚なので、10回繰り返せばいいのですが、毎回これをやるのもめんどくさく、複数の会社を処理する場合は、大変です。
そこでマクロを使います。

 

 

データの形式をそろえることが必須

マクロを書く前に前提として必要なのは、データの形式をそろえることです。

シート「1月」では、セルB48からセルG55までに該当データがあります。

これがシート「2月」で、セルB49からセルG56までにあってはいけないのです。
すべてのシートで、セルB48からセルG55にデータがなければいけません。
気まぐれでデータの位置を変えていると、マクロでは対応できないのです。

【データをそろえる】のは、非常に重要なことで、マクロを書くようになると、より意識するようになります。

 

 

データをまとめるマクロを書く過程

では、マクロをゼロから書いてみる過程を解説します。

①マクロの下準備

ExcelでAlt＋F11を押し、マクロを書くVBEを開きます。

Alt、I、Mと押し、標準モジュール（マクロを書く場所）を作ります。
ここまでの下準備はこちらの記事を参考にしてください。
Excelでマクロを書く下準備ーExcelを持っていれば無料で使えますー | EX-IT

 

 

マクロ名は、任意に決めて大丈夫です。ここではsheetmergeとつけました。

②シート「1月」をコピーして貼り付けてみる

最初に何をやるか考えます。
各月のシートのセルB49からG56までをコピーして、シート「all」に貼り付けるわけです。
マクロ書くときは少しずつテストしながら作る方がうまくいきます。
まずは、シート「1月」をコピーして貼り付けてみましょう。

マクロはこう書きます。

これを実行させると、エラーになってしまいます。
コピーして集めようとしている部分に数式が入っているからです。

値のみ選択して貼り付けをしなければいけません。
マクロを修正して実行すると、

無事貼り付けることができました。
1つのシートでうまく動くのを確認してから全体を考えていきます。

③繰り返しの指示をする

次は、1月〜9月、3月賞与で同じことをやればいいのです。
先ほどのマクロをこうやってコピーして2月、3月・・・と作っていくことが考えられます。
ただ、これでは美しくありませんし、めんどくさいです。

マクロには、「すべてのシートに対して実行してくれ」という構文があります。
こういった構文です。

    Dim w
    For Each w In Worksheets

    Next

「For Each」と「Next」の間に命令を書きます。
さきほどの、コピー貼り付けの構文を入れてみましょう。

これを動かすと、こうなります。

ん・・？「すべてのシートでやってくれ」っていったのに、何も変わっていません。
Excelが悪いのでしょうか？？
Excelが悪いと思う前に、自分の指示が正しかったかをまず考えましょう。
（人間に対してもすべからくそう考えるべきです(^_^;)）

マクロをよく読んでみると、

すべてのシートに対してやるようにいっているのですが、シート「1月」のセルB49からセルG55をコピーして貼り付けるという命令になっています。
「1月から9月まで」という命令は入っていません。
この命令を入れるなら、こうなります。

「w」というのは、変数と呼ばれるもので、こう書くと、1回目は1月、2回目は2月・・・というようにシートごとに処理してくれるのです。
たとえば、「毎月この資料が届いたらコピーしておいて」という指示を出すようなものと考えてください。
1月に「1月の資料が届いたらコピーしておいて」
2月に「2月の資料が届いたらコピーしておいて」
・
・
・
と毎回指示しないはずです。

④データの貼り付け位置を指定する

では、このマクロを動かしてみましょう。
こうなります。

あれ、指示通りやってるのかいな・・・と思うかもしれません。
試しに、ちょっとずつ指示を出して様子をみましょう。
F8キーを押すとマクロを1行ずつ実行できます。
黄色いのは、「今からこの行を実行する」という意味です。
1月のデータが貼り付けられます。

次は2月のデータが貼り付けられるのですが、1月のデータに上書きしてしまっているのです。

「こらー！普通、1月のデータの下に貼り付けるだろ！」と怒るかもしれませんが、指示を確認しましょう。

Worksheets("all").Range("a1").PasteSpecial Paste:=xlPasteValues

「シート「all」のセルA1に貼り付けなさい」という指示です。
Excel上では忠実にその指示が守られています。

この場合、「1月のデータを貼り付けたら、そのすぐ下のセルに2月のデータに貼り付けて。2月のデータを貼り付けたら・・」という指示を出さなければいけないのです。

データの数を数えてその一番下を指示するには、次のような構文を使います。

dim Last_data
Last_data = Worksheets("all").Range("a" & Rows.Count).End(xlUp).Row

「Last_data」は、任意に決められます（最終行という日本語でもかまいませんが、日本語だと入力モードの切り替えの必要があるため、私は英語かローマ字にしています）

これを組み入れて

実行すると、なんだかうまくいってそうです。

⑤コピーするシートを指定する

ところが、下の方を見ると、変なデータが混ざっています。
「すべてのシートで」と指示しているので、1月から9月、3月賞与以外のシートの該当部分もコピーしてしまっているのです。

「給与以外のデータはコピーしなくていいよ！常識だろ！」と怒ってはいけません。
そのように指示しているからです。

たとえば、「シート名に「月」が入っている場合だけコピーして」という指示をするなら、こう書きます。

        If InStr(w.Name, "月") > 0 Then
        End If

実行・・の前に、毎回実行していくとデータがたまってしまうので、最初に、

    Worksheets("all").Cells.delete

と、データをきれいにします。
その上で構文を組み込むとこうなり、

実行すると、こうなりました。

ようやく完成です。

 

Sub sheetmerge()

    Worksheets("all").Cells.Delete
    
    Dim w
    Dim Last_data
    For Each w In Worksheets
        
        If InStr(w.Name, "月") > 0 Then
            w.Range("b48", "g55").Copy
            
            Last_data = Worksheets("all").Range("a" & Rows.Count).End(xlUp).Row
            
            Worksheets("all").Range("a" & Last_data + 1).PasteSpecial Paste:=xlPasteValues
        
        End If
        
    Next
    
    
End Sub

 

まとめ

マクロは一朝一夕にはできません。
いろいろと試行錯誤しながら作っていくものです。
そして、最も大事なのは、「自分の指示が正しいかどうかを常に考えること」
人への指示のいいトレーニングにもなります(^_^;)
人間は融通をきかせてくれますが、マクロ、Excelはそうではありません。
その代わり、何回でも繰り返して処理してくれますし、文句もいいませんし、サボらないし、正確です。

---

---

---

---

【編集後記】
ふるさと納税に関する連載をやることになりました。
（定期購読誌で書店では販売されません）
ひとまず昨日、概略をまとめ、連休明けに提出します。

［10/17までに200kmチャレンジ］
昨日　5.5km　累計　126.5km

【昨日の1日1新】
※詳細は→「1日1新」

新しいランコース
とある方の紹介で人と会う
自宅近くのスタバ2店舗利用
とある地銀のネットバンクをExcel経由で弥生会計に取込