ブック内のすべてのシートをループして、
複数のシートに同じ処理を実行するコードを紹介します。
たくさんのシートに同じ処理を一括で行うことは、
シート関数ではできない、マクロならではの便利な技です。
いくつかのパターンをご用意しましたので、
お好きなコードをお持ち帰りください。
すべてのシートに同じ処理を行うコード
シート番号を使ってFor~Next文でループする
Sub すべてのシートに同じ処理を実行する() Dim シートNo As Long For シートNo = 1 To Worksheets.Count ' シートごとへの処理をここに書く Worksheets(シートNo).Range("A1") = 1 Next End Sub
こちらがすべてのシートに同じ処理を行う最も基本的なコードです。
For~Nextステートメントを使用し、
シート番号を1からシートの枚数までループしています。
このサンプルでは、すべてのシートのA1セルの値を1にしています。
ちなみに、変数「シートNo」を「i」にしているコードをよく見かけますが、
ここでiを使ってしまうと、シート内の処理にiが使えなくなります。
例えばCells(i ,1)などを使いたくなった時に、名前がかぶって困りますからね。
Cells(i, 1)で書かれたコードとコピペ合体しようとすると大変なことになります。
シートNoが面倒なら「s」でもいいので、「i」はやめておきましょう。
シートをループするFor文の中で、さらにセルに対してFor文をまわす
Sub すべてのシートのA1からA10セルを処理する() Dim シートNo As Long For シートNo = 1 To Worksheets.Count ' 各セルへの処理をここに書く For R = 1 To 10 Worksheets(シートNo).Cells(R, 1) = 1 Next Next End Sub
シートのFor文の中で、さらにFor文を使って複数のセルを処理するサンプルです。
For文を重ねる場合は、インデントを、Forごとにしっかりとりましょう。
どのForとNextが対応しているのかわかりやすくなり、コーディングが楽になります。
インデントをしていないコードと比べてみてください。
Sub すべてのシートのA1からA10セルを処理する() Dim シートNo As Long For シートNo = 1 To Worksheets.Count ' 各セルへの処理をここに書く For R = 1 To 10 Worksheets(シートNo).Cells(R, 1) = 1 Next Next End Sub
インデントは
- Tabキーで増やす(コードを⇒へ移動)
- Shift+Tabキーで減らす(コードを⇐へ移動)
で簡単に設定できます。
複数の行を一斉にインデントもできますので、
このショートカットは必ず覚えておいてください。
ついでですが、例のようにCells(i, 1) ではなくCells(R, 1)としておくと、
この変数が行番号ということがわかりやすくなります。
他のiを使ったコードとのコピペ合体にも強くなります。
カウンタ変数になんとなく「i」を使っていた方は、この機会に試してみてください。
特定のシート(ある名前から始まる、偶数番目など)だけに処理をする
すべてのシートに処理をするのではなく、特定のシートだけに処理をしたい場合は、
- まずはすべてのシートをループするFor文を書く
- ループ開始直後に実際に処理するかどうかのIf文を書く
という順でコードを書きます。
Sub 売上データから始まる名前のシートだけに同じ処理を実行する() Dim シートNo As Long For シートNo = 1 To Worksheets.Count If Left(Worksheets(シートNo).Name, 5) = "売上データ" Then Worksheets(シートNo).Range("A1") = 1 End If Next End Sub
Sub 偶数番目のシートだけに同じ処理を実行する() Dim シートNo As Long For シートNo = 1 To Worksheets.Count If WorksheetFunction.IsEven(シートNo) Then Worksheets(シートNo).Range("A1") = 1 End If Next End Sub
このように、For文とIf文を組み合わせることで、
ループする対象のシートを絞ることができます。
なお、対象シートの条件がシート番号だけであるならば、
If文を使わずに、For文の設定で行うことも可能です。
Sub 3枚目から7枚目までのシートに同じ処理を実行する() Dim シートNo As Long For シートNo = 3 To 7 Worksheets(シートNo).Range("A1") = 1 Next End Sub
Sub 偶数番目のシートだけに同じ処理を実行する() Dim シートNo As Long For シートNo = 2 To Worksheets.Count Step 2 Worksheets(シートNo).Range("A1") = 1 Next End Sub
偶数シートへの処理が2つ出てきましたが、
If IsEven(シート番号) | ⇒ シート番号が偶数なら処理する |
For 2 To ○○ Step 2 | ⇒ 2番目のシートから2ずつ進んで処理していく |
このどちらでも同じ処理をすることができるということです。
どちらも書き方自体はとても簡単ですので、
両方使えるようにしておきましょう。
複数のブックを扱うマクロを作成する場合
Sub すべてのシートの名前を一括で変更する() Dim シートNo As Long For シートNo = 1 To Thisworkbook.Worksheets.Count Thisworkbook.Worksheets(シートNo).Name = _ Workbooks("シート名リスト.xlsx").Worksheets(1).Cells(シートNo, 1) Next End Sub
シート名のリストを別ブックに書いておき、一括でシート名を変更するサンプルです。
⇐
今までの例で書いていた「Worksheets」という記述は、
正確にはActiveWorkbook.Worksheetsが省略されたものです。
複数のブックを扱うマクロでは、選択中のブックが変わる可能性があり、
アクティブブック・シートに頼ったコードはバグの元になります。
Workbooks("○○.xlsx").Worksheetsや、Thisworkbook.Worksheetsなど、
どのブックのWorksheetsなのかを明示しておきましょう。
コードを書くのを楽にするテクニック
さて、今までのサンプルではわかりやすいように実際の処理は1行にしていました。
' 全シートに行う処理はこの1行だけだった Worksheets(シートNo).Range("A1") = 1
しかし実際のマクロでは、↓のように処理が何行~何十行にもわたることも珍しくありません。
Sub 売上データから始まる名前のシートだけに同じ処理を実行する() Dim シートNo As Long For シートNo = 1 To Worksheets.Count If Left(Worksheets(シートNo).Name, 5) = "売上データ" Then ' ←判定もある Worksheets(シートNo).Range("A1") = 1 ' ← 処理も複数行 Worksheets(シートNo).Range("B1") = 2 Worksheets(シートNo).Range("C1") = 3 ' ~~まだまだ処理が続く End If Next End Sub
こうなるとWorksheets(シートNo)が何度も出てきて面倒だし、読みづらいです。
これをいちいち書きたくありませんので、
これを楽に書き、しかも読みやすくするテクニックを紹介していきます。
変数を使う
Worksheets(シートNo)を省略する方法として、変数を使うコードがこちらです。
Sub 売上データから始まる名前のシートだけに同じ処理を実行する() Dim ws As Worksheet Dim シートNo As Long For シートNo = 1 To Worksheets.Count Set ws = Worksheets(シートNo) If Left(ws.Name, 5) = "売上データ" Then ws.Range("A1") = 1 ws.Range("B1") = 2 ws.Range("C1") = 3 End If Next End Sub
このコードでは、For文の開始直後に、
Worksheets(シートNo)を、変数wsに格納しています。
以降のコードはwsと打つだけでそのシートを指しますので、
コードが劇的に書きやすくなりますし、読みやすくなりますね。
さらにもう一つの利点として、変数の宣言でDim ws As Worksheetと、
「この変数に入るのはワークシートだよ」とVBA(VBE)さんに教えてあげたことで、
このWorksheetが持っているメンバーの選択肢が使えるようになることも重要です。
Rangeと打つ必要もなくなり、スペルミスの確率もかなり減ります。
何回も出てくるコードを変数にしてしまうテクニックは、
Worksheetだけでなく、なんにでも使えますので、ぜひ身に着けておきましょう。
なお、今回は1つのシートしか登場しないため「ws」としましたが、
複数のシートを扱う場合は注意が必要です。
安易に「ws1」「ws2」などにすると、取り違えの元ですし、
あとで読み返す際に訳が分からなくなる危険があります。
このとき、
Dim ws出力 As Worksheet Dim ws取込 As Worksheet
など、役割も一緒に名付けてあげると、
書きやすさと読みやすさを両立できます。
プログラムは書きやすさより読みやすさの方がトータルの作業時間に影響しますので、
変数名をサボりすぎないように注意しましょう。
ちなみにwsと入力した段階で「Ctrl+Space」を押すことで、
このように選択肢からの入力ができます。
VBAをやるうえで最も重要なショートカットキーですので、
使っていなかった方はこの機会に覚えてしまいましょう。
Withステートメントを使う
Worksheets(シートNo)を省略する方法として、
Withステートメントを使うコードがこちらです。
Sub 売上データから始まる名前のシートだけに同じ処理を実行する() Dim シートNo As Long For シートNo = 1 To Worksheets.Count With Worksheets(シートNo) If Left(.Name), 5) = "売上データ" Then .Range("A1") = 1 .Range("B1") = 2 .Range("C1") = 3 End If End With Next End Sub
このコードでは、For文の開始直後に、Worksheets(シートNo)を、
With Worksheets(シートNo)
と、Withステートメントに格納しています。
こうすることで、以降のコードでは、
If Left(.Name), 5) = "売上データ" Then .Range("A1") = 1
などのように、「.」1文字で、そのシートを指定できるようになります。
Withステートメントは非常に強力な武器で、
特にセルなどの子供たちを多く持つWorksheetとの相性は抜群です。
積極的に活用してきましょう。
おまけ:もう一つのSubプロシージャを使う
Worksheets(シートNo)を省略する方法として、
もう1つのSubプロシージャを作る方法もあります。
いわゆるプロシージャ分割(関数分割)ですね。
プロシージャ分割と聞くと難しく感じる方もいらっしゃるかもしれませんが、
Worksheetを扱う関数はとても簡単に作れて、しかも読みやすいです。
この機会に触れてみると、意外とすんなりイメージできると思いますので、
正確にわからなくても、適当に流し読みしてみてください。
Sub 売上データから始まる名前のシートだけに同じ処理を実行する() Dim シートNo As Long For シートNo = 1 To Worksheets.Count Call シートごとの処理(Worksheets(シートNo)) Next End Sub Sub シートごとの処理(ws As Worksheet) If Left(ws.Name, 5) = "売上データ" Then ws.Range("A1") = 1 ws.Range("B1") = 2 ws.Range("C1") = 3 End If End Sub
シートごとの処理をそっくり関数に切り出したSubプロシージャを作成しています。
関数の中身は、よく見ると上記の変数wsを使ったバージョンと全く同じコードです。
- Worksheets(シートNo)を変数wsにするコードを
- Worksheets(シートNo)を関数の引数wsに渡すコードにすることができる
ということですね。
関数化の1番のメリットは、メインのSubプロシージャが極端に短くなることです。
シートごとの処理を行う部分が、Callする1行に凝縮できています。
これにより、
- メインのプロシージャではシートをループするコードに集中する
- シートごとの処理プロシージャでは、実際に行う処理のコードに集中する
と、頭を整理してコーディングに臨めるようになります。
シートごとの処理が複雑になればなるほど、
シートをループする「For」から「Next」までがものすごく広がってしまいます。
「このNextってどのForのやつだっけ?」「このExit Forってどこに行くんだろ…」
みたいにループのロジックで混乱してしまうことを、関数化で防いでいるわけですね。
また、他のメリットとして、
「なんか第5シートで処理したときに不具合あるな…」
みたいになったときに、
Call シートごとの処理(Worksheets(5))
と実行したり、
「このシートが怪しい気がする…」と睨んだら、
Call シートごとの処理(ActiveSheet)
と実行するなど、テストや検証が格段に楽になるというメリットもあります。
関数と聞くと難しそうな印象があるかもしれませんが、
シート内の処理を切り出す関数は、Worksheetを引数にしてあとはコピペするだけで動くものも多いです。
せっかくなので、この機会に使ってみておくと、
いつか関数を本格的に学ぶのが楽になると思います。
気が向いたら勉強してみてください。
おまけ2:For Eachステートメントを使う
最後に、「For Each~Nextステートメント」を紹介して終わります。
今までのFor文は「1からシート数までシートNoを増やしていく」ものでしたが、
For Each文では、「すべてのシートを順に取得していく」というストレートな指定ができます。
説明するより実際のコードを見た方が簡単ですので、
百聞は一見に如かず、以下のコードをご覧ください。
Sub ブック内のすべてのシートに処理をする() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Range("A1") = 1 Next End Sub
↑のコードの通り、「For Each 変数○○ In コレクション△△」という書き方で、
△△の中にあるすべてのオブジェクトを、順番に変数○○に取得していくことができます。
コレクション△△の中身を全部処理するコードを書きたい場合は、
変数のSetなどを書かなくてよい分、ストレートで読みやすいコードになりますね。
For文での書き方である、
For シートNo = 1 To Worksheets.Count
こちらと、どちらが優れているというものではなく、
場合によって使い分けることができるとよいものです。
余力があれば覚えてあげてください。
なお、初級者の方が勘違いしやすいポイントとして、
For Each ws In ThisWorkbook
ではなく、
For Each ws In ThisWorkbook.Worksheets
です。
「ワークブックの中のワークシートをループ」するのではなく、
「ワークブックの中のワークシート群の中の要素をループ」します。
For Eachはコレクション(≒△△群)の中身をすべて処理するステートメントですので、
間違えないようにご注意ください。
まとめ
今回は「すべてのシートに同じ処理を行う」コードとして、
を紹介しました。
WorksheetをFor文でループ処理するのは、ExcelVBAの基本となる大事なコードです。
複数のシートを一括処理するのは、シート関数ではできないマクロの特権ですので、
ぜひ身に着けて、積極的に活用していきましょう。